-1

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).

closed as off-topic by DPSSpatial, BBG_GIS, aldo_tapia, user2856, MaryBeth Nov 13 '17 at 19:40

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general IT or with no clear GIS component, are off-topic here but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software), Database Administrators (relational databases) and other SE sites" – DPSSpatial, BBG_GIS, aldo_tapia, user2856, MaryBeth
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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
4

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()
RETURNS TRIGGER AS
$body$
BEGIN
    -- replace 100 by the number of rows you want
    IF (SELECT count(*) FROM your_table) > 100
    THEN 
        RAISE EXCEPTION 'INSERT statement exceeding maximum number of rows for this table' 
    END IF;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER tr_check_number_of_row 
BEFORE INSERT ON your_table
FOR EACH ROW EXECUTE PROCEDURE check_number_of_row();
  • 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
-3

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.

  • 4
    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. – Gob Tron Nov 13 '17 at 19:07

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