Hi,

I've created the following table as an example from a bigger one:

# \d lixo
Table "public.lixo"
Column | Type | Modifiers
----------+-----------------------+-----------
id | bigint |
sp_fk_id | integer |
sp_char | character varying(10) |


# select * from lixo;
id | sp_fk_id | sp_char
----+----------+---------
43 | 434198 | 434198
44 | |
45 | 347861 | 347861
46 | 407136 | 407136
47 | |
48 | |
(6 rows)

The sp_char column was an attempt I made to try to solve the problem.
It's equal to column 'sp_fk_id' casted to varchar(10).

I want to be able to get all rows that have no values for column
'sp_fk_id' (or 'sp_char').

# select * from lixo where sp_fk_id = null;
id | sp_fk_id | sp_char
----+----------+---------
(0 rows)


# select * from lixo where char_length(sp_fk_id) = null;
id | sp_fk_id | sp_char
----+----------+---------
(0 rows)


# select * from lixo where char_length(sp_char) = 0;
id | sp_fk_id | sp_char
----+----------+---------
(0 rows)


# select * from lixo where char_length(sp_char) < 6;
id | sp_fk_id | sp_char
----+----------+---------
(0 rows)


# select * from lixo where char_length(sp_fk_id) < 6;
id | sp_fk_id | sp_char
----+----------+---------
(0 rows)


# select * from lixo where sp_fk_id != 434198;
id | sp_fk_id | sp_char
----+----------+---------
45 | 347861 | 347861
46 | 407136 | 407136
(2 rows)

# select * from lixo where sp_char != '434198';
id | sp_fk_id | sp_char
----+----------+---------
45 | 347861 | 347861
46 | 407136 | 407136
(2 rows)

What am I missing? How can I select rows containing null values for
sp_fk_id or sp_char?


Luis P. Mendes

begin Luis P. Mendes <luis_lupe2 (AT) netvisao (DOT) pt> wrote:

You can't compare with NULL, NULL is nothing, and you can't compare
something with nothing.

But, you can use:

select * from lixo where sp_fk_id is null;



end
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Andreas Kretschmer escreveu:

Luis P. Mendes