postgresql replace table without losing dependancies

Question

i want to replace an existing table with a new one, without losing foreign keys or inheritance relations. But I feel lazy to alter it to make it look like the new one. Is there a way to replace the table without doing all the hard job? Like a CREATE OR REPLACE statement in SQLITE or by tweaking the table name in pg_class table?

CREATE TABLE a (
 id serial NOT NULL,
 -- a bunch of other columns
 CONSTRAINT a_pk PRIMARY KEY(a)
);
CREATE TABLE b (
 id serial NOT NULL,
 a_id bigint refernces a(id)
);
--REPLACE a with a new table without violating b FOREIGN KEY relations(somehow)
CREATE OR REPLACE a( 
 id serial NOT NULL,
 -- a bunch of new columns
 CONSTRAINT a_pk PRIMARY KEY(a)
);

• user1822: What's wrong with using ALTER TABLE?
• Ben: It's not wrong, it's just a lot when you have written a replacement for a bunch of tables, and i know that some other rdb's have this feature @a_horse_with_no_name
• user1822: I have never seen a create or replace table in any other DBMS (Oracle, SQL Server, DB2, Firebird, HSQLDB, ....)
• Ben: @a_horse_with_no_name SQLite has it
• Craig Ringer: @Benedictus How does that work? Magic? If you create or replace a table with a blah varchar(8) column and the replacement has a blah integer column, what happens? Does it assume that since they're the same name you want to try to coerce the data?

Answer 1

1. Disable any FKs pointing to the table
2. Create table SAVE as SELECT * FROM A
3. Truncate table A
4. Begin to create a script with the following

with xfer as (select cast ('a' as text) as targ_tab, cast ('b' as text) as from_tab)
select xfer.*, tabs.table_name as targ_table_name, cols.table_name as from_table_name,
 'alter table ' || tabs.table_name || ' add (' ||
 coalesce(cols.column_name, '') || ' ' || coalesce (cols.data_type, '')
 || 
 case when data_type = 'numeric' and coalesce (cols.numeric_precision,
 cols.numeric_scale) is not null then
 '(' || coalesce ('' || cols.numeric_precision, '*') || ', ' ||
 coalesce (cols.numeric_scale, 0) || ')'
 else ''
 end
 || ')' 
 as synt
from xfer join
 information_schema.tables tabs on (xfer.targ_tab = tabs.table_name) 
 join
 information_schema.columns cols on (xfer.from_tab = cols.table_name)
where not exists (
select 1
from information_schema.columns targcols
where targcols.table_name = xfer.targ_tab
and targcols.column_name = cols.column_name
)
order by ordinal_position

5. Repopulate with INSERT/SELECT
6. Re-enable foreign keys