PostgreSQL - how to rename CamelCase column?

Question

When I tried to rename a CamelCase column like this:

ALTER TABLE mytable RENAME COLUMN camelCaseColumn TO camel_case_column;

I get the following error:

ERROR: column "camelcasecolumn" does not exist

Surrounding the CamelCase column name with single quotes like this:

ALTER TABLE mytable RENAME COLUMN 'camelCaseColumn' TO camel_case_column;

gives me a syntax error.

Is there any way I can rename the column? Or do I have to create a new table and transfer the data?

The database is running on Ubuntu, and I am using Mac OS X to ssh into the server where the database is running on to issue the ALTER TABLE commands. I know that Mac OS X is case insensitive, so could this be part of the problem?

Thanks!

Answer 1

Turns out that using double quotes works:

ALTER TABLE mytable RENAME COLUMN "camelCaseColumn" TO camel_case_column;

• Craig Ringer: Yes, PostgreSQL case-folds unquoted identifiers, per the SQL spec requirements. See the documentation: postgresql.org/docs/current/static/sql-syntax-lexical.html

Answer 2

If you wish to migrate all of the column names in the database to the PostgreSQL convention, check out my post here

• Moving to PostgreSQL convention