Search code examples
postgresqlformatcheck-constraint

String Format Constraint On PostgreSQL Column Not Working

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.

Solution

  • 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)   
);