Re: Select from tableA - if not exists then tableB

On Tue, May 9, 2017 at 6:00 PM, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:
> 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).

I'm assuming tablec is supposed to replace tablea.

Being a view makes it trickier. You can still do it with:

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 (
select DISTINCT ON (id) [columns] from (
select [columns/pads], 1 as tableorder from tablec
union all
select [columns/pads], 2 as tableorder from tablea
) t
ORDER BY id, tableorder
) n1
JOIN tableb s2 ON s2.path = n1.path;

This will cause it to prefer the data in tablec, but use any id's in
tablea that aren't in tablec .

This may be very slow, as i'm not sure if predicate pushdown would
happen here, so this may cause full table scans of both tablea and
tablec possibly making performance bad if those are large tables. It
should do what you are asking for though.