By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

COPY TO order

P: n/a
Hello,

How to make sure COPY TO writes the table lines to the file in the same order
they were inserted?

I'm producing html pages in pl/pgsql and using COPY TO to write then to file.
Occasionaly, about once in 7 or 9, the lines are copied to the file out of the
order they were inserted in the table.

The lines have one only column of the type text.

The pages are here: www.kakao.pop.com.br

Regards,
Clodoaldo Pinto Neto

__________________________________________________ ____________________

Yahoo! Messenger - Fale com seus amigos online. Instale agora!
http://br.download.yahoo.com/messenger/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Centuries ago, Nostradamus foresaw when cl*************@yahoo.com.br (Clodoaldo Pinto Neto) would write:
How to make sure COPY TO writes the table lines to the file in the same order
they were inserted?
You probably want to rewrite PostgreSQL then.
I'm producing html pages in pl/pgsql and using COPY TO to write then
to file. Occasionaly, about once in 7 or 9, the lines are copied to
the file out of the order they were inserted in the table.


If you need to maintain data in some order, then you need to add a key
field that indicates that ordering, and use ORDER BY in order to
select the data in that order.

That will involve not using COPY TO.
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/lisp.html
Would-be National Mottos:
Poland: "We probably would have had a happier history if we were
between Canada and Mexico, not Germany and Russia."
Nov 23 '05 #2

P: n/a
Christopher Browne wrote:
Centuries ago, Nostradamus foresaw when cl*************@yahoo.com.br (Clodoaldo Pinto Neto) would write:

How to make sure COPY TO writes the table lines to the file in the same order
they were inserted?


You probably want to rewrite PostgreSQL then.
I'm producing html pages in pl/pgsql and using COPY TO to write then
to file. Occasionaly, about once in 7 or 9, the lines are copied to
the file out of the order they were inserted in the table.


If you need to maintain data in some order, then you need to add a key
field that indicates that ordering, and use ORDER BY in order to
select the data in that order.

That will involve not using COPY TO.

Not really.

If you have a 'serial' or 'bigserial' field like this :

create table test_table (
test_id bigserial,
data integer,
comment text
);

and you use :

copy test_table (data,comment)
from '/wherever/the/file/is'
using delimiters ',';
to insert data like this :

27,some kind of entry
32,another kind of entry
16,yet another entry
....

Assuming this is the first set of data entered the table will get populated with :

1 | 27 | some kind of entry
2 | 32 | another kind of entry
3 | 16 | yet another entry
....

I have used this in the past and it works well.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

P: n/a
--- Guy Fraser <gu*@incentre.net>
If you have a 'serial' or 'bigserial' field like this :

create table test_table (
test_id bigserial,
data integer,
comment text
);

and you use :

copy test_table (data,comment)
from '/wherever/the/file/is'
using delimiters ',';
to insert data like this :

27,some kind of entry
32,another kind of entry
16,yet another entry
...

Assuming this is the first set of data entered the table will get populated
with :

1 | 27 | some kind of entry
2 | 32 | another kind of entry
3 | 16 | yet another entry
...

I have used this in the past and it works well.


The problem I have is with COPY TO and not COPY FROM as I need to write a file.

Regards,
Clodoaldo

__________________________________________________ ____________________

Yahoo! Messenger - Fale com seus amigos online. Instale agora!
http://br.download.yahoo.com/messenger/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #4

P: n/a
Ahh, I see.

Like this from the command line :

psql --no-align --tuples-only --field-separator , -c "select
data,comment from test_table order by test_id ;" database >/tmp/file

From psql prompt :

\a\t\f,
select data,comment from test_table order by test_id \g /tmp/file
\a\t\f|

Either way you should get a file {/tmp/file} contaning :

27,some kind of entry
32,another kind of entry
16,yet another entry
....

Clodoaldo Pinto Neto wrote:
--- Guy Fraser <gu*@incentre.net>

If you have a 'serial' or 'bigserial' field like this :

create table test_table (
test_id bigserial,
data integer,
comment text
);

and you use :

copy test_table (data,comment)
from '/wherever/the/file/is'
using delimiters ',';
to insert data like this :

27,some kind of entry
32,another kind of entry
16,yet another entry
...

Assuming this is the first set of data entered the table will get populated
with :

1 | 27 | some kind of entry
2 | 32 | another kind of entry
3 | 16 | yet another entry
...

I have used this in the past and it works well.


The problem I have is with COPY TO and not COPY FROM as I need to write a file.

....snip...

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #5

P: n/a
Thank You Guy,

As you probably already read I solved it writing the whole html page into a
single table line.

I don't know if your solution would do it:

It happens inside a pl/pgsql function. The file names varies like t1.html,
t2.html, etc. where the t# is defined inside a FOR row IN select_query LOOP.
The written table have its rows deleted in all interactions after it is COPYed
TO.

Is it possible to redirect output from inside a pl/pgsql function?

Regards,
Clodoaldo

--- Guy Fraser <gu*@incentre.net> escreveu: > Ahh, I see.

Like this from the command line :

psql --no-align --tuples-only --field-separator , -c "select
data,comment from test_table order by test_id ;" database >/tmp/file

From psql prompt :

\a\t\f,
select data,comment from test_table order by test_id \g /tmp/file
\a\t\f|

Either way you should get a file {/tmp/file} contaning :

27,some kind of entry
32,another kind of entry
16,yet another entry
...

__________________________________________________ ____________________

Yahoo! Messenger - Fale com seus amigos online. Instale agora!
http://br.download.yahoo.com/messenger/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.