CODEWITHSUNDEEP
postgresql
ZedZip
ZedZip

Reputation: 6470

How to query select if table exists?

I have PostgreSQL and trying to do something like this to avoid error:

if table exists select value from table else select 'NOTABLE'. For example, in SQL Server I do it:

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'params'))
                            select cast(par_val as nvarchar(4)) from params where par_name='DBALIAS';

I do in PGSQL this way:

select case when EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'params')
then (select par_val from params where par_name='DBALIAS') else (select 'NOTABLE') end as DBAlias;

It works only in case if table exists else it gives me an error: relation does not exist.

Is it possible to do it in PGSQL in a query?

Upvotes: 0

Views: 1770

Answers (1)

Ramin Faracov
Ramin Faracov

Reputation: 3303

On SQL Server you can write function or procedure which returns any data or any table. Inside procedure you can write select any tables and when we execute this procedure we will be view result this selection.

But on PostgreSQL you cannot write any table selection without return query clause. And return query requires us structure of return data. So, this syntax: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'params' works on PostgreSQL too.

I write for you samples how you can do it:

Sample data:

CREATE TABLE public.data_table (
    person_name text NULL,
    city text NULL
);

INSERT INTO data_table (person_name, city) VALUES('Jon', 'California');
INSERT INTO data_table (person_name, city) VALUES('Tom', 'Los Angeles');
INSERT INTO data_table (person_name, city) VALUES('Sarah', 'New York');
INSERT INTO data_table (person_name, city) VALUES('Villiam', 'Texas');
INSERT INTO data_table (person_name, city) VALUES('Suresh', 'Chicago');
INSERT INTO data_table (person_name, city) VALUES('Michael', 'Houston');
INSERT INTO data_table (person_name, city) VALUES('Smith', 'New York');

Create function:

CREATE OR REPLACE FUNCTION select_table()
RETURNS TABLE(person text)
LANGUAGE plpgsql
AS $function$
begin

    if (exists(select * from information_schema."tables" where table_name = 'data_table')) then 
        return query 
        select person_name from data_table;
    else 
        return query 
        select 'no table';
    end if; 
    
end;
$function$
;

Executing our function:

select * from select_table();

Result: 

|person |
+-------+
|Jon    |
|Tom    |
|Sarah  |
|Villiam|
|Suresh |
|Michael|
|Smith  |

Let's drop the table and again execute our function:

drop table data_table; 

select * from select_table();

Result: 

|person  |
+--------+
|no table|

Upvotes: 2

Related Questions