Postgresql: Unexpected Insertion Result

Question

Version: PostgreSQL 9.4.2

   Column   |  Type   |                           Modifiers                            
------------+---------+----------------------------------------------------------------
 id         | integer | not null default nextval('T1_id_seq'::regclass)
 name       | text    | 
 value      | text    | 
 parent_id  | integer | 

Indexes:
    "T1_pkey" PRIMARY KEY, btree (id)
    "T1_id_idx" btree (id)

I have two tables like this in Postgresql, say T1 and T2 with tree like data structure referencing data from own table.

I need to modify some rows in T1 and insert it to T2 in the exact order as the rows appeared in T1. What I have done thus far is copy the relevant rows from table T1 to a temporary table T3 for data modification and insert everything from T3 to T2 when changes' made.

T3 is created using

CREATE TABLE T3 (LIKE T1 INCLUDING ALL)
INSERT * INTO T3 SELECT * FROM T1

The end result is rather strange. All the data from T3 were copied to T2, but the order of the ids seems to be random.

However the result is correct if I invoke the same script to copy data from T1 to T3 directly. What is even more bizarre is it's also correct if if I split the above script into two separate script to

Create T3 from T1 and copy data from T1 to T3
Copy T3 to T2 using INSERT method.

Any clues?

Rows in a relational database are NOT sorted. So "the order of ids seems to be random" is perfectly OK because there is no such thing as "the order of the rows". The only, (really: the only) way to get a specific order or rows is to use order by in a select statement — user330315
– user330315, Commented Jun 16, 2015 at 18:57

Craig Ringer · Accepted Answer · 2015-06-16 23:58:40Z

1

You didn't specify an ORDER BY clause. Without one, PostgreSQL might fetch the rows for your SELECT in whatever order happens to be fastest to execute.

Try:

CREATE TABLE T3 (LIKE T1 INCLUDING ALL);

INSERT INTO T3
SELECT * FROM T1 ORDER BY T1.id;

Note that strictly there is no guarantee that the INSERT of multiple rows will process rows in the order they are read from the SELECT, but in practice PostgreSQL at this time will always process them in order and it's not likely to change in a hurry.

answered Jun 16, 2015 at 23:58

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql: Unexpected Insertion Result

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related