1

below is the SQL query ran in Postgres and its corresponding output.

enter image description here

The data type for the column 'premiseId' is text which is the equivalent of varchar (without any length specifier). So my query is how come the results contain null values in the specified column?

The Postgres version used is 10.11.

Eugene
  • 1,013
  • 1
  • 22
  • 43

4 Answers4

0

Single quotes in SQL denote string literals. 'premiseId' is not the column's name, it's a string literal. Since this literal is not null, all the rows in the table are returned. If you want to use the column in the query, you should refer to it without the quotes:

SELECT * FROM request WHERE premiseId IS NOT NULL
-- No quotes ---------------^-------^
Mureinik
  • 297,002
  • 52
  • 306
  • 350
0

You should specify column names without quotes

SELECT * FROM request WHERE premiseId IS NOT NULL;

Here documentation https://www.postgresql.org/docs/current/sql-syntax-lexical.html

Deepak Mahakale
  • 22,834
  • 10
  • 68
  • 88
Yevhen Bondar
  • 4,357
  • 1
  • 11
  • 31
0

Ah thanks for the replies!! I should have used double quotes instead as the column name has some characters that are upper case.

select * from request where "premiseId" is NOT NULL;
Eugene
  • 1,013
  • 1
  • 22
  • 43
0

when trying to reproduce your issue:

create table testtable (
    id integer primary key,
    some_text varchar
)

insert into testtable values 
(1, NULL), (2, 'abc'), (3, 'NULL'), (4, '[null]');

select * from testtable where some_text is not null

I got only expected results.

id some_text
2 abc
3 NULL
4 [null]

as the id 3,4 are texts NULL or [null] and not the actual "value" null. Maybe an issue with your loader and the representation in the source (e.g. csv)

Carsten Steckel
  • 1
  • did not see the single qoutes around your attribute names :-) well a constant string 'premiseId' is never NULL. better answer ;-) – Carsten Steckel Apr 23 '21 at 07:15