Thread: newbie sql question...

newbie sql question...

From
Jason Ziegler
Date:
Hello folks, I'm new to this list, and have been putting up with mysql
for too long, so please forgive me for asking such a newbie question as
this:

I have a query that works in mysql:
"INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text
stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"

When I try this in postgresql, it won't work:
"INSERT INTO table1 ( 'id', 'column2', 'column3' ) VALUES ( '1', 'text
stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"

I think I'm getting the syntax wrong for stringing together multiple
value sets or something.
Can anyone point me in the correct direction of how to do this
properly, please?

Thanks,

jz


Re: newbie sql question...

From
Jean-Christian Imbeault
Date:
Jason Ziegler wrote:
>
> I have a query that works in mysql:
> "INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text stuff',
> 'text stuff' ), ( '2', 'more text', 'even more text')"

I'm not SQL expert bunt I don't think the INSERT statement will let you
insert more than one set of values at a time. You need to do one INSERT
for each set of values. Someone *will* correct me if I am wrong :)

You need:

INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text stuff',
'text stuff' );
INSERT INTO table1 ( id, column2, column3 ) VALUES ( '2', 'more text',
'even more text');

HTH,

Jean-Christian Imbeault




Re: newbie sql question...

From
"Vincent Hikida"
Date:
I'm a newbie too but I believe that you need to do:

INSERT INTO table1 ( id, column2, column3 )
values (1,'text stuff','text stuff');
INSERT INTO table1 ( id, column2, column3 )
values (2,'more text stuff','text stuff');

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "Vincent Hikida" <vhikida@inreach.com>
To: "Jason Ziegler" <moo@zigfam.org>
Sent: Monday, May 26, 2003 10:48 PM
Subject: Re: [GENERAL] newbie sql question...


> I'm a newbie too but I believe that you need to do:
>
> INSERT INTO table1 ( id, column2, column3 )
> values (1,'text stuff','text stuff');
> INSERT INTO table1 ( id, column2, column3 )
> values (2,'more text stuff','text stuff');
>
> Vincent Hikida,
> Member of Technical Staff - Urbana Software, Inc.
> "A Personalized Learning Experience"
>
> www.UrbanaSoft.com
>
> ----- Original Message -----
> From: "Jason Ziegler" <moo@zigfam.org>
> To: <pgsql-general@postgresql.org>
> Sent: Monday, May 26, 2003 10:07 PM
> Subject: [GENERAL] newbie sql question...
>
>
> > Hello folks, I'm new to this list, and have been putting up with mysql
> > for too long, so please forgive me for asking such a newbie question as
> > this:
> >
> > I have a query that works in mysql:
> > "INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text
> > stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"
> >
> > When I try this in postgresql, it won't work:
> > "INSERT INTO table1 ( 'id', 'column2', 'column3' ) VALUES ( '1', 'text
> > stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"
> >
> > I think I'm getting the syntax wrong for stringing together multiple
> > value sets or something.
> > Can anyone point me in the correct direction of how to do this
> > properly, please?
> >
> > Thanks,
> >
> > jz
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>


Re: newbie sql question...

From
Stephan Szabo
Date:
On Tue, 27 May 2003, Jason Ziegler wrote:

> Hello folks, I'm new to this list, and have been putting up with mysql
> for too long, so please forgive me for asking such a newbie question as
> this:
>
> I have a query that works in mysql:
> "INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text
> stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"
>
> When I try this in postgresql, it won't work:
> "INSERT INTO table1 ( 'id', 'column2', 'column3' ) VALUES ( '1', 'text
> stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"

There are two problems with this.  The first is that postgresql doesn't
yet support multiple rows in the values section iirc, the second is that
single quoting the column names will give a parse error.

You can either use multiple inserts or possibly copy as workarounds.


Re: newbie sql question...

From
Jason Ziegler
Date:
Dear Vincent, Stephan & Jean-Christian,

Thank you for your replies!
I ended up doing exactly what you all advised which of course worked
just fine.

Jason


On Tuesday, May 27, 2003, at 10:41  AM, Stephan Szabo wrote:

> On Tue, 27 May 2003, Jason Ziegler wrote:
>
>> Hello folks, I'm new to this list, and have been putting up with mysql
>> for too long, so please forgive me for asking such a newbie question
>> as
>> this:
>>
>> I have a query that works in mysql:
>> "INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text
>> stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"
>>
>> When I try this in postgresql, it won't work:
>> "INSERT INTO table1 ( 'id', 'column2', 'column3' ) VALUES ( '1', 'text
>> stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"
>
> There are two problems with this.  The first is that postgresql doesn't
> yet support multiple rows in the values section iirc, the second is
> that
> single quoting the column names will give a parse error.
>
> You can either use multiple inserts or possibly copy as workarounds.
>


Re: newbie sql question...

From
Bruno Wolff III
Date:
On Tue, May 27, 2003 at 00:07:48 -0500,
  Jason Ziegler <moo@zigfam.org> wrote:
> Hello folks, I'm new to this list, and have been putting up with mysql
> for too long, so please forgive me for asking such a newbie question as
> this:
>
> I have a query that works in mysql:
> "INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text
> stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"

It is possible to do more than one insert in a single statment. You
can build a select list using a union between the rows being added
and use the insert from a select to load the data. I suspect that
this won't work well for really long lists. I don't know that it is
any faster than using multiple inserts in a single transaction.

Re: newbie sql question...

From
"scott.marlowe"
Date:
On Tue, 27 May 2003, Jason Ziegler wrote:

> Hello folks, I'm new to this list, and have been putting up with mysql
> for too long, so please forgive me for asking such a newbie question as
> this:
>
> I have a query that works in mysql:
> "INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text
> stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"
>
> When I try this in postgresql, it won't work:
> "INSERT INTO table1 ( 'id', 'column2', 'column3' ) VALUES ( '1', 'text
> stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"

As others have pointed out, this won't work.

If you need to insert the two records as a single transaction, then do:

begin;
insert...1
insert...2
commit;

Also, there was some discussion on the hackers list a while back about
implementing this feature.  don't know if it's in CVS tip or if anyone's
even working on it, but it's on someone's radar dish, just way down on
their list of things to do I think.


Re: newbie sql question...

From
Alvaro Herrera
Date:
On Tue, May 27, 2003 at 11:59:01AM -0600, scott.marlowe wrote:

> Also, there was some discussion on the hackers list a while back about
> implementing this feature.  don't know if it's in CVS tip or if anyone's
> even working on it, but it's on someone's radar dish, just way down on
> their list of things to do I think.

AFAIR someone actually implemented it, but in a way that was not
satisfactory to the hacker team, so it was not included.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"In Europe they call me Niklaus Wirth; in the US they call me Nickel's worth.
That's because in Europe they call me by name, and in the US by value!"