English 中文(简体)
员额核实如果无效,就算作数字
原标题:postgres check if null then cast to numeric
  • 时间:2012-02-09 20:35:18
  •  标签:
  • postgresql

我正在试图检查,如果某一数值无效,那么该选择就会变成数字,但会犯错误。 这实际上是插入说明的一部分。

INSERT into someTable(name,created,power) 
SELECT  xyz ,now(),
case when :power = null  then NULL else cast(:power as numeric) end from abc 

我发现的错误

Error: ERROR: invalid input syntax for type numeric: "null"

:权力是一种变量,可使用java法典给予任何价值。 如果我给出一个无效的价值,就会产生错误。

In code I get the following error from the java stack trace

org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to numeric
最佳回答

Error

SELECT CASE WHEN  null  =  null  THEN NULL ELSE cast( null  AS numeric) END 

No error

DO
$$
DECLARE
   power text :=  null ;
BEGIN
   PERFORM CASE WHEN power =  null  THEN null ELSE cast(power AS numeric) END;
END
$$

Explanation

如果你进行问询,即使有<条码>的表述也会产生例外情况,即使有<条码>。 ELSE 从未执行过,因为它是invalid content syntax,而这一例外是在 s(如错误信息所暗示的)期间而不是在执行期间提出的。

缩略语 字面上的二例在组别后与初审无关。

为了像这样充满活力,你需要检查一下之前的数值,然后才能建立查询卡。 或您使用职能或准备的声明,并将价值作为<>参数。 这也会奏效。

More advice after comment:

在你的特殊情况下,你可以检查 app具的价值,并打造这样的 que:

INSERT INTO tbl(name, abc_id, created, power) 
SELECT  xyz 
     , abc_id
     , now()
     , <insert_value_of_power_or_NULL_here> -- automatically converted to numeric
FROM   abc;

You may be interested in a different approach to INSERT data from a file conditionally.
Use COPY for files local to the server or psql s meta-command copy for files local to the client.

问题回答

if the field value is null, and you want in this case to map it to some value you can use coalesce(field_name, Some value ) or coalesce(field_name, 123).
For full documentation see here.

您必须同国际不动产业者进行核对,而不是在你处理国际不动产业联合会时做到:

INSERT into someTable(name,created,power) 
SELECT  xyz ,now(),
case when :power IS null then NULL else cast(:power as numeric) end from abc 
INSERT into someTable(name,created,power) SELECT  xyz ,now(),
   case :power when  null  then NULL else :power end::numeric from abc

I was trying to do something similar in order to update/insert some records where a numeric value can be null or not. You can validate a variable before you send it to the function or inside the function depending the value passed (For me using a variable is better than use CASE WHEN THEN ELSE END CASE every time you need to validate the value)

因此,与民族解放军的价值观合作,利用定期比较操作,以便找到更新的记录,可以通过向全国人民代表大会转变_null_ Equals来实现。

我希望这能帮助一个人

CREATE OR REPLACE FUNCTION update_insert_transaction(vcodaccount integer, vcodaccountaux text, 
  vdescription text, vcodgroup integer)
  RETURNS integer AS $$
DECLARE
  n integer = 0;
  vsql text =  NULL ; 
BEGIN 
    IF vcodaccountaux <>    THEN
        vsql = vcodaccountaux;
    END IF; 
        SET LOCAL transform_null_equals TO ON;
        EXECUTE  UPDATE account_import_conf SET (codaccount, codaccountaux, description, codgroup) = 
            ( ||vcodaccount|| , ||vsql|| ,trim( ||quote_literal(vdescription)|| ), ||vcodgroup|| ) 
            WHERE codaccount= ||vcodaccount||  AND codaccountaux =  ||vsql||  RETURNING *  ;
    GET DIAGNOSTICS n = ROW_COUNT;
    IF n = 0 THEN
    EXECUTE  INSERT INTO account_import_conf (codaccount, codaccountaux, description, codgroup)         
        SELECT  ||vcodaccount|| , ||vsql||  ,trim( ||quote_literal(vdescription)|| ), ||vcodgroup|| ; ;
    END IF;
RETURN n;
END;$$
  LANGUAGE plpgsql;




相关问题
摘录数据

我如何将Excel板的数据输入我的Django应用? I m将PosgreSQL数据库作为数据库。

Postgres dump of only parts of tables for a dev snapshot

On production our database is a few hundred gigabytes in size. For development and testing, we need to create snapshots of this database that are functionally equivalent, but which are only 10 or 20 ...

How to join attributes in sql select statement?

I want to join few attributes in select statement as one for example select id, (name + + surname + + age) as info from users this doesn t work, how to do it? I m using postgreSQL.

What text encoding to use?

I need to setup my PostgreSQL DB s text encoding to handle non-American English characters that you d find showing up in languages such as German, Spanish, and French. What character encoding should ...

SQL LIKE condition to check for integer?

I am using a set of SQL LIKE conditions to go through the alphabet and list all items beginning with the appropriate letter, e.g. to get all books where the title starts with the letter "A": SELECT * ...

热门标签