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

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.

You cannot write such a constraint in a table declaration.

There are some workarounds:

• Create a trigger that would check the number of photos for each user
• Create a photo_order column that would keep the order of photos, make (user_id, photo_order) UNIQUE, and add CHECK(photo_order BETWEEN 1 AND 10)

One another approach would be to add column "photo_count" to users table, update it with triggers to make it reflect reality, and add check on it to enforce maximum number of photos.

Side benefit from this is that at any given moment we know (without counting) how many photos user has.

On other hand - the approach Quassnoi suggested is also pretty cool, as it gives you ability to reorder the photos in case user would want it.