6

I have a table called my_table that has a MultiPoint column. I want to limit the MultiPoint column. A Multipoint must have 3 Points most and it must have 2 points at least.

How can I do the limitation ?

-- this is my table
CREATE TABLE my_table (
  id serial Primary Key,
  geom geometry(MultiPointZ, 4326)
)

This one should work, because it has 2 points.

INSERT INTO my_table (geom)
VALUES (
    ST_SETSRID(ST_Collect(
        ST_MakePoint(0, 0, 0),
        ST_MakePoint(0, 0, 0)
    ), 4326)
)

This one should NOT work, because it has a point.

INSERT INTO my_table (geom)
VALUES (
    ST_SETSRID(ST_Collect(
        ST_MakePoint(0, 0, 0)
    ), 4326)
)

This one should NOT work, because it has 4 points.

INSERT INTO my_table (geom)
VALUES (
    ST_SETSRID(ST_Collect(
        ST_MakePoint(0, 0, 0),
        ST_MakePoint(0, 0, 0),
        ST_MakePoint(0, 0, 0),
        ST_MakePoint(0, 0, 0)
    ), 4326)
)
3
  • 2
    What is your question?
    – PolyGeo
    Dec 24 '21 at 12:32
  • I want to limit geom column. A Multipoint must have 3 Points most. A Multipoint must have 2 points at least. Dec 24 '21 at 12:48
  • Please edit your question to include a question mark that indicates the question that you are asking.
    – PolyGeo
    Dec 24 '21 at 13:00
9

Try

CREATE TABLE my_table (
  id   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  geom GEOMETRY(MULTIPOINTZ, 4326),

  CHECK (ST_NumGeometries(geom) BETWEEN 2 AND 3)
);

ST_NumGeometries behaves IMMUTABLE and should be supported. You may need to explicitly use >= and <= rather than BETWEEN.

1

I think you need handle this by first converting it into string and substring formatting try by using check constraint refer this
https://stackoverflow.com/a/35699685/17549908

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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