An integer column can be null, but '' is an empty string not null. The right syntax for a null integer (or any other sql type) is null.
An integer column can be null, but '' is an empty string not null. The right syntax for a null integer (or any other sql type) is null.
Double quotes can be used to delimit identifiers, like "myColumnName".
Single quotes are used to delimit values in string literals, like 'my string literal'.
For integer values you typically use numeric constants to input values, which are not quoted at all, like 123. (But you can cast a string literal, too.)
Numeric data types (integer, numeric, double precision, ...) cannot store empty strings (''), only string types (text, varchar, ...) can.
To pass a null value, use the key word null. Or omit the column completely from your INSERT statement. If you did not define a different column default, it defaults to null automatically.
The double quotes you see in the error message are just delimiters added by Postgres for the purpose of the error message, meaning you passed an empty string (''). If you had actually passed
empty double quotes ""
(which would require to be single-quoted in turn: '""'), you would see this error message:
ERROR: invalid input syntax for integer: """"
Not sure how Java plays into this.
Consider the chapter Lexical Structure in the Postgres manual.
Related:
- Insert text with single quotes in PostgreSQL
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
You have to use single quote:
SELECT fk_fc_id,
di_timestamp,
di_item_value
FROM data_item
WHERE fk_fc_id=''
The types of values need to be consistent; coalescing the empty string to a 0 means that you cannot then compare it to null in the nullif. So either of these works:
# create table tests (orig varchar);
CREATE TABLE
# insert into tests (orig) values ('1'), (''), (NULL), ('0');
INSERT 0 4
# select orig, cast(coalesce(nullif(orig,''),'0') as float) as result from tests;
orig | result
------+--------
1 | 1
| 0
| 0
0 | 0
(4 rows)
# select orig, coalesce(cast(nullif(orig,'') as float),0) as result from tests;
orig | result
------+--------
1 | 1
| 0
| 0
0 | 0
(4 rows)
You could also use
cast(
case
when coalesce(orig, '') = '' then '0'
else orig
end
as float
)
You could also unwrap that a bit since you're being fairly verbose anyway:
cast(
case
when orig is null then '0'
when orig = '' then '0'
else orig
end
as float
)
or you could put the cast inside the CASE:
case
when coalesce(orig, '') = '' then 0.0
else cast(orig as float)
end
A CASE makes it a bit easier to account for any other special conditions, this also seems like a clearer expression of the logic IMO. OTOH, personal taste and all that.