Select from other table if exist

queryunionvertica

hi I want to union 2 tables but I am not sure if the table is exits
this is my query:

select  a from table 
union  
select  case when exists(select a from table1) then (select a from  table1)
else (select a from table2) end;

got this

ERROR: ERROR 4840: Subquery used as an expression returned more than one row

I need to use limit 1 in a subquery but it's not the result that I wanted.

do you have other suggestions?
thanks

table, for example, I just created them in my local env for testing:

table 1

CREATE TABLE public.table1 
(
    a int,
    b varchar(80),
    c int
);


CREATE TABLE public.table
    a int,
    b varchar(80),
    c int
);

Best Answer

In principle this won't work, as you can't check if table exists and then join it in a query.

The error is unrelated. You are selecting a value in a subselect and it returns multiple rows, as the table exists.

What you can do is:

  • Create temp table
  • Check if table1 exists using V_Catalog.table and if specific column existence is important, v_catalog.column
  • If it exists, select data from table1 into temp table
  • Use the temp table in the union instead of table1
Related Question