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.soa...@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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to