On Jan 9, 2008 9:35 AM, A. Kretschmer <andreas.kretschmer@xxxxxxxxxxxxxx> wrote:
All my requirement is that the dumped table in database2 should be in the same order as the original table(at the time of dump) in database1 .
am Wed, dem 09.01.2008, um 14:07:13 +0000 mailte Raymond O'Donnell folgendes:> On 09/01/2008 14:02, Josh Harrison wrote:Right, but within the table the rows hasn't an order. You can see this
>
> >When restoring the pg_dumped data thro psql does the rows of the table
> >are restored in the same order? ie for example if
> >Table A has rows r1,r2,r3,r4,r5 in this order, then if I pg_dump and
> >restore it to another database, will it have the rows in the same order
> >r1,r2,r3,r4,r5? Does this apply to big tables also?
>
> If you use the text dump format, you can see the order in which the rows
> are restored.
when you select the ctid-column:
test=# create table order_test (i int);
CREATE TABLE
test=*# insert into order_test values (1);
INSERT 0 1
test=*# insert into order_test values (2);
INSERT 0 1
test=*# insert into order_test values (3);
INSERT 0 1
test=*# select ctid, i from order_test ;
ctid | i
-------+---
(0,1) | 1
(0,2) | 2
(0,3) | 3
(3 rows)
test=*# update order_test set i=20 where i=2;
UPDATE 1
test=*# update order_test set i=2 where i=20;
UPDATE 1
test=*# select ctid, i from order_test ;
ctid | i
-------+---
(0,1) | 1
(0,3) | 3
(0,5) | 2
(3 rows)
Now a pg_dump:
ALTER TABLE public.order_test OWNER TO webmaster;
--
-- Data for Name: order_test; Type: TABLE DATA; Schema: public; Owner:
webmaster
--
COPY order_test (i) FROM stdin;
1
3
2
\.
Now the question: what is the correct order?
Thanks
josh
