COPY TO order

Centuries ago, Nostradamus foresaw when clodoaldo_pinto@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."

Christopher Browne wrote:

Centuries ago, Nostradamus foresaw when clodoaldo_pinto@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.

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.

I absolutely need to write to a file.

Solved it writing all the html page to a single table line so COPY TO has no
way now to write one only line out of order.

Regards,
Clodoaldo

______________________________________________________________________

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

 --- Guy Fraser <guy@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/

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

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 <guy@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/