1

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?

2

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)

  • 1
    That's an unfortunate example because user is a reserved word ;) – Craig Ringer Aug 6 '15 at 10:25
  • @CraigRinger indeed, was using the table name that the questioner asked. Should probably edit it! – michel-slm Aug 6 '15 at 10:41
  • @CraigRinger can you throw some light into this conversation? dba.stackexchange.com/questions/108129/… – ypercubeᵀᴹ Aug 6 '15 at 11:03
  • 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 Aug 7 '15 at 7:01
  • 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? – Jeevitha Aug 10 '15 at 6:06

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.