CODEWITHSUNDEEP
sqlpostgresqlif-statementsql-insert
Rishav Sharan
Rishav Sharan

Reputation: 2932

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

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

Upvotes: 0

Views: 1750

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657912

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:

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270421

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;

Upvotes: 1

Related Questions