Обсуждение: 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
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
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 > > >
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.
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. >
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.
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.
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!"