postgresql insert null value on query

Question

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"

Simo Kivistö · Accepted Answer · 2015-03-20 09:16:10Z

53

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);

edited Mar 20, 2015 at 9:16

answered Mar 20, 2015 at 8:56

Simo Kivistö

4,5933 gold badges41 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

huddreth Over a year ago

is there any other solution than that? ^_^ because i got so many variables like that and i don't wan't to waste my time placing conditions on the variables whenever it returns a blank value, then assign that variable="null" .. By the way thanks for that :)

Simo Kivistö Over a year ago

Ok, added another option.

huddreth Over a year ago

tanx SIR... i place this on my code since the datatype is double precision INSERT INTO table(number1,number2,number3) VALUES (1,cast(NULLIF('$variable[$i]','') as double precision),3);

Frank Heikens Over a year ago

$variable, be careful of SQL injection!

huddreth Over a year ago

What should I do SIR to avoid injections? ^_^

Collectives™ on Stack Overflow

postgresql insert null value on query

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related