#1087 dbcopy from informix to postgresql fails on a field called "user"

All
open
nobody
None
5
2013-06-05
2013-06-05
Ivan Voras
No

Using the dbcopy plugin to Copy & Paste a table from an Informix database to PostgreSQL fails with the message "source table has a column defined (user) that cannot be used as a column name in PostgreSQL.

This is false, since all keywords CAN be used in PostgreSQL database, table and column names, they only have to be properly quoted. For example, this works:

db=# create table blah ("user" integer);
CREATE TABLE

Discussion

  • Ivan Voras
    Ivan Voras
    2013-06-05

    A way around it is to simply quote all object names when pasting to PostgreSQL. This could, in theory, break expectations since quoted names become case-sensitive, but the DBA should deal with it.

     
  • Ivan Voras
    Ivan Voras
    2013-06-05

    Also, the script apparently tries to copy index names from Informix verbatim, which fails if the names begin with a number (i.e. are not valid identifiers in PostgreSQL).