2017-05-09 10:19 GMT+12:00 Brian Dunavant <br...@omniti.com>: > From what you're saying about migrating, I'm assuming the new table > has additional columns or something. If you can map the difference, > then you could use CTE's to select from the first table, and if > nothing is there, then pull from the second table and pad it with > nulls so they "match". This should work fine in 9.1. > > For example: > > db=# create table old ( id integer ); > CREATE TABLE > db=# create table new ( id integer, newcol text ); > CREATE TABLE > db=# insert into old (id) values (1), (2); > INSERT 0 2 > db=# insert into new (id, newcol) values (1, 'a'); > INSERT 0 1 > > New table: > > db=# with new_check as ( > db(# select id, newcol from new where id = 1 > db(# ) > db-# select id, null::text as newcol from old where id = 1 > db-# and not exists ( select 1 from new_check ) > db-# union all > db-# select * from new_check; > id | newcol > ----+-------- > 1 | a > (1 row) > > Old table: > > db=# with new_check as ( > db(# select id, newcol from new where id = 2 > db(# ) > db-# select id, null::text as newcol from old where id = 2 > db-# and not exists ( select 1 from new_check ) > db-# union all > db-# select * from new_check; > id | newcol > ----+-------- > 2 | > (1 row) > > Neither: > > db=# with new_check as ( > db(# select id, newcol from new where id = 3 > db(# ) > db-# select id, null::text as newcol from old where id = 3 > db-# and not exists ( select 1 from new_check ) > db-# union all > db-# select * from new_check; > id | newcol > ----+-------- > (0 rows)
Hmm.. that's interesting... The query is (This is actually a view) SELECT split_part(n1.path::text, '/'::text, 18)::integer AS id, split_part(n1.path::text, '/'::text, 14)::integer AS clientid, lower(n1.md5::text)::character(32) AS md5, 0 AS cont, '00000000-1000-1000-3000-600000000000'::uuid AS guid, n1.bytes AS byte_count, n1.last_modified AS last_modified FROM tablea n1 JOIN tableb s2 ON s2.path = n1.path Where tablec is the new one. AS you can see, there is no reference for the new tablec on that query, so I need to: - Get the data from the new table, - if it is not in there, then go to old table (query above). \d tablec: > > Table "public.tablec" > Column | Type | > Modifiers > > --------------------+-----------------------------+------------------------------------------------------------------- > id | integer | not null default > nextval('tablec_id_seq'::regclass) > e_type | integer | not null > e_id | bigint | > e_variation | character varying(16) | not null > path | character varying(255) | not null > name | character varying(255) | not null > size | bigint | not null > md5 | md5_hash | not null > modified_date | timestamp without time zone | default > statement_timestamp() > created_date | timestamp without time zone | default > statement_timestamp() > clientid | bigint | not null > f_id | bigint | So, will the best way to use UNION ALL? Thanks Patrick.