dump order by

Hello,

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

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

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