How to write a constraint concerning a max number of rows in postgresql?

by

Quassnoi is right; a trigger would be the best way to achieve this.

Here’s the code:

CREATE OR REPLACE FUNCTION enforce_photo_count() RETURNS trigger AS $$
DECLARE
    max_photo_count INTEGER := 10;
    photo_count INTEGER := 0;
    must_check BOOLEAN := false;
BEGIN
    IF TG_OP = 'INSERT' THEN
        must_check := true;
    END IF;

    IF TG_OP = 'UPDATE' THEN
        IF (NEW.owner != OLD.owner) THEN
            must_check := true;
        END IF;
    END IF;

    IF must_check THEN
        -- prevent concurrent inserts from multiple transactions
        LOCK TABLE photos IN EXCLUSIVE MODE;

        SELECT INTO photo_count COUNT(*) 
        FROM photos 
        WHERE owner = NEW.owner;

        IF photo_count >= max_photo_count THEN
            RAISE EXCEPTION 'Cannot insert more than % photos for each user.', max_photo_count;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER enforce_photo_count 
    BEFORE INSERT OR UPDATE ON photos
    FOR EACH ROW EXECUTE PROCEDURE enforce_photo_count();

I included table locking in order to avoid situations where two concurrent tansactions would count photos for a user, see that the current count is 1 below the limit, and then both insert, which would cause you to go 1 over the limit. If that’s not a concern for you it would be best to remove the locking as it can become a bottleneck with many inserts/updates.

More Related Contents:

  1. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  2. Perform this hours of operation query in PostgreSQL
  3. NOT NULL constraint over a set of columns
  4. Continuing a transaction after primary key violation error
  5. Postgres constraint for unique datetime range
  6. Select first row in each GROUP BY group?
  7. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  8. How to use RETURNING with ON CONFLICT in PostgreSQL?
  9. PostgreSQL DISTINCT ON with different ORDER BY
  10. Split comma separated column data into additional columns
  11. How to delete duplicate entries?
  12. Escaping keyword-like column names in Postgres
  13. Transpose latest rows per user to columns
  14. Delete duplicate rows (don’t delete all duplicate)
  15. GROUP BY + CASE statement
  16. subquery in FROM must have an alias
  17. Compute percents from SUM() in the same SELECT sql query
  18. SQL Between clause with strings columns
  19. Insert an image in postgresql database
  20. Getting date list in a range in PostgreSQL
  21. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  22. SQL Server 2008- Get table constraints
  23. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  24. Postgresql Column Not Found, But Shows in Describe
  25. INSERT COMMAND :: ERROR: column “value” does not exist
  26. Return multiple columns and rows from a function PostgreSQL instead of record
  27. Postgres error updating column data
  28. SQL LIKE condition to check for integer?
  29. Counting the number of occurrences of a substring within a string in PostgreSQL
  30. Is there a postgres CLOSEST operator?

Leave a Comment