Postgres SQL | IF ELSE | HOW TO-CodePudding

I am using psql (PostgreSQL) 11.2 (Debian 11.2-1.pgdg90 1). I am trying to write a logic in .PSQL file that needs to import some data into a table if this table is empty, else do something else.

I am struggling to find the correct syntax to make it work. Would appreciate some help around this.

    DO $$ BEGIN
    SELECT count(*) from (SELECT 1 table_x LIMIT 1) as isTableEmpty
    IF isTableEmpty > 0
    THEN
            INSERT INTO table_x
            SELECT * FROM table_b;
    ELSE
            INSERT INTO table_y
            SELECT * FROM table_b;    
    END IF;
    END $$;

thanks!

CodePudding user response：

Read plpgsql structure. Then you would know you need a DECLARE section to declare isTableEmpty and from here Select into that you need to select into the isTableEmpty variable. So:

...
DECLARE
   isTableEmpty integer;
BEGIN
   SELECT count(*) into isTableEmpty from (SELECT 1 table_x LIMIT 1);
...

Though I'm not sure what you are trying to accomplish with?:

SELECT count(*) from (SELECT 1 table_x LIMIT 1) as isTableEmpty

As that is always going to return 1.

CodePudding user response：

You are using count just to determine that a row exists or not in the table. To do so you need to create a variable in the DO block, select into that variable, and reference that variable. This is all unnecessary; you can just use exists(...) instead of count(*) .... See demo;

do $$ 
begin
    
    if not exists (select null from table_x) then 
       insert into table_x (...) 
            values (...);
    else
       insert into table_y (...) 
            values (...);   
    end if;
end ; 
$$;