Re: [GENERAL] Exporting data from view
I read recently about the efforts underway to COPY from a view, however I was wondering what the current best-practices are for being able to copy out of a view and import that data into an actual table elsewhere. I am currently doing psql -c SELECT ... and the using a bit of perl to transform that into something copyable (i.e. CSV), but is there a way to directly export the data in an easily importable form? psql -A -t -c select * from view; You can use -F to set the delimiter -o to specify an output file name (or just redirect stdout) etc. Try man psql To redirect it into a table, insert into table select ; between databases/systems psql -d DB -p port -A -t -c select * from view; | psql ... copy from stdin...; can work, as the psql extract can be written to generate the same format as copy from. Brent Wood ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Exporting data from view
On 6/20/06, Aaron Koning [EMAIL PROTECTED] wrote: google pg_dump Really? What command do you use? I've tried the following: pg_dump -t viewname dbname and I get the view definition, whereas I would like the data. Is there an option to pg_dump that I'm missing? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Exporting data from view
CREATE TABLE sometable AS SELECT * FROM someview; pg_dump -t sometable dbname DROP TABLE sometable Que? Si!On 6/20/06, Worky Workerson [EMAIL PROTECTED] wrote: On 6/20/06, Aaron Koning [EMAIL PROTECTED] wrote: google pg_dumpReally?What command do you use?I've tried the following:pg_dump -t viewname dbname and I get the view definition, whereas I would like the data.Isthere an option to pg_dump that I'm missing?---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- +|Aaron Koning|Information Technologist|Prince George, BC, Canada.+| http://datashare.gis.unbc.ca/fist/|http://datashare.gis.unbc.ca/gctp-js/+
Re: [GENERAL] Exporting data from view
On Tue, Jun 20, 2006 at 10:29:21AM -0700, Aaron Koning wrote: CREATE TABLE sometable AS SELECT * FROM someview; pg_dump -t sometable dbname DROP TABLE sometable Que? Si! Eh? If you're going to create the table anyway, I'd use psql: psql -c COPY table TO STDOUT If you put CSV there you can get the output in CSV. Far cleaner than pg_dump. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Exporting data from view
copyable, importable... into Excel or another postgres db?On 6/20/06, Martijn van Oosterhout kleptog@svana.org wrote:On Tue, Jun 20, 2006 at 10:29:21AM -0700, Aaron Koning wrote: CREATE TABLE sometable AS SELECT * FROM someview; pg_dump -t sometable dbname DROP TABLE sometable Que? Si!Eh? If you're going to create the table anyway, I'd use psql:psql -c COPY table TO STDOUTIf you put CSV there you can get the output in CSV. Far cleaner than pg_dump.--Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. -BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFEmE9VIB7bNG8LQkwRAhJTAJ9loGC2v/inI+28RLvbRGGAljS6/ACdEJDne/aJg1Qu6XaBNIuhiPWt+MU==rpRd-END PGP SIGNATURE- -- +|Aaron Koning|Information Technologist|Prince George, BC, Canada.+ |http://datashare.gis.unbc.ca/fist/|http://datashare.gis.unbc.ca/gctp-js/+
Re: [GENERAL] Exporting data from view
On Tue, Jun 20, 2006 at 12:44:24PM -0700, Aaron Koning wrote: copyable, importable... into Excel or another postgres db? Ofcourse. It doesn't have the table definition ofcourse, just the data. The CSV format was added specifically for importing into Excel and similar programs... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
