Re: INSERT INTO FROM SELECT

"lmanorders" <lmanorders@gmail.com> writes:
> Here are the commands I’m using:

> CREATE TEMP SEQUENCE rprtfrmt_seq INCREMENT BY 50 START WITH 50;
> INSERT INTO rprtfrmt (lineno, bdgtacct, prntline, addline, totllevl,
> desconly, prntunderln, balshtentry, rprttype, blnkline)
>     (SELECT nextval(‘rprtfrmt_seq’), acctno, 1,1,0,0,0,0,0,1 FROM
> accounts WHERE (accttype = 0 OR accttype = 2) ORDER BY acctno)

> It inserts all of the line numbers, account numbers, and ‘fixed’ data
> into the rprtfrmt table, but not in account number order. Any help will be
> greatly appreciated.

I assume what you mean is that the "lineno" values don't match up with the
"acctno" values the way you expected?

What you need is to make sure that the sorting happens before the
nextval() values are computed.  In the above syntax the planner may well
choose to compute the output rows (including nextval()s) before it sorts.
You could check what's happening with EXPLAIN VERBOSE.

The recommended fix is to do something like

INSERT ...
SELECT nextval(‘rprtfrmt_seq’), acctno, 1,1,0,0,0,0,0,1 FROM
(SELECT * FROM accounts WHERE (accttype = 0 OR accttype = 2) ORDER BY
acctno) ss;

regards, tom lane

Thanks Tom! This works great. I appreciate the clarification.
Lynn