Обсуждение: 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
Hello,
Did you try to set the constraints as deferrable
i.e.
begin;
set constraints all deferred;
...
commit;
From: jo <jose.soares@sferacarta.com>
To: pgsql-general@postgresql.org
Sent: Saturday, December 22, 2012 6:29 PM
Subject: [GENERAL] 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
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Did you try to set the constraints as deferrable
i.e.
begin;
set constraints all deferred;
...
commit;
Also, you can set this by forign key , have a look on
http://www.postgresql.org/docs/9.0/static/sql-createtable.html
Regards
From: jo <jose.soares@sferacarta.com>
To: pgsql-general@postgresql.org
Sent: Saturday, December 22, 2012 6:29 PM
Subject: [GENERAL] 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
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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