Thread: selective export for subsequent import (COPY)
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. -- View this message in context: http://www.nabble.com/selective-export-for-subsequent-import-%28COPY%29-tf3604927.html#a10071704 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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, pleaseadvise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses containedin 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 > 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. > >
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 > 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 >> >> >> > >
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/ ----