Re: duplicating table

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