4

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)
);
7
  • What's wrong with using ALTER TABLE?
    – user1822
    Commented Oct 15, 2014 at 14:47
  • 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
    – Ben
    Commented Oct 15, 2014 at 14:49
  • I have never seen a create or replace table in any other DBMS (Oracle, SQL Server, DB2, Firebird, HSQLDB, ....)
    – user1822
    Commented Oct 15, 2014 at 14:55
  • @a_horse_with_no_name SQLite has it
    – Ben
    Commented Oct 15, 2014 at 14:56
  • @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? Commented Oct 15, 2014 at 15:15

1 Answer 1

3
  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
  1. Repopulate with INSERT/SELECT
  2. Re-enable foreign keys

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.