postgresql insert null value on query

Issue

In my php code query, i got some error when i will place a null value
into the table. The column names “number1″,”number2”, and “number3” are integers and can have a null value.

if there is no null value, the query works

query is like this
insert into table(number1,number2,number3) values (1,2,3);

but when I leave a blank value on the input form on the PHP,

for example I will not place value for column “number2”, the query will look
like this.

insert into table(number1,number2,number3) values (1,,3);

it says ERROR: syntax error at or near “,”
SQL state: 42601

Does anybody have an idea how to solve this?

this query is likely to be the solution but is there any other way?
insert into table(number1,number2,number3) values (1,null,3);

because i got so many variables like that and am a bit lazy to place some conditions like when that variable returns a blank value then value=”null”

Solution

You insert NULL value by typing NULL:

INSERT INTO table(number1,number2,number3) VALUES (1,NULL,3);

If you have a variable and when that variable is empty you want to insert a NULL value you can use NULLIF with the variable enclosed in single quotes to prepare for that (this is somewhat dirty solution as you have to treat the variable as an empty string and then convert it to integer):

INSERT INTO table(number1,number2,number3) VALUES (1,NULLIF('$var','')::integer,3);

Answered By – Simo Kivistö

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published