Postgresql – Insert a ‘NULL’ string in a table in PostgreSQL

nullpostgresql

I would like to know if it is possible to insert a 'null' string in a column which has a NOT NULL constraint.

I'm using PostgreSQL, although this probably applies to other database management systems as well.

The query would look like the below

INSERT INTO originators(originator, id, regexp)
VALUES ('null', 1, -1);

This is the table design:

CREATE TABLE originators
(
      originator    varchar(30)    NOT NULL
    , id            int            NOT NULL
    , regexp        int            NOT NULL
);

Here the originator column has a NOT NULL constraint, so there should be no NULL values in the table.

However, when I query the table, I'm seeing this:

╔════════════╦════╦════════╗
║ originator ║ id ║ regexp ║
╠════════════╬════╬════════╣
║ NULL       ║  1 ║     -1 ║
╚════════════╩════╩════════╝

How is this possible?

Best Answer

'NULL' is not the same as NULL, the first one is an string just like any other string, so your constraint (NOT NULL) won't work.

I think you are working with a programming language (like PHP or Java) that you put your value inside a string with a variable like 

INSERT INTO originators(originator, id, regexp) 
VALUES ('$variable', $id, $regexp);

so in this case you have to correct your code to prevent the quotes, or if you can't do that you constraint should be CHECK(upper(originator) != 'NULL').

Related Question