Обсуждение: [GENERAL] Select from tableA - if not exists then tableB

On 05/08/2017 02:56 PM, Patrick B wrote:
> Hi guys,
>
> I have two tables that supports the same data, but different table DDL
> (We are migrating all the data from one to another).
>
> What I need is basically:
>
> 1. Query looks for the data on table A,
> 2. if it doesn't find it on table A, go look for it on table B
>
> Now, how could I do that in a Select? Can you please provide some examples?

If you can provide some information:

1) The table schema

2) How the data is related between the two tables.

>
>
> I'm using PostgreSQL 9.1.
>
> Thanks
> Patrick


--
Adrian Klaver
adrian.klaver@aklaver.com

On 9 May 2017 at 09:56, Patrick B <patrickbakerbr@gmail.com> wrote:
> Hi guys,
>
> I have two tables that supports the same data, but different table DDL (We
> are migrating all the data from one to another).
>
> What I need is basically:
>
> 1. Query looks for the data on table A,
> 2. if it doesn't find it on table A, go look for it on table B
>
> Now, how could I do that in a Select? Can you please provide some examples?
>
>
> I'm using PostgreSQL 9.1.

You could exploit DISTINCT ON for this.

Something like:

select distinct on (id) id,value from (select *,'a' tablename from a
where id=1 union all select *,'b' tablename from b where id=1) ab
order by id,tablename;

Assuming that id is what you want to be unique.


--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

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)





On Mon, May 8, 2017 at 5:56 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
> Hi guys,
>
> I have two tables that supports the same data, but different table DDL (We
> are migrating all the data from one to another).
>
> What I need is basically:
>
> 1. Query looks for the data on table A,
> 2. if it doesn't find it on table A, go look for it on table B
>
> Now, how could I do that in a Select? Can you please provide some examples?
>
>
> I'm using PostgreSQL 9.1.
>
> Thanks
> Patrick

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