Search code examples
postgresqlconstraintsvarchar

Prevent empty strings in CHARACTER VARYING field

I am using PostgreSQL and would like to prevent certain required CHARACTER VARYING (VARCHAR) fields from allowing empty string inputs.

These fields would also need to contain unique values, so I am already using a unique constraint; however, this does not prevent an original (unique) empty value.

Basic example, where username needs to be unique and not empty

| id | username | password |
+----+----------+----------+
| 1  | User1    | pw1      | #Allowed
| 2  | User2    | pw1      | #Allowed
| 3  | User2    | pw2      | #Already prevented by constraint
| 4  | ''       | pw2      | #Currently allowed, but needs to be prevented
Solution

  • Use a check constraint:

    CREATE TABLE foobar(
  x TEXT NOT NULL UNIQUE,
  CHECK (x <> '')
);

INSERT INTO foobar(x) VALUES('');