PostgreSQL Field Name

Question

The project which I am working now is upgrading the database from mysql to postgreSQL in Zend framework. I had migrated the database to PostgreSQL through "ESF Database Migration Toolkit". How ever the field names like "Emp_FirstName", "Emp_LastName" etc are stored in PostgreSQL as "emp_firstname" and "emp_lastname". This caused Errors in code. However when I updated the filed in PostgreSQL to Emp_FirstName it showing error

********** Error **********

ERROR: column "Emp_FirstName" does not exist
SQL state: 42703
Character: 8

Is it possible to give filed name exactly like in MYSQL?

For future reference, please always show: Exact query text, exact error message (as you did, thanks), exact PostgreSQL version from select version(), an any other relevant details like your platform. As it happens it doesn't matter for this question but it often does. — Craig Ringer
– Craig Ringer, Commented Mar 19, 2013 at 11:17

Craig Ringer · Accepted Answer · 2013-03-19 11:16:07Z

The migration tool isn't "double quoting" identifiers, so their case is being flattened to lower-case by PostgreSQL. Your code must be quoting the identifiers so they're case-preserved. PostgreSQL is case sensitive and case-flattens unquoted identifiers, wheras MySQL is case-insensitive on Windows and Mac and case-sensitive on *nix.

See the PostgreSQL manual section on identifiers and keywords for details on PostgreSQL's behaviour. You should probably read that anyway, so you understand how string quoting works among other things.

You need to pick one of these options:

Change your code not to quote identifiers;
Change your migration tool to quote identifiers when creating the schema;
Hand migrate the schema instead of using a migration tool;
Fix the quoting of identifers in the tool-generated SQL by hand; or
lower-case all identifiers so it doesn't matter for Pg

The last option won't help you when you add Oracle support and discover that Oracle upper-cases all identifiers, so I'd recommend picking one of the first four options. I didn't find a way to get the migration tool to quote identifiers in a quick 30second Google search, but didn't spend much time on it. I'd look for options to control quoting mode in the migration tool first.

PostgreSQL does not have a configuration option to always treat identifiers as quoted or to use case-insensitive identifier comparisons.

This is far from the only incompatibility you will encounter, so be prepared to change queries and application code. In some cases you might even need one query for MySQL and another for PostgreSQL if you plan to continue to support MySQL.

If you weren't using sql_mode = ANSI and using STRICT mode in MySQL you'll have a lot more trouble with porting than if you were using those options, since both options bring MySQL closer to SQL standard behaviour.

Collectives™ on Stack Overflow

PostgreSQL Field Name

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related