5

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?

12

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
1

Moving to PostgreSQL convention

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.