Thread: COPY TO order

COPY TO order

From
Clodoaldo Pinto Neto
Date:
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/

Re: COPY TO order

From
Christopher Browne
Date:
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."

Re: COPY TO order

From
Guy Fraser
Date:
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.




Re: COPY TO order

From
Clodoaldo Pinto Neto
Date:
>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/

Re: COPY TO order

From
Clodoaldo Pinto Neto
Date:
 --- 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/

Re: COPY TO order

From
Guy Fraser
Date:
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...



Re: COPY TO order

From
Clodoaldo Pinto Neto
Date:
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/