Postgres Pro
Downloads
Home   >   mailing lists

Thread: 'order by' in an insert into command

'order by' in an insert into command

From
Mike Nolan
Date:
I have the following insert to populate a new table:

insert into pending_tnmt_sec
select tseceventid, tsecsecno,
nextval('sec_seq'),
tsecrtddt
from tnmtsec
order by tsecrtddt,tseceventid,tsecsecno;

I need to access this data in a particular order which may change over
time but the initial order I want is in the order by clause.

The problem is, I'm not getting the data into the right order based
on the sequence values being inserted:

tsecrtddt        tseceventid   tsecsecno      seq

2004-08-30     | 20040731910 |         1 | 356270    ### out of sequence
2004-07-08     | 20040531897 |         2 | 360792
2004-06-03     | 20040425023 |         1 | 354394
2004-04-23     | 20040320702 |         1 | 353557
2004-02-18     | 20040117178 |         2 | 359387    ### out of sequence
2004-01-10     | 20031213418 |         1 | 351315

I can't tell whether this is because the order by clause in the insert
is being ignored or because the sequence is incrememted before the sort
takes place.  Is there a way to do this insert?
--
Mike Nolan

Re: 'order by' in an insert into command

From
Jean-Luc Lachance
Date:
Try:

insert into pending_tnmt_sec
select tseceventid, tsecsecno,
nextval('sec_seq'),
tsecrtddt
from (
   select tseceventid, tsecsecno, tsecrtddt
   from tnmtsec
   order by tsecrtddt,tseceventid,tsecsecno) as ss;




Mike Nolan wrote:

> I have the following insert to populate a new table:
>
> insert into pending_tnmt_sec
> select tseceventid, tsecsecno,
> nextval('sec_seq'),
> tsecrtddt
> from tnmtsec
> order by tsecrtddt,tseceventid,tsecsecno;
>
> I need to access this data in a particular order which may change over
> time but the initial order I want is in the order by clause.
>
> The problem is, I'm not getting the data into the right order based
> on the sequence values being inserted:
>
> tsecrtddt        tseceventid   tsecsecno      seq
>
> 2004-08-30     | 20040731910 |         1 | 356270    ### out of sequence
> 2004-07-08     | 20040531897 |         2 | 360792
> 2004-06-03     | 20040425023 |         1 | 354394
> 2004-04-23     | 20040320702 |         1 | 353557
> 2004-02-18     | 20040117178 |         2 | 359387    ### out of sequence
> 2004-01-10     | 20031213418 |         1 | 351315
>
> I can't tell whether this is because the order by clause in the insert
> is being ignored or because the sequence is incrememted before the sort
> takes place.  Is there a way to do this insert?
> --
> Mike Nolan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

Re: 'order by' in an insert into command

From
Richard Huxton
Date:
Mike Nolan wrote:
> I have the following insert to populate a new table:
>
> insert into pending_tnmt_sec
> select tseceventid, tsecsecno,
> nextval('sec_seq'),
> tsecrtddt
> from tnmtsec
> order by tsecrtddt,tseceventid,tsecsecno;
>
> I need to access this data in a particular order which may change over
> time but the initial order I want is in the order by clause.
>
> The problem is, I'm not getting the data into the right order based
> on the sequence values being inserted:

In your example, I would expect the nextval() to be called during the
"fetch", before the ordering. You could probably do something like:

INSERT INTO pending_tnmt_sec
SELECT foo.*, nextval('sec_seq') FROM
   (
     SELECT tseceventid, ...
     ORDER BY tsecrtddt,tseceventid,tsecsecno
   ) AS foo
;

I'm not sure whether the SQL standard requires the ORDER BY to be
processed in the sub-select. From a relational viewpoint, I suppose you
could argue that ordering is strictly an output feature.

--
   Richard Huxton
   Archonet Ltd

Re: 'order by' in an insert into command

From
Tom Lane
Date:
Richard Huxton <dev@archonet.com> writes:
> In your example, I would expect the nextval() to be called during the
> "fetch", before the ordering. You could probably do something like:

> INSERT INTO pending_tnmt_sec
> SELECT foo.*, nextval('sec_seq') FROM
>    (
>      SELECT tseceventid, ...
>      ORDER BY tsecrtddt,tseceventid,tsecsecno
>    ) AS foo
> ;

> I'm not sure whether the SQL standard requires the ORDER BY to be
> processed in the sub-select. From a relational viewpoint, I suppose you
> could argue that ordering is strictly an output feature.

I believe the SQL standard disallows this entirely, precisely because it
considers ordering to be strictly an output feature.  Postgres will take
it though (in recent releases), and should produce the results Mike wants.

            regards, tom lane