Menu
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 
993
Handling empty strings in PostgreSQL In Oracle, because empty strings are treated as NULL, the preceding insert statements #2 and #3 will store NULL for column tname in the table. However, in PostgreSQL, the table will store NULL for the #2 and an empty string for the #3 insert statements.
In formal language theory, the empty string, or empty word, is the unique string of length zero.
nullif also used with the coalesce function to handle the null values. PostgreSQL nullif function returns a null value if provided expressions are equal. If two expressions provided are equal, then it provides a null value; as a result, otherwise, it will return the first expression as a result.
Use a check constraint:
CREATE TABLE foobar( x TEXT NOT NULL UNIQUE, CHECK (x <> '') ); INSERT INTO foobar(x) VALUES(''); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
