selective export for subsequent import (COPY)

am Wed, dem 18.04.2007, um 21:59:35 -0700 mailte chrisj folgendes:

I would like to do a selective export of a number of tables from a large
database to import into a smaller (test) DB.

I know about: psql dbname -tc "select * from tableX where whatever" >
tableX.dat

but unless I put it through a sed script, this file cannot be easily used
for import.

It feels like I am re-inventing the wheel. Does anybody know a better way
or have a good sed script.

If you have 8.2, than you can use COPY also for VIEWs or for SELECTS.
If not, create a temp. table as result for your SELECT and COPY this
temp. table instead the original table.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

Chris,

I know about: psql dbname -tc "select * from tableX where whatever" >
tableX.dat

What about
psql $DATABASEHOST -U $DATABASEUSER -d $DATABASE -q -P footer -A -F , -c "$DETAIL_SQL" >table.csv

To produce a comma separated file of tuples only.
If I'm not mistaken, as happens quite a bit, you can then use COPY to import the csv file.

Allan

The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments.

chrisj wrote:

I would like to do a selective export of a number of tables from a large
database to import into a smaller (test) DB.

I know about: psql dbname -tc "select * from tableX where whatever" >
tableX.dat

You might try
psql dbname -Atc "select * from tableX where whatever" > tableX.dat

to produce un-aligned output, if this is your problem.

Brent Wood

Thanks Alan,
This helped a lot, but ideally I want a tab field delimiter and -F '\t' does
not seem to work, any ideas??

I noticed one other post on this same problem of the fieldsep '\t' not
working but the only advise offered was to use \pset. Can \pset be used on
the command line, I can only get it to work within the psql command
processor.

Harvey, Allan AC wrote:

Chris,

I know about: psql dbname -tc "select * from tableX where whatever" >
tableX.dat

What about
psql $DATABASEHOST -U $DATABASEUSER -d $DATABASE -q -P footer -A -F , -c
"$DETAIL_SQL" >table.csv

To produce a comma separated file of tuples only.
If I'm not mistaken, as happens quite a bit, you can then use COPY to
import the csv file.

Allan

The material contained in this email may be confidential, privileged or
copyrighted. If you are not the intended recipient, use, disclosure or
copying of this information is prohibited. If you have received this
document in error, please advise the sender and delete the document.
Neither OneSteel nor the sender accept responsibility for any viruses
contained in this email or any attachments.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
View this message in context: http://www.nabble.com/selective-export-for-subsequent-import-%28COPY%29-tf3604927.html#a10090719
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

chrisj <chrisj.wood@sympatico.ca> writes:

This helped a lot, but ideally I want a tab field delimiter and -F '\t' does
not seem to work, any ideas??

I don't think there's any provision for backslash-notation in that
switch; you'd need to type an actual tab character there. Depending on
what shell you use, that might be a bit difficult on an interactive
shell command line, but it should be simple enough to insert one in a
script file.

regards, tom lane

chrisj wrote:

Thanks Alan,
This helped a lot, but ideally I want a tab field delimiter and -F '\t' does
not seem to work, any ideas??

I noticed one other post on this same problem of the fieldsep '\t' not
working but the only advise offered was to use \pset. Can \pset be used on
the command line, I can only get it to work within the psql command
processor.

You can always have a text file (file.sql):

\pset ...
select .....

the run the commands is a single client connection with

psql database -Atf file.sql

This runs a file of sql commands in a single psql connection instead of
opening a new connection for every -c "" command.
Thus the result of the \pset is still in force when the next sql
statement is executed.

or run your command as it is & pipe the output through tr to translate
the commas to tabs.
You can see what tr does using
echo "1,2" | tr "," "\t"

eg:
psql $DATABASEHOST -U $DATABASEUSER -d $DATABASE -q -P footer -A -F , -c "$DETAIL_SQL" | tr "," "\t" >table.csv

Cheers,

Brent Wood

Thanks Brent, very much appreciated, your first suggestion is perfect.

the translate suggestion assumes that there are no commas in the data, but
that is why I wanted to use tab.

again, thanks a lot!!

Brent Wood wrote:

chrisj wrote:

Thanks Alan,
This helped a lot, but ideally I want a tab field delimiter and -F '\t'
does
not seem to work, any ideas??

I noticed one other post on this same problem of the fieldsep '\t' not
working but the only advise offered was to use \pset. Can \pset be used
on
the command line, I can only get it to work within the psql command
processor.

You can always have a text file (file.sql):

\pset ...
select .....

the run the commands is a single client connection with

psql database -Atf file.sql

This runs a file of sql commands in a single psql connection instead of
opening a new connection for every -c "" command.
Thus the result of the \pset is still in force when the next sql
statement is executed.

or run your command as it is & pipe the output through tr to translate
the commas to tabs.
You can see what tr does using
echo "1,2" | tr "," "\t"

eg:
psql $DATABASEHOST -U $DATABASEUSER -d $DATABASE -q -P footer -A -F , -c
"$DETAIL_SQL" | tr "," "\t" >table.csv

Cheers,

Brent Wood

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
View this message in context: http://www.nabble.com/selective-export-for-subsequent-import-%28COPY%29-tf3604927.html#a10101989
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi Tom,

It appears to me that the documentation suggests that: -P fieldsep='\t'
should work, but I don't think it does.

Tom Lane-2 wrote:

chrisj <chrisj.wood@sympatico.ca> writes:

This helped a lot, but ideally I want a tab field delimiter and -F '\t'
does
not seem to work, any ideas??

I don't think there's any provision for backslash-notation in that
switch; you'd need to type an actual tab character there. Depending on
what shell you use, that might be a bit difficult on an interactive
shell command line, but it should be simple enough to insert one in a
script file.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--
View this message in context: http://www.nabble.com/selective-export-for-subsequent-import-%28COPY%29-tf3604927.html#a10102249
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

chrisj wrote:

Thanks Brent, very much appreciated, your first suggestion is perfect.

the translate suggestion assumes that there are no commas in the data, but
that is why I wanted to use tab.

I figured as much :-) Note that you can use -F "|" for a pipe symbol, or
use any other character as the field
separator in the psql command line, then change that to a tab with tr,
if you do have commas in the data.

It also scripts up nicely:

...
FSEP="|"
psql -d .... -F "$FSEP" .... | tr "$FSEP" "\t" > $FILE
...

Brent

Tom Lane wrote:

chrisj <chrisj.wood@sympatico.ca> writes:

This helped a lot, but ideally I want a tab field delimiter and -F '\t' does
not seem to work, any ideas??

I don't think there's any provision for backslash-notation in that
switch; you'd need to type an actual tab character there. Depending on
what shell you use, that might be a bit difficult on an interactive
shell command line, but it should be simple enough to insert one in a
script file.

I'm not sure what shell is being used, but the following works with
bash, csh, tcsh, and ksh under Linux:

In order to emit an actual tab character on the shell command line (and
ignore any shell auto-completion features that are normally tied to the
tab key), preface the literal tab character with Ctrl-V. Thus, the
delimiter specification from above would be typed "-F '<Ctrl-V><Tab>'".

Hope this helps.

Andrew

Wow, how did you discover that?

Andrew Kroeger wrote:

Tom Lane wrote:

chrisj <chrisj.wood@sympatico.ca> writes:

This helped a lot, but ideally I want a tab field delimiter and -F '\t'
does
not seem to work, any ideas??

I don't think there's any provision for backslash-notation in that
switch; you'd need to type an actual tab character there. Depending on
what shell you use, that might be a bit difficult on an interactive
shell command line, but it should be simple enough to insert one in a
script file.

I'm not sure what shell is being used, but the following works with
bash, csh, tcsh, and ksh under Linux:

In order to emit an actual tab character on the shell command line (and
ignore any shell auto-completion features that are normally tied to the
tab key), preface the literal tab character with Ctrl-V. Thus, the
delimiter specification from above would be typed "-F '<Ctrl-V><Tab>'".

Hope this helps.

Andrew

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
View this message in context: http://www.nabble.com/selective-export-for-subsequent-import-%28COPY%29-tf3604927.html#a10139682
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Monday 23 April 2007 14:56, chrisj wrote:
| Wow, how did you discover that?

man readline?

search for "quoted-insert"

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----