SQL Not Empty instead of Not NULL

Question

I am using postgreSQL. I have a column that:

NOT NULL

However when I want to insert a row with an empty string as like:

''

it doesn't give me an error and accepts. How can I check insert value should be not empty? (Neither empty nor null)

PS: My column defined as:

"ads" character varying(60) NOT NULL

Peter

Answer 1

Add a constraint to column definition. For example something like:

ads character varying(60) NOT NULL CHECK (ads <> '')

For more, see http://www.postgresql.org/docs/current/static/ddl-constraints.html

Elisha

Why would you reference the old version 8.3? See here for more info: meta.stackexchange.com/questions/108714/… – Gnathic

You can also add a domain if you use this more often: postgresql.org/docs/current/static/sql-createdomain.html – Buchner

Answer 2

Found in the current documentation of postgreSQL you can do the following to achieve what you want:

CREATE TABLE distributors (
    did    integer PRIMARY KEY DEFAULT nextval('serial'),
    name   varchar(40) NOT NULL CHECK (name <> '')
);

From the documentation:

CHECK ( expression )

The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN succeed. Should any row of an insert or update operation produce a FALSE result an error exception is raised and the insert or update does not alter the database. A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint may reference multiple columns.

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.

Symphonious

PostgreSQL 8.2? Version 8.2 was released in 2006 (!), it reaches end of life this year. Please reference /current if you don't need to address an old version. More info here: meta.stackexchange.com/questions/108714/… – Gnathic