在 PostgreSQL 中,我有一个带有 varchar 列的表。数据应该是整数,我在查询中需要整数类型的数据。有些值是空字符串。以下:
SELECT myfield::integer FROM mytable
产生 ERROR: invalid input syntax for integer: ""
如果在 postgres 中的转换过程中出现错误,我如何查询转换并获得 0?
我自己只是在努力解决类似的问题,但不想要函数的开销。我想出了以下查询:
SELECT myfield::integer FROM mytable WHERE myfield ~ E'^\\d+$';
Postgres 缩短了它的条件,所以你不应该让任何非整数命中你的 ::integer 演员表。它还处理 NULL 值(它们与正则表达式不匹配)。
如果您想要零而不是不选择,那么 CASE 语句应该可以工作:
SELECT CASE WHEN myfield~E'^\\d+$' THEN myfield::integer ELSE 0 END FROM mytable;
您还可以创建自己的转换函数,在其中可以使用异常块:
CREATE OR REPLACE FUNCTION convert_to_integer(v_input text)
RETURNS INTEGER AS $$
DECLARE v_int_value INTEGER DEFAULT NULL;
BEGIN
BEGIN
v_int_value := v_input::INTEGER;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Invalid integer value: "%". Returning NULL.', v_input;
RETURN NULL;
END;
RETURN v_int_value;
END;
$$ LANGUAGE plpgsql;
测试:
=# select convert_to_integer('1234');
convert_to_integer
--------------------
1234
(1 row)
=# select convert_to_integer('');
NOTICE: Invalid integer value: "". Returning NULL.
convert_to_integer
--------------------
(1 row)
=# select convert_to_integer('chicken');
NOTICE: Invalid integer value: "chicken". Returning NULL.
convert_to_integer
--------------------
(1 row)
INSERT
语句中使用函数 while 将字符串转换为特定字段的整数?
我有同样的需求,发现这对我很有效(postgres 8.4):
CAST((COALESCE(myfield,'0')) AS INTEGER)
一些测试用例来演示:
db=> select CAST((COALESCE(NULL,'0')) AS INTEGER);
int4
------
0
(1 row)
db=> select CAST((COALESCE('','0')) AS INTEGER);
int4
------
0
(1 row)
db=> select CAST((COALESCE('4','0')) AS INTEGER);
int4
------
4
(1 row)
db=> select CAST((COALESCE('bad','0')) AS INTEGER);
ERROR: invalid input syntax for integer: "bad"
如果您需要处理字段包含非数字文本的可能性(例如“100bad”),您可以使用 regexp_replace 在强制转换之前去除非数字字符。
CAST(REGEXP_REPLACE(COALESCE(myfield,'0'), '[^0-9]+', '', 'g') AS INTEGER)
然后像 "b3ad5" 这样的 text/varchar 值也会给出数字
db=> select CAST(REGEXP_REPLACE(COALESCE('b3ad5','0'), '[^0-9]+', '', 'g') AS INTEGER);
regexp_replace
----------------
35
(1 row)
为了解决 Chris Cogdon 对解决方案没有在所有情况下都给出 0 的担忧,包括诸如“坏”(根本没有数字字符)之类的情况,我做了这个调整后的声明:
CAST((COALESCE(NULLIF(REGEXP_REPLACE(myfield, '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);
它的工作原理类似于更简单的解决方案,除了当要转换的值仅是非数字字符时给出 0,例如“bad”:
db=> select CAST((COALESCE(NULLIF(REGEXP_REPLACE('no longer bad!', '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);
coalesce
----------
0
(1 row)
(0 || myfield)::integer
说明(在 Postgres 8.4 上测试):
上述表达式为 myfield
中的 NULL 值生成 NULL
,为空字符串生成 0
(此确切行为可能适合您的用例,也可能不适合)。
SELECT id, (0 || values)::integer from test_table ORDER BY id
测试数据:
CREATE TABLE test_table
(
id integer NOT NULL,
description character varying,
"values" character varying,
CONSTRAINT id PRIMARY KEY (id)
)
-- Insert Test Data
INSERT INTO test_table VALUES (1, 'null', NULL);
INSERT INTO test_table VALUES (2, 'empty string', '');
INSERT INTO test_table VALUES (3, 'one', '1');
查询将产生以下结果:
---------------------
|1|null |NULL|
|2|empty string|0 |
|3|one |1 |
---------------------
而仅选择 values::integer
将导致错误消息。
(trim(values) || '.0')::numeric
完美运行。
@Matthew's answer 很好。但它可以更简单、更快。问题要求将空字符串 (''
) 转换为 0
,而不是其他“无效输入语法”或“超出范围”输入:
CREATE OR REPLACE FUNCTION convert_to_int(text)
RETURNS int AS
$func$
BEGIN
IF $1 = '' THEN -- special case for empty string like requested
RETURN 0;
ELSE
RETURN $1::int;
END IF;
EXCEPTION WHEN OTHERS THEN
RETURN NULL; -- NULL for other invalid input
END
$func$ LANGUAGE plpgsql IMMUTABLE;
这会为空字符串返回 0
,为任何其他无效输入返回 NULL
。
它可以轻松适应任何数据类型转换。
进入异常块的成本要高得多。如果空字符串很常见,那么在引发异常之前捕获这种情况是有意义的。如果空字符串非常罕见,则将测试移至异常子句是值得的。
SELECT CASE WHEN myfield="" THEN 0 ELSE myfield::integer END FROM mytable
我从未使用过 PostgreSQL,但我检查了 manual 以了解 SELECT 查询中 IF 语句的正确语法。
SUBSTRING 在某些情况下可能会有所帮助,您可以限制 int 的大小。
SELECT CAST(SUBSTRING('X12312333333333', '([\d]{1,9})') AS integer);
null
案例。所以SELECT COALESCE(CAST(SUBSTRING('X12312333333333', '([\d]{1,9})') AS integer), 0);
CREATE OR REPLACE FUNCTION parse_int(s TEXT) RETURNS INT AS $$
BEGIN
RETURN regexp_replace(('0' || s), '[^\d]', '', 'g')::INT;
END;
$$ LANGUAGE plpgsql;
如果输入字符串中没有数字,此函数将始终返回 0
。
SELECT parse_int('test12_3test');
将返回 123
最后,我设法忽略了无效字符,只获取将文本转换为数字的数字。
SELECT (NULLIF(regexp_replace(split_part(column1, '.', 1), '\D','','g'), '')
|| '.' || COALESCE(NULLIF(regexp_replace(split_part(column1, '.', 2), '\D','','g'),''),'00')) AS result,column1
FROM (VALUES
('ggg'),('3,0 kg'),('15 kg.'),('2x3,25'),('96+109'),('1.10'),('132123')
) strings;
我发现以下代码既简单又有效。原始答案在这里https://www.postgresql.org/message-id/371F1510.F86C876B@sferacarta.com
prova=> create table test(t text, i integer);
CREATE
prova=> insert into test values('123',123);
INSERT 64579 1
prova=> select cast(i as text),cast(t as int)from test;
text|int4
----+----
123| 123
(1 row)
希望能帮助到你
以下功能确实
对不可转换的结果使用默认值 (error_result),例如 abc 或 999999999999999999999999999999999999999999
保持 null 为 null
修剪输入中的空格和其他空白
将转换为有效 bigint 的值与 lower_bound 进行比较,例如仅强制执行正值
CREATE OR REPLACE FUNCTION cast_to_bigint(text)
RETURNS BIGINT AS $$
DECLARE big_int_value BIGINT DEFAULT NULL;
DECLARE error_result BIGINT DEFAULT -1;
DECLARE lower_bound BIGINT DEFAULT 0;
BEGIN
BEGIN
big_int_value := CASE WHEN $1 IS NOT NULL THEN GREATEST(TRIM($1)::BIGINT, lower_bound) END;
EXCEPTION WHEN OTHERS THEN
big_int_value := error_result;
END;
RETURN big_int_value;
END;
如果数据应该是整数,而您只需要将这些值作为整数,那么您为什么不走完整个英里并将该列转换为整数列呢?
然后,您可以在将数据插入表的系统点将非法值转换为零一次。
通过上述转换,您将强制 Postgres 为该表的每个查询中的每一行一次又一次地转换这些值 - 如果您对该表中的该列执行大量查询,这可能会严重降低性能。
我也有同样的需求,但适用于 JPA 2.0 和 Hibernate 5.0.2:
SELECT p FROM MatchProfile p WHERE CONCAT(p.id, '') = :keyword
创造奇迹。我认为它也适用于 LIKE。
这也应该完成这项工作,但这是跨 SQL 而不是 postgres 特定的。
select avg(cast(mynumber as numeric)) from my table
不定期副业成功案例分享
E'\\d{1,5}$'
。{1,5}
限制可能是一个好主意,但它会掩盖更大的数字,如果您正在转换表格,这可能会导致麻烦。就我个人而言,我宁愿把查询错误放在前面,并且知道我的一些“整数”是有问题的(您也可以先选择E'\\d{6,}$'
以确保)。