Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

Postgresql unique objects naming convention and converting from SQL Server

13 July,2023 by Rambler

One of the problems when migrating from SQL Server to PostgreSQL is dealing with PostgreSQL requirement for every object name to be unique within a schema.  The DBA could face  this sort of issue:

1) There are SQL Server tables which may have indexes attached with the same name.

table1 with index called "myindex"

table2 with index called "myindex"

PostgreSQL requires all object’s names in a schema to be unique. Index names must be unique in the schema of the base table. SQL Server allows , an index name to bee the same for different tables. 

If you're using the AWS Schema Conversion Tool (SCT)  and want to avoid Error 42P07   , it is possible to configure the AWS SCT so it produces unique index names duing the metadata conversion process. 


To ensure the uniqueness of index names, AWS SCT gives you the option to generate unique index names if your index names are not unique.
To do this, choose the option Generate unique index names in the project properties. By default, this option is enabled.
If this option is enabled, unique index names are created using the format IX_table_name_index_name. If this option is disabled, index names aren’t changed. 

Generate_unique_indexes


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Postgresql unique objects naming convention and converting from SQL Server


dba-ninja.com