Re: Select from tableA - if not exists then tableB

[Patrick B <patrickbakerbr@xxxxxxxxx>]

2017-05-09 10:19 GMT+12:00 Brian Dunavant <brian@xxxxxxxxxx>:

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.