HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

postgresql replace table without losing dependancies

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlwithoutreplacelosingdependanciestable

Problem

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

Solution


  • Disable any FKs pointing to the table



  • Create table SAVE as SELECT * FROM A



  • Truncate table A



  • 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


  • Repopulate with INSERT/SELECT



  • Re-enable foreign keys

Code Snippets

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

Context

StackExchange Database Administrators Q#80258, answer score: 3

Revisions (0)

No revisions yet.