PostgreSQL: Why is NOT NULL not working here?

Question

I'm developing my first ever application with PostgreSQL.

The Scenario

This is what my table "person" looks like:

   Column   |            Type             |                      Modifiers
------------+-----------------------------+-----------------------------------------------------
 id         | bigint                      | not null default nextval('person_id_seq'::regclass)
 first_name | character varying(255)      | not null
 last_name  | character varying(255)      | not null
 email      | character varying(255)      | not null
 password   | character varying(255)      | not null
 created_at | timestamp without time zone |
 updated_at | timestamp without time zone |
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)
    "person_email_unique" UNIQUE CONSTRAINT, btree (email)
    "person_id_unique" UNIQUE CONSTRAINT, btree (id)
Referenced by:
    TABLE "access" CONSTRAINT "access_person_id_foreign" FOREIGN KEY (person_id) REFERENCES person(id)

This was created using migrations in knex.schema.

If I run the following query in psql...

insert into person (first_name, last_name, email, password) values ('Max', 'Mustermann', '', '123123123');

I get back INSERT 0 1 and the row is successfully inserted:

id | first_name | last_name  |               email               |    password    |       created_at        |       updated_at
----+------------+------------+-----------------------------------+----------------+-------------------------+-------------------------
12 | Max        | Mustermann |                                   | 123123123      |                         |

My Question:

I expect the operation to fail, because no e-mail (NOT NULL) was specified. Why does it not fail?

Thank you very much for your help!

Max

null and '' are not the same thing. If you excluded email from your insert completely it would throw an error — Matt Busche
– Matt Busche, Commented Apr 29, 2014 at 0:46
The empty string is not the same as NULL (except in Oracle). — Gordon Linoff
– Gordon Linoff, Commented Apr 29, 2014 at 0:46
If you want to disallow empty strings too then add check(email <> '') or check(length(email) > 0). — mu is too short
– mu is too short, Commented Apr 29, 2014 at 1:23
"lack of a value" is not the same thing as "a value of zero"; similarly, "lack of an answer" is not the same thing as "an answer of no" en.wikipedia.org/wiki/Null_(SQL) — pozs
– pozs, Commented Apr 29, 2014 at 8:39
Ah, I see, for some reason I thought '' was NULL in SQL. Thanks for your help, guys! — Macks
– Macks, Commented Apr 29, 2014 at 9:50

ntalbs · Accepted Answer · 2014-04-29 04:43:56Z

8

Some DBMS (like Oracle) treats empty string ('') as NULL. Others (like MySQL, PostgreSQL, etc) treat empty string and NULL as different.

PostgreSQL treats '' as empty string, not NULL, so your insert statement executed successfully.

edited Apr 29, 2014 at 4:43

answered Apr 29, 2014 at 0:48

ntalbs

29.6k8 gold badges72 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oscar Over a year ago

But, postgreSQL will display a NULL where in fact the value is '' if the column type is string

Matt Busche · Accepted Answer · 2014-04-29 00:49:08Z

2

null and an empty string are not the same values, by passing in an empty string you have satisfied the requirement.

If you query was

insert into person (first_name, last_name, password) 
values ('Max', 'Mustermann', '123123123');

then an error would be thrown because you are not passing in a value for email

answered Apr 29, 2014 at 0:49

Matt Busche

14.4k5 gold badges38 silver badges63 bronze badges

Comments

jake · Accepted Answer · 2014-04-29 00:45:39Z

1

Because you didn't pass a null value. You passed an empty string.

answered Apr 29, 2014 at 0:45

jake

1,0278 silver badges9 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: Why is NOT NULL not working here?

The Scenario

My Question:

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The Scenario

My Question:

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related