I migrated a database from SQL Server to PostgreSQL.
Most column names contain double words, for example:
SELECT [Column Name] FROM table;
...which does not work in PostgreSQL.
What is the correct syntax for PostgreSQL?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
In most RDBMSs, double-quotes are what are used to specify an exact spelling of something.. (single quotes being string delimiters).
SELECT tab."This IS My Column EXACTLY" AS col FROM "My TabLE Name Contains Spaces Too!" tab WHERE tab."ANOTHER UGLY COLUMN name" = 'MyFilterString';
Notice that capital/lowercase also matters when using double-quotes. (Postgres lower-cases everything when double-quotes are not used ... Oracle upper-cases everthing, etc..)
SELECT COLUMN1 FROM TABLE
in postgres, is different from
SELECT "COLUMN1" FROM TABLE
where as in oracle, is different from
SELECT "column1" FROM TABLE
For more information on this see the post How do I adopt the PostgreSQL naming convention in legacy database?
In PostgreSQL by convention and with good reason, we neither use spaces nor capital letters in our identifiers (columns, tables, schemas, etc). The use of
_ is purely style though.
SELECT FORMAT( 'ALTER TABLE %I.%I.%I RENAME %I to %I;', table_catalog, table_schema, table_name, column_name, lower( -- replace all spaces with _, xX and Xx becomes x_x regexp_replace( -- First, replace spaces with an _ replace(column_name, ' ', '_'), '([[:lower:]])([[:upper:]])', '\1_\2', 'xg' ) ) ) FROM information_schema.columns WHERE column_name ~ ' |[[:lower:]][[:upper:]]';
From there you can edit the commands to be run, delete the ones you don't want, or run
\gexec and all the problems go away.