如何在使用PostgreSQL进行转换时将字符串转换为整数并且为0时出现错误？

11 回答

• 3

  我自己只是在解决类似的问题，但不想要一个函数的开销 . 我想出了以下查询：

  SELECT myfield::integer FROM mytable WHERE myfield ~ E'^\\d+$';
  

  Postgres会快速调整其条件，因此你不应该让任何非整数命中你的::整数 . 它还处理NULL值（它们与regexp不匹配） .

  如果你想要零而不是不选择，那么CASE语句应该有效：

  SELECT CASE WHEN myfield~E'^\\d+$' THEN myfield::integer ELSE 0 END FROM mytable;
  

  回复于 2024-04-20T15:44:00+08:00
• 0

  您还可以创建自己的转换函数，在其中可以使用异常块：

  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)
  

  回复于 2024-04-20T15:44:00+08:00
• -3

  我有同样的需求，发现这对我有用（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对解决方案的关注，不解决所有情况，包括诸如“坏”（完全没有数字字符）之类的情况，我做了这个调整后的声明：

  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)
  

  回复于 2024-04-20T15:44:00+08:00
• 3

  这可能有点像黑客，但它在我们的案例中完成了工作：

  (0 || myfield)::integer
  

  Explanation (Tested on Postgres 8.4):

  对于空字符串，上面提到的表达式为 myfield 和 0 中的NULL值产生 NULL （这种确切行为可能适合您的用例，也可能不适合您的用例） .

  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 将导致错误消息 .

  希望这可以帮助 .

  回复于 2024-04-20T15:44:00+08:00
• 18

  SELECT CASE WHEN myfield="" THEN 0 ELSE myfield::integer END FROM mytable

  我从未使用过PostgreSQL，但是我在manual中检查了SELECT查询中IF语句的正确语法 .

  回复于 2024-04-20T15:44:00+08:00
• 79

  @Matthew's answer很好 . 但它可以更简单，更快捷 . 并且问题要求将空字符串（ '' ）转换为 0 ，而不是其他"invalid input syntax"或"out of range"输入：

  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 .
  它很容易适应 any data type conversion .

  输入异常块要贵得多 . 如果空字符串是常见的，那么在引发异常之前捕获该情况是有意义的 .
  如果空字符串非常罕见，则将测试移动到exception子句是值得的 .

  回复于 2024-04-20T15:44:00+08:00
• 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

  回复于 2024-04-20T15:44:00+08:00
• 1

  我发现以下代码简单易用 . 原始答案在这里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)
  

  希望能帮助到你

  回复于 2024-04-20T15:44:00+08:00
• 132

  如果数据应该是整数，并且您只需要将这些值作为整数，那么为什么不进入整个里程并将列转换为整数列？

  然后，您可以在将数据插入表中的系统点将非法值转换为零一次 .

  通过上述转换，您强制Postgres为该表的每个查询中的每一行反复转换这些值 - 如果您对此表中的此列执行大量查询，则会严重降低性能 .

  回复于 2024-04-20T15:44:00+08:00
• 23

  我也有同样的需求，但适用于JPA 2.0和Hibernate 5.0.2：

  SELECT p FROM MatchProfile p WHERE CONCAT(p.id, '') = :keyword
  

  令人惊奇 . 我认为它也适用于LIKE .

  回复于 2024-04-20T15:44:00+08:00
• 1

  这应该也可以完成这项工作，但这是跨越SQL而不是特定于postgres .

  select avg(cast(mynumber as numeric)) from my table
  

  回复于 2024-04-20T15:44:00+08:00