Using an if else for a value in one table to insert data into another table

Question

I want to allows a user to add data to my 'posts' table if the user entry in my 'users' table has a banned_till value of either nil or less than current time.

Something like (pseudocode);

If (select banned_till from users where userid = $1) = nil or < currentTime
   Insert in posts (col1, col2) values ('abc', 'xyz')
ELSE
   RAISE Exception "User is banned"
ENDIF

Currently i am doing this using 2 queries; first one checks if the user is banned and then the 2nd one inserts into the posts table. I'd really want to combine them into a single query, if i can.

Note: I'd really prefer not using and stored procedures or something too specific to a SQL DB. Something simple and universal is much preferred.

EDIT: I went with a modified version of Erwin's answer.

DO
$$
BEGIN
    IF (select banned_till from users where unqid = 'user01') < now() THEN
      RAISE EXCEPTION 'User is banned';
    ELSE
        insert into posts (unqid, title, link, content, author_id, author_nick, author_flair) 
            SELECT 'pid03', 'Sample post title', 'www.google.com', 'This is a sample Post Content', unqid, nickname, flair 
            from users where unqid = 'user01';
    END IF;
END
$$;

Pros: Now my ban check happens before other queries are even fired. No race conditions. Most importantly I can now have two different error messages - one for the ban check and other for unqid not matching. Cons: I redo the select query on users twice

Why do you want to do this in SQL? Seems like the program/whatever should handle this — dustytrash, Sep 18 '18 at 15:46
I am currently handling this via application code and 2 db calls. But the largest timesink in my app is the db calls and i want to cut it down to one, if i can. — Rishav Sharan, Sep 18 '18 at 15:57
That won't help. It might even make the query longer. Instead you should look at optimizing your queries, or the database altogether. Or if you're already calling the users table somewhere else (When you got the userid for example), you may be able to remove this query altogether — dustytrash, Sep 18 '18 at 16:10
A part of my problem is that the db and application are on different networks and the network calls for the queries are the major bottleneck. Also, i don't think the insert query will really become a problem. my app is heavily focused on reads and that is where i am focusing to optimize. For me, just reducing the number of db calls for writes should be good enough. — Rishav Sharan, Sep 18 '18 at 17:50

Erwin Brandstetter · Accepted Answer · 2018-09-18T21:33:09.030

Don't run a separate SELECT, that would just add cost - and introduce a gratuitous race condition under concurrent write load: A user might get banned between SELECT and INSERT or similar complications.

Faster, simpler and safe against race conditions:

INSERT INTO posts (col1, col2) 
SELECT 'abc', 'xyz'
FROM   users
WHERE  userid = $1  -- assuming userid is UNIQUE
AND   (banned_till >= currentTime) IS NOT TRUE;

If you need the exception, you can wrap it in a function or SQL DO statement:

DO
$$
BEGIN
   INSERT INTO posts (col1, col2)
   SELECT 'abc', 'xyz'
   FROM   users
   WHERE  userid = $1
   AND   (banned_till >= currentTime) IS NOT TRUE;

   IF NOT FOUND THEN
      RAISE EXCEPTION 'User is banned';
   END IF;
END
$$;

About IF NOT FOUND:

PostgreSQL IF statement

The race condition may be irrelevant (like in your case probably) or devastating, depending on your exact requirements. Related:

This is working for me. thanks. Is there a way to raise the exception so that it doesn't checks on the userid= xxx bit but only the banned_til > yyy bit? In case, The userid doesn't exists, I have a different flow in my app. I only want to throw the exception based on the banned_till value. Otherwise the normal exceptions should take place. — Rishav Sharan, Sep 18 '18 at 19:52
@a_horse_with_no_name: Indeed, thanks. Forgot to remove the parentheses. — Erwin Brandstetter, Sep 18 '18 at 21:34

score 1 · Answer 2 · answered Sep 18 '18 at 17:04

In Postgres, you can express this as a single query:

with s as (
      select banned_till
      from users where userid = $1
     ),
     i as (
      insert into posts (col1, col2)
          select v.col1, v.col2
          from (values ('abc', 'xyz')) v(col1, col2)
          where (select coalesce(max(banned_till), current_date) from s) < now()
    )
select max( coalesce(max(banned_till), current_date) ) < current_time as is_success
from s;

Using an if else for a value in one table to insert data into another table

2 Answers2