Обсуждение: 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? Thanks!
google pg_dump
--
+--------------------------------------------
| Aaron Koning
| Information Technologist
| Prince George, BC, Canada.
+--------------------------------------------
| http://datashare.gis.unbc.ca/fist/
| http://datashare.gis.unbc.ca/gctp-js/
+--------------------------------------------
On 6/20/06, Worky Workerson <worky.workerson@gmail.com> wrote:
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?
Thanks!
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
--
+--------------------------------------------
| Aaron Koning
| Information Technologist
| Prince George, BC, Canada.
+--------------------------------------------
| http://datashare.gis.unbc.ca/fist/
| http://datashare.gis.unbc.ca/gctp-js/
+--------------------------------------------
On 6/20/06, Aaron Koning <aaronkoning@gmail.com> 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?
CREATE TABLE sometable AS SELECT * FROM someview;
pg_dump -t sometable dbname
DROP TABLE sometable
Que? Si!
--
+--------------------------------------------
| Aaron Koning
| Information Technologist
| Prince George, BC, Canada.
+--------------------------------------------
| http://datashare.gis.unbc.ca/fist/
| http://datashare.gis.unbc.ca/gctp-js/
+--------------------------------------------
pg_dump -t sometable dbname
DROP TABLE sometable
Que? Si!
On 6/20/06, Worky Workerson <worky.workerson@gmail.com> wrote:
On 6/20/06, Aaron Koning <aaronkoning@gmail.com> 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 majordomo@postgresql.org 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/
+--------------------------------------------
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.
Вложения
copyable, importable... into Excel or another postgres db?
--
+--------------------------------------------
| Aaron Koning
| Information Technologist
| Prince George, BC, Canada.
+--------------------------------------------
| http://datashare.gis.unbc.ca/fist/
| http://datashare.gis.unbc.ca/gctp-js/
+--------------------------------------------
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 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.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
iD8DBQFEmE9VIB7bNG8LQkwRAhJTAJ9loGC2v/inI+28RLvbRGGAljS6/ACdEJDn
e/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/
+--------------------------------------------
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.
Вложения
> > 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