Re: [GENERAL] quick question abt pg_dump and restore

From: A. Kretschmer <andreas.kretschmer(at)schollglas.com>
Date: Wed Jan 09 2008 - 09:35:54 EST

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?


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   
http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

Received on Wed Jan 9 09:41:20 2008