当列具有DATE约束时,将空字符串更改为NULL [英] Change empty string to NULL when column has DATE constraint
问题描述
这可能是不可能的,但我想知道是否有经验的人知道在Postgresql中是否可以这样做。
This might be impossible but I was wondering if someone more experienced knew if this is possible to do in postgresql.
我的create语句中有一列
I have a column in my create statement
如果不存在则创建表(其他cols,some_date DATE,其他cols);
当我从API中提取json对象时,实际上可能有一个空字符串' '而不是一个空单元格。当然哪一个给我以下错误 psycopg2.errors.InvalidDatetimeFormat:类型为date的输入语法无效:
When I extract the json object from the API, there might actually be an empty string '' instead of a empty cell. Which of course gives me the following error psycopg2.errors.InvalidDatetimeFormat: invalid input syntax for type date: ""
解决方案只是将约束更改为VARCHAR,但我想知道 CREATE TABLE
或 INSERT $ c中是否有某种方法$ c>语句表示以下伪代码:
如果为空字符串,则插入NULL
。
The solution would simply to change the constraint to VARCHAR, but i was wondering if there was some way in the CREATE TABLE
or INSERT
statements to say the following pseudo code: if empty string insert NULL
.
推荐答案
在您的INSERT语句中使用 NULLIF
:
Use NULLIF
in your INSERT statement:
INSERT INTO your_table (cols..., some_date) VALUES (..., NULLIF(your_input_field, ''))
如果要输入的值是多个值中的任何一个,则要插入NULL,则使用CASE语句可能最简单:
If you want to insert NULL if the value in question is any of a number of values, it may be easiest to use a CASE statement:
INSERT INTO your_table (cols..., some_date)
VALUES (..., CASE WHEN your_input_field IN ('', '#', '-', '--', '??') THEN NULL ELSE your_input_field END)
可以与数组相同,如果更简单的话:
Could do the same with an array as well, if that's easier:
INSERT INTO your_table (cols..., some_date)
VALUES (..., CASE WHEN your_input_field = ANY('{"",#,-,--,??}'::TEXT[]) THEN NULL ELSE your_input_field END)
这篇关于当列具有DATE约束时,将空字符串更改为NULL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!