Quoting columns with spaces in PostgreSQL?

Question

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?

Answer 1

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

• Kassandry: This is also referred to in greater detail in the lexical structure documentation.

Answer 2

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.