Re: quick question abt pg_dump and restore

On Jan 9, 2008 9:35 AM, A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com>
wrote:

> 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:
> >
> > >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.
>
> Right, but within the table the rows hasn't an order. You can see this
> 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?
>
> 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 .
Thanks
josh