Prevent empty strings in CHARACTER VARYING field

postgresql constraints varchar
SimonMayer picture SimonMayer · Mar 4, 2012 · Viewed 9.5k times · Source

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

Answer

Frank Heikens picture Frank Heikens · Mar 4, 2012

Use a check constraint:

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

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

Related questions

How to add "on delete cascade" constraints?

In PostgreSQL 8 is it possible to add ON DELETE CASCADES to the both foreign keys in the following table without dropping the latter? # \d scores Table "public.scores" Column | Type | Modifiers ---------+-----------------------+----------- id | character varying(32) | gid | integer | money | …

Read More
PostgreSQL - disabling constraints

I have a table with approx 5 million rows which has a fk constraint referencing the primary key of another table (also approx 5 million rows). I need to delete about 75000 rows from both tables. I know that if I try doing …

Read More
Postgres: Add constraint if it doesn't already exist

Does Postgres have any way to say ALTER TABLE foo ADD CONSTRAINT bar ... which will just ignore the command if the constraint already exists, so that it doesn't raise an error?

Read More