Обсуждение: Exporting 1 text column from all rows from a table to a file per row?

Hi,

Is it possible, and if so how, to export a single column of a table into
a separate file per row? I have a table with ~21000 rows that have a
column "body1" containing ASCII text and I want to have 21000 separate
ASCII files, each containing that column "body1". The name of the file
does not matter, although it would be nice if they had the extension
"txt".

TIA

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl

On 21 November 2011 19:10, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl> wrote:
> Hi,
>
> Is it possible, and if so how, to export a single column of a table into
> a separate file per row? I have a table with ~21000 rows that have a
> column "body1" containing ASCII text and I want to have 21000 separate
> ASCII files, each containing that column "body1". The name of the file
> does not matter, although it would be nice if they had the extension
> "txt".

Does the data contain newlines?  If not, you can just export it to a
single file then use:

split -l 1 exportedfile.txt

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:

> Hi,
>
> Is it possible, and if so how, to export a single column of a table
> into a separate file per row? I have a table with ~21000 rows that
> have a column "body1" containing ASCII text and I want to have 21000
> separate ASCII files, each containing that column "body1". The name
> of the file does not matter, although it would be nice if they had
> the extension "txt".

run a script like this and save to a file that you will later run sh
on...

psql -Atqf thatscript >sh_commands

create temp sequence s;

select 'cat <<! >' || nextval('s')::text || '.txt
' || somefield || '
!'
from sometable;

> TIA
>
> --
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> web: www.askesis.nl
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144

Hi,

On 22 November 2011 06:10, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl> wrote:
> Is it possible, and if so how, to export a single column of a table into
> a separate file per row? I have a table with ~21000 rows that have a
> column "body1" containing ASCII text and I want to have 21000 separate
> ASCII files, each containing that column "body1". The name of the file
> does not matter, although it would be nice if they had the extension
> "txt".

Something like this could do the job:

for i in `psql -a -t -c 'select id from T'`; do psql -a -t -c "select
body1 from T where id = $i" -o $i.txt; done;

where 'T' is your table and 'id' is primary column in that table. I've
omitted connection options (-h -U ...) from psql commands.

--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)