String Format Constraint On PostgreSQL Column Not Working

Question

I am moving a database from SQL Server to PostgreSQL and hitting some issues with a check constraint in one of the tables. PostgreSQL version is 9.5.1.

I have a table that I created with a check constraint on one of the columns to enforce format. This worked in SQL Server. The idea is that only values beginning with the letters AF and followed by three numeric characters (e.g. AF001) can be entered in one of the columns.

The SQL looked like this to create the table:

CREATE TABLE TableName (
referenceID     VARCHAR(5) NOT NULL CHECK (referenceID LIKE 'AF[0-9][0-9][0-9]'),
comment         VARCHAR(50) NOT NULL,
PRIMARY KEY (referenceID)   
);

But when I try to enter any data it fails. Example of data entry:

INSERT INTO TableName (reference, comment) VALUES ('AF000','A comment');

The error I get is:

ERROR:  new row for relation "tablename" violates check constraint "tablename_referenceID_check"
DETAIL:  Failing row contains (AF000, A comment).

********** Error **********

ERROR: new row for relation "TableName" violates check constraint "tablename_referenceID_check"
SQL state: 23514
Detail: Failing row contains (AF000, A comment).

I'm assuming the issue is with the actual check constraint, but I'm unsure.

Relevant: Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL — toraritte
– toraritte, Commented Sep 5, 2019 at 20:26

Patrick · Accepted Answer · 2016-02-24 16:53:41Z

6

The LIKE clause does not use regular expression patterns in PostgreSQL. Instead of LIKE you should use the SIMILAR TO clause:

CREATE TABLE TableName (
referenceID     VARCHAR(5) NOT NULL CHECK (referenceID SIMILAR TO 'AF[0-9]{3}'),
comment         VARCHAR(50) NOT NULL,
PRIMARY KEY (referenceID)   
);

answered Feb 24, 2016 at 16:53

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

String Format Constraint On PostgreSQL Column Not Working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related