Le vendredi 08 avril 2011 à 11:12 -0400, Sean Davis a écrit :
2011/4/8 Louise-Amélie Schmitt <louise-amelie.schmitt@embl.de>:
Hello everyone
I just met a huge problem concerning the database. I'm currently trying to transfer my data from MySQL to PostgreSQL by writing a Perl script that would do the job.
Here is the issue: In the "form_definition" table, one of the field identifiers is "desc", which is a reserved SQL keyword used for ordering values. Therefore, There's currently no way of making any query of the type "INSERT INTO table_name (<identifiers list>) VALUES (<values list>);" which is a big handicap in this context, since the order of the identifiers list we dynamically retrieve is not necessarily (and seldom) the right order.
Is there a way to fix this issue without blowing everything up?
You need to quote the identifiers. A simple example using "desc" as a column name:
sdavis=# create table test_table( id int, desc varchar); ERROR: syntax error at or near "desc" LINE 3: desc varchar); ^ sdavis=# create table test_table( id int, "desc" varchar); CREATE TABLE
Hope that helps.
Sean
Thanks, but I cheated by duplicating a little the information and it works fine. I use "insert into table values (...);". I still have problems transfering the data though. The script runs without growling but nothing is actually written in the target tables... I'll have to see that on monday now :/ If I succeed, I'll let you know. Cheers, L-A