I would like to set a maximum number of rows on a PostgreSQL table. I want users to be able to delete and recreate rows if need be; but there should never be more than x rows in my table. Is there a way to add such a constraint to a table - or a workaround to achieve the same goal?

The purpose of this is to prevent people from adding too many new polygons in a table. I'm looking for either a general SQL solution or a PostGIS-related solution (based on geometry for example).

  • There does not seem to be a GIS component to your question. – PolyGeo Nov 13 '17 at 20:26
  • You're right. I didn't give enough details in my question and thus the GIS part was unclear. I edited it. – Mefimefi Nov 16 '17 at 8:14
  • I was not one of those that voted to close your question, and my expertise is not in this space, so I will not counter their votes. – PolyGeo Nov 16 '17 at 8:40

You can create a trigger that checks the number of records in your destination table when an INSERT is used.

The trigger would look like this:

CREATE OR REPLACE FUNCTION check_number_of_row()
    -- replace 100 by the number of rows you want
    IF (SELECT count(*) FROM your_table) > 100
        RAISE EXCEPTION 'INSERT statement exceeding maximum number of rows for this table' 
    END IF;
LANGUAGE plpgsql;

CREATE TRIGGER tr_check_number_of_row 
FOR EACH ROW EXECUTE PROCEDURE check_number_of_row();
| improve this answer | |
  • I already have several triggers on my table, so I was wondering whether a shorter, built-in Postgre/PgAdmin solution existed. But maybe there isn't any. I'll wait a few days and accept your answer if nothing else comes up. Thanks anyway! – Mefimefi Nov 16 '17 at 8:20
  • 1
    This will not work for several reasons. Trigger should return (in case of no errors): RETURN NEW Also this will not prevent to have different transactions each of those adding 100 lines. After the commits you will get N*100 lines where N are the parallel open transactions. – Gaetano Mendola Sep 10 '19 at 19:48

In your PostgreSQL query you can add a limit value:

SELECT field1, field2, field3 FROM table1 LIMIT 100

This should give you the first 100 rows of the table.

| improve this answer | |
  • 5
    You are getting downvotes because the OP wants to limit how much rows a table contains, not how much rows a SELECT on this returns. – TurboGraphxBeige Nov 13 '17 at 19:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.