Columns names are case sensitive on postgres

Question

I have successfully migrated database from MySQL to PostgreSQL. While I am trying to connect with ofbiz, I am getting an error saying that

"user_id" not found.

When I query against postgres by using

select * from user where user_id='1';

It's throwing the same error. But when I try with

select * from user where "USER_ID"='1';

It's fetching correctly. Table names are not case sensitive here, only column names. Can anyone give me a solution please?

• user1822: postgresql.org/docs/current/static/…

Answer 1

When migrating, did the migration script double-quote the column names when creating tables? That would preserve the case.

You could either fix the migration script and re-import, or rename the columns e.g.

ALTER TABLE my_user RENAME COLUMN "USER_ID" TO user_id;

(note: user was used in the original question but that's a reserved keyword in Postgres so that couldn't have been one of the tables involved)

• Craig Ringer: That's an unfortunate example because user is a reserved word ;)
• michel-lind: @CraigRinger indeed, was using the table name that the questioner asked. Should probably edit it!
• ypercubeᵀᴹ: @CraigRinger can you throw some light into this conversation? dba.stackexchange.com/questions/108129/…
• Jeevitha: Craig, I am facing this issue for all the tables. The table user is an example one not an exact table which i am using.As per your suggestion let me verify the migration script first.
• Jeevitha: Hi, This issue is because of character set. In my Mysql db character set is latin1 and collations are latin1_swedish_ci and latin1_general_cs. previously i have tried with utf8 encoding.Thats why case sensitive issue i hope. But now i tried with latin1, mysql dump and conversion got success but while restoring dump into postgres its throwing an error like psql:dbname.psql:14: ERROR: collation "latin1_general_cs" for encoding "UTF8" does not exist. can anyone give me a solution plz?