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 <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
-- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
