CODEWITHSUNDEEP
sqldatabasepostgresql
Floris
Floris

Reputation: 677

CHECK constraint to check substring matches other column (PostgreSQL)

I want to create a CHECK constraint to check that the first and the second elements of a barcode field match the data supplied in the project_code and center_code fields, eg.

+----------------+--------------+-------------+
|    barcode     | project_code | center_code |
+----------------+--------------+-------------+
| PP22-NY-1BCDE6 | PP22         | NY          |
+----------------+--------------+-------------+

I have tried using

barcode ~ ^[A-Z0-9]{4}-[A-Z0-9]{2}-[A-Z0-9]{6}$

Which works to some extent, however barcodes that do not precisely match the project_code and center_code would still match. How can I incorporate other field values in the CHECK constraint in PostgreSQL to do this?

Upvotes: 0

Views: 1127

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You would do:

constraint chk_t_barcode check (barcode like project_code || '-' || center_code || '-%')

If you wanted to be sure that the last six characters are alphanumeric:

constraint chk_t_barcode check (barcode ~ project_code || '-' || center_code || '-[a-zA-Z0-9]{6}')

Upvotes: 2

Related Questions