Re: dump order by

I'm sorry my first example was incomplete
I need to migrate data from postgresql to oracle
thus I have to use
dump --column-inserts instead of copy
to have an output like this but order by pk:

INSERT INTO test (id, note, id_father) VALUES (6, 'Homer Simpson ', 5);
INSERT INTO test (id, note, id_father) VALUES (5, 'Abraham Simpson', NULL);
INSERT INTO test (id, note, id_father) VALUES (10, 'Maggie Simpson ', 6);
INSERT INTO test (id, note, id_father) VALUES (1, 'Ned Flanders ', NULL);
INSERT INTO test (id, note, id_father) VALUES (2, 'Maude Flanders ', NULL);
INSERT INTO test (id, note, id_father) VALUES (9, 'Bart Simpson ', 6);
INSERT INTO test (id, note, id_father) VALUES (3, 'Rod Flanders ', 1);
INSERT INTO test (id, note, id_father) VALUES (4, 'Todd Flanders ', 1);
INSERT INTO test (id, note, id_father) VALUES (7, 'Marge Simpson ', NULL);
INSERT INTO test (id, note, id_father) VALUES (8, 'Lisa Simpson ', 6);

j

On 12/22/2012 10:35 PM, Robert Treat wrote:
> You can COPY based on a select statement, so if you copy to stdout
> with a select with an order by clause, it should sort the data the way
> you want.
>
> Robert Treat
> conjecture: xzilla.net
> consulting: omniti.com
>
> On Sat, Dec 22, 2012 at 12:29 PM, jo <jose(dot)soares(at)sferacarta(dot)com> wrote:
>> Hi all,
>> I would like to know if it is possible to dump a table ordered by its
>> primary key.
>> Take a look at the this test table...
>> \d test
>> Table "public.test"
>> Column | Type | Modifiers
>> -----------+---------+---------------------------------------------------
>> id | integer | not null name | text |
>> id_father | integer |
>> Indexes:
>> "test_pkey" PRIMARY KEY, btree (id)
>> Foreign-key constraints:
>> "test_id_father_fkey" FOREIGN KEY (id_father) REFERENCES test(id)
>>
>> select * from test;
>> id | nome | id_father
>> ----+-----------------+-----------
>> 6 | Homer Simpson | 5
>> 5 | Abraham Simpson |
>> 10 | Maggie Simpson | 6
>> 1 | Ned Flanders |
>> 2 | Maude Flanders |
>> 9 | Bart Simpson | 6
>> 3 | Rod Flanders | 1
>> 4 | Todd Flanders | 1
>> 7 | Marge Simpson |
>> 8 | Lisa Simpson | 6
>> (10 rows)
>>
>> I would like to dump the table with this order:
>>
>> COPY test (id, nome, id_father) FROM stdin;
>> 1 Ned Flanders \N
>> 2 Maude Flanders \N
>> 3 Rod Flanders 1
>> 4 Todd Flanders 1
>> 5 Abraham Simpson \N
>> 6 Homer Simpson 5
>> 7 Marge Simpson \N
>> 8 Lisa Simpson 6
>> 9 Bart Simpson 6
>> 10 Maggie Simpson 6
>> \.
>>
>> instead it is dumped like this:
>>
>> COPY test (id, note, id_father) FROM stdin;
>> 6 Homer Simpson 5
>> 5 Abraham Simpson \N
>> 10 Maggie Simpson 6
>> 1 Ned Flanders \N
>> 2 Maude Flanders \N
>> 9 Bart Simpson 6
>> 3 Rod Flanders 1
>> 4 Todd Flanders 1
>> 7 Marge Simpson \N
>> 8 Lisa Simpson 6
>> \.
>>
>> and I can't upload the table because the foreing keys.
>>
>> j
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general