Thread: INSERT INTO FROM SELECT
I’m using Postgres 9.3 on Windows. I am attempting to insert several rows into a table using data from another table. It is inserting correctly, but the order isn’t correct. Is an ‘order by’ command not recognized in this situation?
Here are the two tables:
CREATE TABLE accounts (
acctno char(22) PRIMARY KEY,
acctdesc varchar(60),
...
accttype integer
);
CREATE TABLE rprtfrmt (
lineno integer PRIMAY KEY,
bdgtacct char(22),
prntline integer,
addline integer,
totllevl integer,
desconly integer,
prntundrln integer,
balshtentry integer,
rprttype integer,
blnkline integer
);
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.
Thanks,
Lynn
Hi Lynn, To the best of my knowledge the SQL standard doesn't define that data inside a table needs to be stored in any given order. If you expect to see it in a certain sequence define that at query time, when you retrieve the data? Cheers, Andrej On 3 June 2014 11:52, lmanorders <lmanorders@gmail.com> wrote: > I’m using Postgres 9.3 on Windows. I am attempting to insert several rows > into a table using data from another table. It is inserting correctly, but > the order isn’t correct. Is an ‘order by’ command not recognized in this > situation? > > Here are the two tables: > CREATE TABLE accounts ( > acctno char(22) PRIMARY KEY, > acctdesc varchar(60), > ... > accttype integer > ); > > CREATE TABLE rprtfrmt ( > lineno integer PRIMAY KEY, > bdgtacct char(22), > prntline integer, > addline integer, > totllevl integer, > desconly integer, > prntundrln integer, > balshtentry integer, > rprttype integer, > blnkline integer > ); > > 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. > Thanks, > Lynn > -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.georgedillon.com/web/html_email_is_evil.shtml
Thanks Andrej. The problem seems to be that inside the 'insert' command, the 'select' command isn't returning the accounts 'acctno' in 'acctno' order, even though I'm including an 'order by acctno' option. The way the insert command is written, I'm expecting the 'lineno' and 'bdgtacct' to both end up in ascending order. If I query rprtfrmt and request 'order by lineno', the 'bdgtacct's should also be in order, but they aren't. (I hope this makes sense.) Thanks, Lynn -----Original Message----- From: Andrej Sent: Monday, June 02, 2014 6:00 PM To: lmanorders Cc: pgsql-novice Subject: Re: [NOVICE] INSERT INTO FROM SELECT Hi Lynn, To the best of my knowledge the SQL standard doesn't define that data inside a table needs to be stored in any given order. If you expect to see it in a certain sequence define that at query time, when you retrieve the data? Cheers, Andrej On 3 June 2014 11:52, lmanorders <lmanorders@gmail.com> wrote: > I’m using Postgres 9.3 on Windows. I am attempting to insert several rows > into a table using data from another table. It is inserting correctly, but > the order isn’t correct. Is an ‘order by’ command not recognized in this > situation? > > Here are the two tables: > CREATE TABLE accounts ( > acctno char(22) PRIMARY KEY, > acctdesc varchar(60), > ... > accttype integer > ); > > CREATE TABLE rprtfrmt ( > lineno integer PRIMAY KEY, > bdgtacct char(22), > prntline integer, > addline integer, > totllevl integer, > desconly integer, > prntundrln integer, > balshtentry integer, > rprttype integer, > blnkline integer > ); > > 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. > Thanks, > Lynn > -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.georgedillon.com/web/html_email_is_evil.shtml
"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
BYacctno)
> It inserts all of the line numbers, account numbers, and ‘fixed’ data into the rprtfrmt table, but not in account
numberorder. 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
"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