Re: dump order by - Mailing list pgsql-general

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.soares@sferacarta.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general