3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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