postgresql 在查询中插入空值 [英] postgresql insert null value on query

问题描述

在我的 php 代码查询中，当我放置空值时出现错误进桌子.列名number1"、number2"和number3"是整数，可以为空值.

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

查询是这样的插入表(number1,number2,number3)值(1,2,3);

但是当我在 PHP 的输入表单上留下一个空白值时，

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

例如我不会为列number2"放置值，查询将看起来像这样.

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

插入 table(number1,number2,number3) 值 (1,,3);

它说错误:，"处或附近的语法错误SQL 状态:42601

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

有人知道如何解决这个问题吗?

Does anybody have an idea how to solve this?

此查询可能是解决方案，但还有其他方法吗?插入表(number1,number2,number3) 值(1,null,3);

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

因为我有很多这样的变量，而且我有点懒惰地设置一些条件，比如当该变量返回一个空值然后 value="null"

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"

推荐答案

您可以通过键入 NULL 来插入 NULL 值:

You insert NULL value by typing NULL:

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

如果您有一个变量，并且当该变量为空时，您想插入一个 NULL 值，您可以使用 NULLIF 将变量括在单引号中来为此做准备(这是有点脏的解决方案，因为您必须将变量视为空字符串，然后将其转换为整数):

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

这篇关于postgresql 在查询中插入空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！