Check in conditional query if table table is empty

Question

I have the following query in which the forelast parameter should be false if the corresponding table is empty, and true otherwise:

SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1), ' ||
       CASE    WHEN (SELECT COUNT(*) FROM quote_ident(tablename))=0 THEN FALSE
               ELSE TRUE
           END ||
       ') FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
  AND S.oid = D.objid
  AND D.refobjid = T.oid
  AND D.refobjid = C.attrelid
  AND D.refobjsubid = C.attnum
  AND T.relname = PGT.tablename
ORDER BY S.relname;

But instead it always evaluates to true. Indeed the result of the subquery SELECT COUNT(*) FROM quote_ident(tablename) is always 1:

       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1), ' ||
       (SELECT COUNT(*) FROM quote_ident(tablename))||
       ') FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
  AND S.oid = D.objid
  AND D.refobjid = T.oid
  AND D.refobjid = C.attrelid
  AND D.refobjsubid = C.attnum
  AND T.relname = PGT.tablename
ORDER BY S.relname;

But I have no idea why.

For a minimal working example try:

create table empty(id serial, name varchar);
create table notempty(id serial, name varchar);
insert into notempty(name) values('foobar');

Now when you execute the query from above it evaluates for both tables true respectively 1:

SELECT SETVAL('public.empty_id_seq', COALESCE(MAX(id), -->1<--), 1) FROM public.empty;
SELECT SETVAL('public.notempty_id_seq', COALESCE(MAX(id), 1), 1) FROM public.notempty;

Tip of today: Switch to modern, explicit JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh
– jarlh, Commented Apr 5, 2019 at 12:59
SELECT COUNT(*) FROM quote_ident(tablename) will always return 1 because it's not part of your dynamic sql statement. You are selecting the count of literally calling that function, rather than selecting a count from a table, and that function returns one row. — 404
– 404, Commented Apr 5, 2019 at 13:14
@404 ok thanks that explains the current behaviour. But then how can I make it part of the dynamic sql statement? — Deadman44
– Deadman44, Commented Apr 5, 2019 at 13:17
Do you want the actual count statement to be part of the generated sql, or just the true/false value? — 404
– 404, Commented Apr 5, 2019 at 13:19

sticky bit · Accepted Answer · 2019-04-05 13:28:47Z

The easiest way is to include the CASE expression in the generated queries:

SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1), ' ||
       'CASE    WHEN (SELECT COUNT(*) FROM ' || quote_ident(PGT.schemaname) || '.' || quote_ident(tablename) || ')=0 THEN FALSE
               ELSE TRUE
           END' ||
       ') FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
  AND S.oid = D.objid
  AND D.refobjid = T.oid
  AND D.refobjid = C.attrelid
  AND D.refobjsubid = C.attnum
  AND T.relname = PGT.tablename
ORDER BY S.relname;

If you insist on only having the literals true/false there you'd need another layer of dynamic SQL. But since the contents of a table might have changed in between the time you generated the queries and execute them, having the expression in the queries, i.e. getting the result at query run time, is the safer bet anyway.

And I think you can actually simplify this and directly have an expression if count(*) > 0 in your function call. There's no need to subquery the same table again and no need for a CASE expression that simply wraps a Boolean expression as Postgres can use Boolean expressions directly.

SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1), COUNT(*) > 0) FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
  AND S.oid = D.objid
  AND D.refobjid = T.oid
  AND D.refobjid = C.attrelid
  AND D.refobjsubid = C.attnum
  AND T.relname = PGT.tablename
ORDER BY S.relname;

thanks @sticky-bit, it actually solves my problem and my false intention with the original query :-).

Collectives™ on Stack Overflow

Check in conditional query if table table is empty

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related