Thread: INSERT INTO row value constructors

INSERT INTO row value constructors

From
"Brandon Aiken"
Date:
This is just an odd question.  Previously RDBMSs (MySQL, SQLite, IIRC) I've used have allowed row value constructors
(optionalfeature F641 in SQL'03) on INSERT statements.  That is: 

INSERT INTO mytable
VALUES (0,'hello'),(1,'world');

Is essentially shorthand for:

INSERT INTO mytable VALUES (0,'hello');
INSERT INTO mytable VALUES (1,'world');

Under PostgreSQL, though, the parser thinks the row value constructors are arrays.  This isn't a problem, per se,
exceptto make me realize that PostgreSQL is unlikely to adopt F641. 

Is that correct, or is this behavior expected to change?

This is just a curiosity question.  I expect that PostgreSQL would behave in a similar manner performance wise inside a
transaction. It just struck me as odd when it didn't work. 

--
Brandon Aiken
CS/IT Systems Engineer
 
Confidentiality Notice
This email, including attachments, may include confidential and/or proprietary information, and may be used only by the
personor entity to which it is addressed.  If the reader of this email is not the intended recipient or his/her
authorizedagent, the reader is hereby notified that any dissemination, distribution or copying of this email is
prohibited. If you have received this email in error, please notify the sender by replying to this message and deleting
thisemail immediately. 


Re: INSERT INTO row value constructors

From
Martijn van Oosterhout
Date:
On Wed, Dec 13, 2006 at 04:55:00PM -0500, Brandon Aiken wrote:
> This is just an odd question.  Previously RDBMSs (MySQL, SQLite, IIRC) I've used have allowed row value constructors
(optionalfeature F641 in SQL'03) on INSERT statements.  That is: 
>
> INSERT INTO mytable
> VALUES (0,'hello'),(1,'world');

<snip>

> Is that correct, or is this behavior expected to change?

It's in 8.2, see the documentation:

http://www.postgresql.org/docs/current/static/dml-insert.html

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: INSERT INTO row value constructors

From
"Brandon Aiken"
Date:
Cool.

Now I just have to wait for Debian backports, or figure it out for
myself if I can find the time.

*sigh* 8.2 isn't even in Portage yet.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: Wednesday, December 13, 2006 5:01 PM
To: Brandon Aiken
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] INSERT INTO row value constructors

On Wed, Dec 13, 2006 at 04:55:00PM -0500, Brandon Aiken wrote:
> This is just an odd question.  Previously RDBMSs (MySQL, SQLite, IIRC)
I've used have allowed row value constructors (optional feature F641 in
SQL'03) on INSERT statements.  That is:
>
> INSERT INTO mytable
> VALUES (0,'hello'),(1,'world');

<snip>

> Is that correct, or is this behavior expected to change?

It's in 8.2, see the documentation:

http://www.postgresql.org/docs/current/static/dml-insert.html

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability
to litigate.