Skip to Content
avatar image
Former Member

Postgres SQL is support in Hana Database ?

Hi Expert,

I have existing PostgreSQL DB now our requirement to move the database to Hana ,

So can we directly used the PostgreSQL Script in Hana to create the same structure of database ?

Thanks,

Sumit

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Dec 01, 2016 at 03:49 PM

    Hi Sumit,

    It all boils down to what you would really like to do. If reading from Information_schema is required, it is perfectly doable - SAP HANA has metadata exposing system views which would give you the desired results. All you may need to do is create a wrapper (read view) on these tables and voila - for eg., INFORMATION_SCHEMA.TABLES can point to SYS.M_CS_TABLES with appropriate field aliasing as well.

    The challenge you may most likely face would be in areas where you are using DB-specific functionality - e.g., partitioning, tablespaces, view-updates, materialised views.

    In summary, you would definitely need to go through an entire DB migration but I would say it should not be overly complicated.

    Regards,

    Kapil

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 21, 2016 at 02:07 PM

    Hi Sumit,

    Yes, you can. SAP HANA is ANSI-SQL compliant - same as PostgreSQL. But it all boils down to how much of the base ANSI-SQL you have really used.

    PostgreSQL has extended functionalities to use it is as on ORDBMS, Procedure polymorphism, etc., which are different in SAP HANA. Some examples of small but important differences exist like there is no varchar(max), varchar is not unicode, procedures do not have polymorphism.

    But, if you have been using PostgreSQL largely as a data-store, it should be a smooth migration. But beware, once you have migrated to SAP HANA though, the performance is quite addictive. :)

    Regards,

    Kapil

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 22, 2016 at 07:38 PM

    Hi Sumit,

    Well looks like you faced your first migration issue and an old classic: databases don't implement SQL standards only, they do their own thing. COPY function is an example which is not on the SQL Standard. HANA on the other hand, does not have that feature (it sure have other import/export features).

    So for the COPY function, I'm sure you can verify on the Postgres documentation how generate insert statements instead of COPY. Of course, you can still bump into differences such as: data types, imperative logic, etc.

    So what I see here is possibly a database migration project (unless you're dealing with a fairly simple database *and* you're not super tied to Postgres specifics) . If that's the case, then using a migration tool (like SAP Data Services) to support the process is a good idea (at least for the data migration). Procedures, functions and complex types defined will demand their migration time and effort accordingly.

    BRs,

    Lucas de Oliveira

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Sumit,

      One thing that it's important to stress here is before going any further: db migrations are *not* straightforward. There's no magic button you can push to make it happen.

      You'll need to evaluate exactly what you have on postgres that needs to go to HANA and specially how to adapt the current scenario to HANA. For example: maybe on your Postgres system you have many databases with many schemas on each database. In that case, it seems to fit into an MDC scenario where each HANA tenant would match to a specific Postgres database. Of course, this is an example not necessarily the reality.

      Another detail here is to keep up with the application side expectations. In other words, application migration (or more likely adaptation). That's just as hard as a db migration and depends strongly on what you use at the front-end (how tied you are to postgres). In short, a successful migrated DB does not mean you can simply aim the application at the new database and hope it will work.

      Again, this kind of study/preparation and decisions needs to be done by whoever is driving the migration taking into account the applications used therein. No magic.

      As per schema information_schema is part of postgres architecture and I'm not sure what is its counter part on HANA (if exists).

      BRs,

      Lucas de Oliveira

  • avatar image
    Former Member
    Nov 21, 2016 at 02:58 PM

    Hi Kapil ,

    Thanks for your reply .

    But I facing issue to create the schema, table and function using existing SQL script .

    One more thing can we create the historical function like copy_to_log , and all the Postgress SQL function in Hana ?

    Thanks,

    Sumit

    Add comment
    10|10000 characters needed characters exceeded