CODEWITHSUNDEEP
sqlpostgresqlnull
nwaooi
nwaooi

Reputation: 43

Postgres value '' single quote vs NULL

May I know what is different between '' and NULL in Postgres?

My column type is set to character varying (255)

In my table the column field is set to '' and NULL.

The record is as below: 

|Name |Mobile|
|James| ''   |
|John |      |

But when I select with query:

  1. Select Name from user where Mobile ='';
  2. Select Name from user where Mobile is null;

both are returning me different result.

Appreciate if anyone able to help and assist on this matter.

Thanks

Upvotes: 0

Views: 2887

Answers (2)

Houari
Houari

Reputation: 5641

You can't even do:

Select Name from user where Mobile = null

Because NULL is not "equal to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.) This behavior conforms to the SQL standard.

Source

Upvotes: 0

Bruno Calza
Bruno Calza

Reputation: 2780

  • NULL is used to represent missing values
  • '' is a value that corresponds to a empty string

I recommend adding \pset null '(null)' on your .psqlrc. Then,

SELECT Name, Mobile from user

will return

|Name |Mobile|
|James|      |
|John |(null)|

It makes easier to distinguish empty strings and missing values when using postgres on command line.

Upvotes: 3

Related Questions