Thread: 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@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/