Re: COPY TO order

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/

Centuries ago, Nostradamus foresaw when clodoaldo_pinto(at)yahoo(dot)com(dot)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(at)yahoo(dot)com(dot)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.

--- Guy Fraser <guy(at)incentre(dot)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(at)incentre(dot)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(at)incentre(dot)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/