12

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.

  • 1
    select fk_fc_id,di_timestamp,di_item_value from data_item where fk_fc_id is null – foibs Dec 3 '13 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 '13 at 13:27
20

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
  • 1
    Ah, I was trying = Null rather than is Null. Thanks, – James Dec 3 '13 at 13:53
  • Both (=;IS) works fine in 9.4 – Petr Jan 19 '18 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=''
  • 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 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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