patternsqlMinor
postgresql replace table without losing dependancies
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_positionContext
StackExchange Database Administrators Q#80258, answer score: 3
Revisions (0)
No revisions yet.