Postgresql check insert input

Question

I'm making a database with PostgreSQL. In one of the attributes in a tables it should be possible to insert numbers: "+" and "-", but no other chars like "A", "B" or "!".

Is it possible to check the input when I'm using the INSERT INTO function? I don't know because I'm just a beginner in Postgre and didn't find a solution in the internet.

Thanks if anybody knows an answer!

It could even be done with a check-constraint using a regexp.(or just use one of the numeric datatypes) No triggers needed. see postgresql.org/docs/9.5/static/sql-createtable.html (under: CHECK) — wildplasser
– wildplasser, Commented May 25, 2016 at 15:14

wildplasser · Accepted Answer · 2016-05-25 15:53:22Z

1

Instead of a trigger, you could use a CHECK constraint on the column's value. (or even make it a domain or type)

CREATE TABLE meuk
        ( id serial NOT NULL PRIMARY KEY
        , thefield varchar CHECK (thefield SIMILAR TO e'[0-9\+\-]+' )
        );

INSERT INTO meuk(thefield) VALUES ('1234');
INSERT INTO meuk(thefield) VALUES ('+1234');
INSERT INTO meuk(thefield) VALUES ('-1234');
INSERT INTO meuk(thefield) VALUES ('-1234a'); -- this one should fail

SELECT * FROM meuk;

edited May 25, 2016 at 15:53

answered May 25, 2016 at 15:35

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Klipo Over a year ago

Thank you very much. That helps a lot.

Collectives™ on Stack Overflow

Postgresql check insert input

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related