They are also different from a design perspective:

e.g. CREATE TABLE t ( id INTEGER NOT NULL, name CHARACTER(40), CONSTRAINT t_PK PRIMARY KEY (id) );

CREATE UNIQUE INDEX t_AK1 ON t (name);

Looks like:

\d t Table "public.t" Column | Type | Modifiers --------+---------------+----------- id | integer | not null name | character(40) | Indexes: "t_pk" PRIMARY KEY, btree (id) "t_ak1" UNIQUE, btree (name)

Lets insert some data:

op=# insert into t(id, name ) values ( 1, 'Hello'); INSERT 0 1

op=# insert into t( id, name) values ( 2, ''); INSERT 0 1

op=# insert into t( id, name) values ( 3, '');

ERROR: duplicate key value violates unique constraint "t_ak1"

Now lets try with null:

op=# insert into t( id, name) values (4, null );

INSERT 0 1

op=# insert into t( id, name) values (5, null);

INSERT 0 1

This is allowed.

Soooooo: nulls are not trivial strings nor the reverse.

Cheers

They are also different from a design perspective:

e.g.

CREATE TABLE t (
    id INTEGER  NOT NULL,
    name CHARACTER(40),
    CONSTRAINT t_PK PRIMARY KEY (id)
);

CREATE UNIQUE INDEX t_AK1 ON t (name);

Looks like:

 \d t
          Table "public.t"
 Column |     Type      | Modifiers
--------+---------------+-----------
 id     | integer       | not null
 name   | character(40) |
Indexes:
    "t_pk" PRIMARY KEY, btree (id)
    "t_ak1" UNIQUE, btree (name)

Lets insert some data:

op=# insert into t(id, name ) values ( 1, 'Hello');
INSERT 0 1

op=# insert into t( id, name) values ( 2, '');
INSERT 0 1

op=# insert into t( id, name) values ( 3, '');

ERROR:  duplicate key value violates unique constraint "t_ak1"

Now lets try with null:

op=# insert into t( id, name) values (4, null );

INSERT 0 1

op=# insert into t( id, name) values (5, null);

INSERT 0 1

This is allowed.

Soooooo: nulls are not trivial strings nor the reverse.

Cheers