Prevent empty strings in CHARACTER VARYING field

Question

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 1

Use a check constraint:

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