Q: 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


A: You can use the standard SQL 'CONSTRAINT...CHECK' clause when defining table fields:
CREATE TABLE test
(
    nonempty VARCHAR NOT NULL UNIQUE CONSTRAINT non_empty CHECK(length(nonempty)>0)
)


A: As a special kind of constraint, you can put the datatype+constraint into a DOMAIN:
-- set search_path='tmp';

DROP DOMAIN birthdate CASCADE;
CREATE DOMAIN birthdate AS date DEFAULT NULL
    CHECK (value >= '1900-01-01' AND value <= now())
    ;

DROP DOMAIN username CASCADE;
CREATE DOMAIN username AS VARCHAR NOT NULL
    CHECK (length(value) > 0)
    ;

DROP TABLE employee CASCADE;
CREATE TABLE employee
    ( empno INTEGER NOT NULL PRIMARY KEY
    , dob birthdate
    , zname username
    , UNIQUE (zname)
    );
INSERT INTO employee(empno,dob,zname) 
  VALUES (1,'1980-02-02', 'John Doe' ), (2,'1980-02-02', 'Jon Doeh' );
INSERT INTO employee(empno,dob,zname)
  VALUES (3,'1980-02-02', '' ), (4,'1980-01-01', 'Joan Doh' );

This will allow you to reuse the domain again and again, without having to copy the constraint every time.

-- UPDATE 2021-03-25  (Thanks to @AlexanderPavlov)
There appears to be a serious flaw in Postgres's implementation: it is possible to insert NULLs from the results of an empty scalar subquery.
The (nonsensical) COALESCE() below "fixes" this behaviour.
This allows us to put the database into a forbidden state.

\echo literal NULL

    INSERT INTO employee(empno,dob,zname) VALUES (5,'2021-02-02', NULL );

\echo empty (scalar) set

    INSERT INTO employee(empno,dob,zname) VALUES (6,'2021-02-02', (select zname from employee where 1=0) );

\echo empty COALESCE((scalar, NULL) ) set

    INSERT INTO employee(empno,dob,zname) VALUES (7,'2021-02-02', (select COALESCE(zname,NULL) from employee where 1=0) );

\echo empty set#2

    INSERT INTO employee(empno,dob,zname) (select 8,'2021-03-03', zname from employee where 1=0 );

\echo duplicate the complete table

    INSERT INTO employee(empno,dob,zname) (select 100+empno,dob+'1mon':: interval, upper(zname) from employee );

select * from employee;


Extra Results:

literal NULL
ERROR:  domain username does not allow null values
empty (scalar) set
INSERT 0 1
empty COALESCE((scalar, NULL) ) set
ERROR:  domain username does not allow null values
empty set#2
INSERT 0 0
duplicate the complete table
ERROR:  domain username does not allow null values
 empno |    dob     |  zname   
-------+------------+----------
     1 | 1980-02-02 | John Doe
     2 | 1980-02-02 | Jon Doeh
     6 | 2021-02-02 | 
(3 rows)


A: Use a check constraint:
CREATE TABLE foobar(
  x TEXT NOT NULL UNIQUE,
  CHECK (x <> '')
);

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

