Can an integer column be null?

Question

I made an integer column that is null by default but when I put empty double quotes "" it gives this error:

ERROR: invalid input syntax for integer: ""

Does the integer column have to be 0 then?

I am using Java to send the query to the database.

Solution 1

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.

Solution 2

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'd actually pass

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

Comments

[efoc]

I also have tried typing the word null it self, with and without quotes but still an error.