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

google pg_dump

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?

---------------------------(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

CREATE TABLE sometable AS SELECT * FROM someview;
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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEmE9VIB7bNG8LQkwRAhJTAJ9loGC2v/inI+28RLvbRGGAljS6/ACdEJDn
e/aJg1Qu6XaBNIuhiPWt+MU=
=rpRd
-----END PGP SIGNATURE-----

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 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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEmFfKIB7bNG8LQkwRAhQ2AJ9j0ZxWlQ1HLtcnwN4DS+ O9lIFW6gCfdQFO
5X5lv9OacEMbPhWBQdmEprc=
=jVvW
-----END PGP SIGNATURE-----

>
> 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