Re: [GENERAL] Select from tableA - if not exists then tableB - Mailing list pgsql-general

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)