Thread: dump order by

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

On 12/22/2012 09:29 AM, jo 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)
>

Why not use the COPY command directly?:

http://www.postgresql.org/docs/9.2/interactive/sql-copy.html

FYI:

"COPY with a file name instructs the PostgreSQL server to directly read
from or write to a file. The file must be accessible to the server and
the name must be specified from the viewpoint of the server. When STDIN
or STDOUT is specified, data is transmitted via the connection between
the client and the server.
"

If you want to work from the client you can use \copy in psql.

--
Adrian Klaver
adrian.klaver@gmail.com

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