Thread: insert into...
Hi,
i would like to understand why the following INSERT INTO statement works :
INSERT INTO mytable
SELECT nextval('my_sequence'),
'myname',
'myfirstname'
;
whereas usually we should do :
INSERT INTO mytable
VALUES
(
SELECT nextval('my_sequence'),
'myname',
'myfirstname'
);
thanks a lot,
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
i would like to understand why the following INSERT INTO statement works :
INSERT INTO mytable
SELECT nextval('my_sequence'),
'myname',
'myfirstname'
;
whereas usually we should do :
INSERT INTO mytable
VALUES
(
SELECT nextval('my_sequence'),
'myname',
'myfirstname'
);
thanks a lot,
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
On Dec 9, 2007, at 11:05 , Alain Roger wrote: > Hi, > > i would like to understand why the following INSERT INTO statement > works : > > INSERT INTO mytable > SELECT nextval('my_sequence'), > 'myname', > 'myfirstname' > ; > > whereas usually we should do : > > INSERT INTO mytable > VALUES > ( > SELECT nextval('my_sequence'), > 'myname', > 'myfirstname' > ); > Well, imho, if the sequence was set up via serial (or otherwise is set as the default for the first column), I think the easiest way is : INSERT INTO mytable (name, firstname) VALUES ('myname', 'myfirstname'); No need to include the nextval call at all. If you look at the INSERT synoposis: http://www.postgresql.org/docs/8.2/static/sql-insert.html INSERT INTO table [ ( column [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ RETURNING * | output_expression [ AS output_name ] [, ...] ] you can see that a VALUES expression or a query are legitimate forms for INSERT. The query form is particularly useful if you'd like to insert a number of rows that are the result of a SELECT. For example, when loading data from a temp table. INSERT INTO mytable (name, firstname) SELECT name, firstname FROM temp_table; Michael Glaesemannn grzm seespotcode net
Values is optional. We support insert into select Dave On 9-Dec-07, at 11:05 AM, Alain Roger wrote: > Hi, > > i would like to understand why the following INSERT INTO statement > works : > > INSERT INTO mytable > SELECT nextval('my_sequence'), > 'myname', > 'myfirstname' > ; > > whereas usually we should do : > > INSERT INTO mytable > VALUES > ( > SELECT nextval('my_sequence'), > 'myname', > 'myfirstname' > ); > > > thanks a lot, > > -- > Alain > ------------------------------------ > Windows XP SP2 > PostgreSQL 8.2.4 / MS SQL server 2005 > Apache 2.2.4 > PHP 5.2.4 > C# 2005-2008
> "Alain Roger" <raf.news@gmail.com> writes: > i would like to understand why the following INSERT INTO statement works : > INSERT INTO mytable > SELECT nextval('my_sequence'), > 'myname', > 'myfirstname' > ; This is a perfectly standard INSERT ... SELECT query. > whereas usually we should do : > INSERT INTO mytable > VALUES > ( > SELECT nextval('my_sequence'), > 'myname', > 'myfirstname' > ); If you'd tried that, you would find that it *does not* work: regression=# INSERT INTO mytable regression-# VALUES regression-# ( regression(# SELECT nextval('my_sequence'), regression(# 'myname', regression(# 'myfirstname' regression(# ); ERROR: syntax error at or near "SELECT" LINE 4: SELECT nextval('my_sequence'), ^ You could make it work by turning the SELECT into a parenthesized sub-SELECT: INSERT INTO mytable VALUES ( (SELECT nextval('my_sequence')), 'myname', 'myfirstname' ); but this is just pointless complexity. The standard idiom is INSERT INTO mytable VALUES ( nextval('my_sequence'), 'myname', 'myfirstname' ); or as already noted, leave out the column entirely and rely on the default expression. regards, tom lane