Is it possible to add a custom constraint in PostgreSQL

postgresql

Now I have a table store some rss subscribe url, sometimes the url end with '/', now I replace all url without end with '/'. to make sure the sub url did not end with '/'. I want to add a custom constraint in the rss sub source table. If the sub url end with '/', throw error to the app. I have read the PostgreSQL 13 docs, and found it have many constraint like uniq constraint check constraint with the int data type. but how to add an constraint with a string data type and make sure the string did not end with '/'? is there any way to do this action?

Best Answer

ALTER TABLE t ADD CONSTRAINT c
    CHECK ( url NOT LIKE '%/');

As suggested by Vérace in comment, an alternative would be a generated column as:

ALTER TABLE T ADD COLUMN proper_url TEXT 
    GENERATED ALWAYS AS ( regexp_replace(url, '/$', '')) STORED;

Related Solutions

PostgreSQL: how to query over a column with a custom range

The range type would allow best performance and simple queries.

If you have some string values like k and Pre-K mixed with numeric values, just create a grade table with a rank value (an integer value representing actual sort order):

CREATE TABLE grade(
   grade text PRIMARY KEY
  ,grade_int UNIQUE int NOT NULL
);

Your numeric grades can conveniently be assigned the same grade_value, other grades get integer values according to their rank.

You might write a simple function to convert ranges with your original string values to their integer representations on the fly ...
And vice-versa to display your grades_served column where needed.

Convert grades_served to an int4range type.
Using the overlap operator &&, your query for a range of grades can then simply be:

SELECT * FROM tbl WHERE grades_served && '[1,4]'::int4range;

Or, for a single grade, the "contains element" operator @>

SELECT * FROM tbl WHERE grades_served @> -1;

Index

If your table is not very small I suggest you create a GiST index to support that (and so does the manual).

CREATE INDEX tbl_grades_served_idx ON tbl USING gist (grades_served);

Postgresql – Constraint to prevent overlapping ranges in an array field in PostgreSQL

I had a similar problem with an array of int4ranges, and solved it (so far to my satisfaction) by writing this for WiTopia who has kindly agreed to let me share. Please satisfy yourself it works before using it in production -- I am still doing that!

CREATE OR REPLACE  FUNCTION overlapping(therange int4range[])
RETURNS BOOLEAN as
$$
BEGIN

        RETURN (
                with  
                      t1 as (select unnest(therange) as lefthand),
                      t2 as (select unnest(therange) as righthand)
                select (select array_length(therange, 1)) !=
                       (select count(*) from t1, t2 where t1.lefthand && t2.righthand)

       );

END;
$$ LANGUAGE plpgsql

I do a && (overlap) based join with the array and itself. Each item will join with itself, and should join with any other item it overlaps as well, so the count of the result of the &&-join would be greater than the count of the original array if there are overlapping items. If there are only non-overlapping items, then each item will join with itself only, and the result of the join will have one row for each element in the original array.

I'd be careful about using this once the arrays got big -- it's N^2 in concept!

After defining this function, I put this constraint in:

check (portsegments is null or not overlapping(portsegments))