PostgreSQL issue: reserved keyword used as identifier

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

PostgreSQL issue: reserved keyword used as identifier

Louise-Amélie Schmitt
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?

Cheers,
L-A

___________________________________________________________
Please keep all replies on the list by using "reply all"
in your mail client.  To manage your subscriptions to this
and other Galaxy lists, please use the interface at:

  http://lists.bx.psu.edu/
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL issue: reserved keyword used as identifier

Sean Davis
2011/4/8 Louise-Amélie Schmitt <[hidden email]>:

> 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

___________________________________________________________
Please keep all replies on the list by using "reply all"
in your mail client.  To manage your subscriptions to this
and other Galaxy lists, please use the interface at:

  http://lists.bx.psu.edu/
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL issue: reserved keyword used as identifier

Louise-Amélie Schmitt
Le vendredi 08 avril 2011 à 11:12 -0400, Sean Davis a écrit :

> 2011/4/8 Louise-Amélie Schmitt <[hidden email]>:
> > 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

___________________________________________________________
Please keep all replies on the list by using "reply all"
in your mail client.  To manage your subscriptions to this
and other Galaxy lists, please use the interface at:

  http://lists.bx.psu.edu/
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL issue: reserved keyword used as identifier

Kanwei Li
Hi Louise,

Have you considered doing a SQL dump and import? Sounds easier to me
than writing a perl script ;)

-K

2011/4/8 Louise-Amélie Schmitt <[hidden email]>:

> Le vendredi 08 avril 2011 à 11:12 -0400, Sean Davis a écrit :
>> 2011/4/8 Louise-Amélie Schmitt <[hidden email]>:
>> > 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
>
> ___________________________________________________________
> Please keep all replies on the list by using "reply all"
> in your mail client.  To manage your subscriptions to this
> and other Galaxy lists, please use the interface at:
>
>  http://lists.bx.psu.edu/

___________________________________________________________
Please keep all replies on the list by using "reply all"
in your mail client.  To manage your subscriptions to this
and other Galaxy lists, please use the interface at:

  http://lists.bx.psu.edu/
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL issue: reserved keyword used as identifier

Brad Chapman
Louise-Amélie;

> >> > 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.

I've used this ruby script to convert a Galaxy MySQL database to
PostgreSQL:

https://github.com/maxlapshin/mysql2postgres

and it worked smoothly. But definitely be safe and take a backup
before messing with the database so you can roll back to MySQL if
you have any issues.

Brad
___________________________________________________________
Please keep all replies on the list by using "reply all"
in your mail client.  To manage your subscriptions to this
and other Galaxy lists, please use the interface at:

  http://lists.bx.psu.edu/
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL issue: reserved keyword used as identifier

Louise-Amélie Schmitt
In reply to this post by Kanwei Li
On Fri, 8 Apr 2011 16:33:58 -0400, Kanwei Li <[hidden email]> wrote:
> Hi Louise,
>
> Have you considered doing a SQL dump and import? Sounds easier to me
> than writing a perl script ;)
>
> -K

Oh yes I did... Yeah, that solution sounded so sweet that is was the first
thing I tried. But don't be fooled by the apparent easyness, it's another
whole ordeal to go through. If it was MySQL to MySQL or PSQL to PSQL, yeah
that would be the easiest and fastest way. But the syntax/structure used by
both is very different (MySQL is awfully non-standard), and there's no
batteries-included way to do the translation, just a few scripts here and
there. You have to edit stuff manually anyway.

An example: everywhere I see people giving the --compatible=postgresql
option of mysqldump as the obvious solution of all problems, but when I
tried it and searched about it, I really wondered why they provide this
option at all. If you want more information about it you should check this
website out:
http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL

When I saw the mess it was, I told myself that since the tables are
automatically generated, I might as well make the most of it and only
transfer the data. I tried using Kettle:
http://pinoytech.org/question/5417386/import-mysql-dump-to-postgresql-database
but in the end I got job crashes and error message like "something
crashed, period" so since I didn't want to waste too much time on trying to
make this steam machine work, I'd rather make my own lil' script. Yeah, in
the end, it was the easiest way to me, believe me or not. ^^

Cheers ;)
L-A

___________________________________________________________
Please keep all replies on the list by using "reply all"
in your mail client.  To manage your subscriptions to this
and other Galaxy lists, please use the interface at:

  http://lists.bx.psu.edu/
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL issue: reserved keyword used as identifier

Louise-Amélie Schmitt
In reply to this post by Brad Chapman
On Fri, 8 Apr 2011 19:02:23 -0400, Brad Chapman <[hidden email]>
wrote:

> Louise-Amélie;
>
>> >> > 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.
>
> I've used this ruby script to convert a Galaxy MySQL database to
> PostgreSQL:
>
> https://github.com/maxlapshin/mysql2postgres
>
> and it worked smoothly. But definitely be safe and take a backup
> before messing with the database so you can roll back to MySQL if
> you have any issues.
>
> Brad

Oooh interesting :) thank you!

I don't really like Ruby but meh, why not after all. I still hope I'll be
able to make my script work though, since it's almost complete, but I'm
keeping your solution too.

Cheers,
L-A
___________________________________________________________
Please keep all replies on the list by using "reply all"
in your mail client.  To manage your subscriptions to this
and other Galaxy lists, please use the interface at:

  http://lists.bx.psu.edu/