15

I have a table and I need to select all the rows with an empty value for the fk_fc_id field (as a prelude to deleting them),

    Column     |            Type             |                         Modifiers
---------------+-----------------------------+------------------------------------------------------------
 di_timestamp  | timestamp without time zone |
 di_item_value | character varying(10)       |
 fk_fc_id      | integer                     |
 di_id         | integer                     | not null default nextval('data_item_di_id_seq1'::regclass)

However this doesn't work,

# select fk_fc_id,di_timestamp,di_item_value from data_item where fk_fc_id="";
ERROR:  zero-length delimited identifier at or near """"
LINE 1: ...di_timestamp,di_item_value from data_item where fk_fc_id="";
                                                                    ^

Trying Null doesn't work either.

If anyone has any suggestions on how to sort this, I would be very grateful.

Improve this question
2
  • 1
    select fk_fc_id,di_timestamp,di_item_value from data_item where fk_fc_id is null
    – foibs
    Dec 3, 2013 at 12:57
  • 2
    Double quotes aren't used for strings in PostgreSQL. Single quotes are used for strings. But you shouldn't be comparing zero-length strings to integers! And comparing values with null using the = operator will always return null and cause that row to be excluded.
    – Colin 't Hart
    Dec 3, 2013 at 13:27

2 Answers 2

Reset to default
29

This is not really database administration related, nor is it really about PostgreSQL, but as @foibs answered, you should have a look at IS NULL: 

 SELECT fk_fc_id,
        di_timestamp,
        di_item_value  
 FROM data_item  
 WHERE fk_fc_id IS NULL
Improve this answer
2
  • 1
    Ah, I was trying = Null rather than is Null. Thanks,
    – James
    Dec 3, 2013 at 13:53
  • Both (=;IS) works fine in 9.4
    – Petr
    Jan 19, 2018 at 13:05
0

You have to use single quote:

SELECT fk_fc_id,
        di_timestamp,
        di_item_value  
 FROM data_item  
 WHERE fk_fc_id=''
Improve this answer
1
  • 1
    Technically, you are right, a string in SQL should be delimited with single quotes. However, in this specific case the OP shouldn't compare to a string (empty or not) in the first place, because the column in question is defined as integer. As you can read in the comments under the accepted answer, the OP decided to try comparison to an empty string only because they hadn't figured out how to correctly compare to null (which is what they were actually after).
    – Andriy M
    May 17, 2019 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.