Re: Select from tableA - if not exists then tableB

2017-05-09 10:19 GMT+12:00 Brian Dunavant <brian(at)omniti(dot)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.