Question 46561 in Debian Question posted in
The oficial documentation can be found here.

Postgres SQL | IF ELSE | HOW TO – Debian

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!

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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 ; 
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search