Thread: insert order question
Hi:
Does...
insert into mytbl (col1) values ('a'),('b'),('c');
... insert records 'a','b','c' in that order while...
insert into mytbl (col1) values ('c'),('b'),('a');
... insert the records in the opposite order?
The order matters because there are triggers on the table which will react differently depending on what's already in the table.
Thanks in Advance !
See here:
http://www.postgresql.org/docs/9.0/static/sql-values.html
Implied is that the supplied data set will be returned in the same order as written unless an ORDER BY is used to re-order the listing prior to it being spit out the other end.
1, 3, 2 = > VALUES = > 1, 3, 2
1, 3, 2 = > VALUES ORDER ASC = > 1, 2, 3
The only time you end up with ordering issues is the “FROM ‘physical table’” because there is no defined order for how those records are stored into memory; but when you explicitly list a set of data that explicit order is maintained as long as possible.
David J.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gauthier, Dave
Sent: Thursday, May 12, 2011 3:37 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] insert order question
Hi:
Does...
insert into mytbl (col1) values ('a'),('b'),('c');
... insert records 'a','b','c' in that order while...
insert into mytbl (col1) values ('c'),('b'),('a');
... insert the records in the opposite order?
The order matters because there are triggers on the table which will react differently depending on what's already in the table.
Thanks in Advance !
"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> Does...
> insert into mytbl (col1) values ('a'),('b'),('c');
> ... insert records 'a','b','c' in that order while...
> insert into mytbl (col1) values ('c'),('b'),('a');
> ... insert the records in the opposite order?
I believe so, but it seems unwise to hard-wire a dependency on that into
your application, since this is only an implementation artifact and not
anything guaranteed by the standard. If you need the inserts to occur
in a specific order, issue them as separate commands ... you're not
going to save all that much by having them be one command.
regards, tom lane
Ya, I'm sort of coming to that conclusion because of a different consideration. I'm worried about whether or not the
triggerswill be fired immediately after each record inserted, or once ot the end, or something else. Just too risky.
I'mgoing to go with the discrete insert statements in the order I desire.
Thanks
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, May 12, 2011 4:06 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] insert order question
"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> Does...
> insert into mytbl (col1) values ('a'),('b'),('c');
> ... insert records 'a','b','c' in that order while...
> insert into mytbl (col1) values ('c'),('b'),('a');
> ... insert the records in the opposite order?
I believe so, but it seems unwise to hard-wire a dependency on that into
your application, since this is only an implementation artifact and not
anything guaranteed by the standard. If you need the inserts to occur
in a specific order, issue them as separate commands ... you're not
going to save all that much by having them be one command.
regards, tom lane
On 12 May 2011, at 22:07, Gauthier, Dave wrote: > Ya, I'm sort of coming to that conclusion because of a different consideration. I'm worried about whether or not the triggerswill be fired immediately after each record inserted, or once ot the end, or something else. That's just the difference between row-level triggers and statement-level triggers, so it depends on which of those you use. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4dccd14f11921626814358!