duplicating table

I can alter the table to add the two
new columns, but I wanted to know if
I can copy the data from the old table
into the new table and put the column
data in the appropriate places
(leaving column: address2 and mailcode empty)?

I have been trying something like:

[example]

insert into t2_ref_pcg_address
select name,riskgrp,address,,city,state,zi p,pcgname from t_ref_pcg_address;

It'd probably work if you used NULL or '' where address2 belongs (the two
commas next to each other is probably your problem there).

Shaunn-

Here's a simplified version using your tables (you'll need to add all of the
rows you care about:

insert into table_two (name, riskgrp) values (select name,riskgrp from
table_one);

-Nick

--------------------------------------------------------------------------
Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Johnson, Shaunn
Sent: Thursday, January 10, 2002 9:53 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] duplicating table

Howdy:
I know this will seem silly, but I'm trying to
make a copy of a table with a few modifications.
I have a table like so:
[snip]
Attribute | Type | Modifier
-----------+-------------------+----------
name | character varying |
riskgrp | character(12) |
address | character varying |
city | character varying |
state | character varying |
zip | character varying |
pcgname | character varying |
[/snip]
and i want to create a new table like so:
[snip]
Attribute | Type | Modifier
-----------+-------------------+----------
name | character varying |
riskgrp | character(12) |
address | character varying |
address2 | character varying |
city | character varying |
state | character varying |
zip | character varying |
pcgname | character varying |
mailcode | character(5) |
[/snip]
I can alter the table to add the two
new columns, but I wanted to know if
I can copy the data from the old table
into the new table and put the column
data in the appropriate places
(leaving column: address2 and mailcode empty)?
I have been trying something like:
[example]
insert into t2_ref_pcg_address
select name,riskgrp,address,,city,state,zi p,pcgname from t_ref_pcg_address;
[/example]
And
[example]
select
name,riskgrp,address,,city,state,zip,pcgname from t_ref_pcg_address into
t2_ref_pcg_address;
[/example]
Neither works.
Any suggestions?
Thanks!

-X

In the first example you did, you need to specify column names:

insert into (col1, col2)
SELECT (col1, col2)
FROM table1

thanks!

-----Original Message-----
From: Johnson, Shaunn [mailto:SJohnson6@bcbsm.com]
Sent: Thursday, January 10, 2002 9:53 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] duplicating table

Howdy:

I know this will seem silly, but I'm trying to
make a copy of a table with a few modifications.

I have a table like so:

[snip]

Attribute | Type | Modifier
-----------+-------------------+----------
name | character varying |
riskgrp | character(12) |
address | character varying |
city | character varying |
state | character varying |
zip | character varying |
pcgname | character varying |

[/snip]

and i want to create a new table like so:

[snip]
Attribute | Type | Modifier
-----------+-------------------+----------
name | character varying |
riskgrp | character(12) |
address | character varying |
address2 | character varying |
city | character varying |
state | character varying |
zip | character varying |
pcgname | character varying |
mailcode | character(5) |
[/snip]

I can alter the table to add the two
new columns, but I wanted to know if
I can copy the data from the old table
into the new table and put the column
data in the appropriate places
(leaving column: address2 and mailcode empty)?

I have been trying something like:

[example]

insert into t2_ref_pcg_address
select name,riskgrp,address,,city,state,zi p,pcgname from t_ref_pcg_address;

[/example]

And

[example]

select
name,riskgrp,address,,city,state,zip,pcgname from t_ref_pcg_address into
t2_ref_pcg_address;

[/example]

Neither works.

Any suggestions?

Thanks!

-X

Shaunn Johnson wrote

Howdy:
I know this will seem silly, but I'm trying to make a copy of a table
with a few modifications.

[snip]

Both methods are possible, if you're created the table first then INSERT
INTO ... SELECT will work. Personally I don't often like using positional
inserts and that might be what's causing you to say "it doesn't work" (BTW
giving _why_ it doesn't work helps :). Try explicity stating what's going to
be inserted.

INSERT INTO new_table (field1, field2, field3, etc.)
SELECT field1, field2, field3, etc.
FROM original_table
;

Alternately you can create the table using the SELECT ... INTO (or the SQL92
way CREATE TABLE .. AS SELECT). Then add the other attributes after.

CREATE TABLE new_table AS SELECT * FROM original_table;
ALTER TABLE new_table ADD field datatype [options];

This won't get the attribute ordering you want though w/o having to muck
about w/ pg_attributes.attnum (something I wouldn't suggest).

I know this will seem silly, but I'm trying to
make a copy of a table with a few modifications.

A variation on the above:

Suppose I create and populate a table called people in the test database
and I want to move it to another database called production. How can a
query across the schema? And schema may not be the correct word.

For simplicity, suppose people is created (in both test and
production) using:

create table people (fname varchar(20), lname varchar(20));

I want to do something like
insert into production.people
select fname, lname from test.people;

Actually I'd be connected to the production instance so I really want to
try:

insert into people
select fname, lname from test.people;

An archive search yielded nothing, though maybe I searched with not the
best keyword (schema).

Any points, comments welcome.

Thanks in advance,
Jim Martinez

Jim Martinez <jjm@bigbigorg.org> writes:

I know this will seem silly, but I'm trying to
make a copy of a table with a few modifications.

A variation on the above:

Suppose I create and populate a table called people in the test database
and I want to move it to another database called production. How can a
query across the schema? And schema may not be the correct word.

Schema is in fact the correct word (it's in the SQL standard).

Schemas are currently not implemented in PostgreSQL. They are on the
TODO list for a future version.

You'll have to use two different databases and use pg_dump/psql to
move the data, or write custom client code to do so.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863