pg_dump or something.... copying data...

[Anon]

i want to take the data out of 1 table, and put it into a table in a different database, with different column names... so i'm wondering if i could do a dump type thing to the table, or even 1 column at a time, edit the text file if necessary, and insert it into the new table?

[smarlowe]

OK, here's a short list of stupid postgresql tricks that can come in handy.

Problem 1: I want to grab three columns from this field, maybe concatenate two, and put them into another (table,database,computer,etc.)

Say we have a table with 4 fields, field1 through field4. field1 has the address, field2 has the last name, field3 has the first name, and field4 has

select field1 as address, field2||', '||field3 as full_name, field4 as number into newtable from oldtable;

will create table newtable with the three fields address, full_name and number. The last and first names will be concatenated like so:

Last, First

Now, to dump that out and put it into another database, you can do this from the command line:

pg_dump dbname -t newtable >newtable.sql

and put it into another postgresql database like this:

psql otherdbname -e <newtable.sql

Let us know if that helps...

[Anon]

this totally solved my problem, thanks a bunch. this is the 2nd time you guys have saved me probably hours of work. thanks again