How cast field with null or text values to DOUBLE PRECISION in Postgres?

Question

I wanna cast my_field to DOUBLE PRECISION in select query. my_field return NULL or Text.

I try this:

SELECT
cast(my_table.my_field AS DOUBLE PRECISION) AS my_double_field
FROM mytablename my_table;

And this:

CASE
  WHEN (my_table.my_field IS NULL)
   THEN NULL
   ELSE my_table.my_field AS DOUBLE PRECISION)
END AS my_double_field

I got error:

ERROR: invalid input syntax for type double precision: "null"

So I try with function:

CREATE FUNCTION casttodouble(VARCHAR) RETURNS DOUBLE PRECISION AS $$
DECLARE
  text_colum text := $1;
BEGIN
  RETURN cast(text_colum AS DOUBLE PRECISION);
END;
$$ LANGUAGE plpgsql;

  SELECT
    CASE
      WHEN (my_table.my_field IS NOT NULL)
        THEN casttodouble(my_table.my_field)
        ELSE NULL
      END AS my_double_field,
  FROM mytablename my_table;

And got:

ERROR: invalid input syntax for type double precision: "null"
Where: SQL statement "SELECT cast(text_colum AS DOUBLE PRECISION)"
PL/pgSQL function casttodouble(character varying) line 5 at RETURN

EDIT:

This didn't fail:

SELECT
cast(NULL AS DOUBLE PRECISION) AS my_double_field
FROM mytablename my_table;

Just to check: are you sure you don't have a string 'null' in the column (which is different from the NULL value)? IE does SELECT * FROM mytablename my_table WHERE my_field ILIKE 'null'; return something? — Marth
– Marth, Commented Aug 3, 2017 at 8:34
@Marth Thank you, you are right. I got 'null', not NULL. I did not notice that. — Olesia Ilchuk
– Olesia Ilchuk, Commented Aug 3, 2017 at 8:41

Vao Tsun · Accepted Answer · 2017-08-03 08:13:34Z

2

Just cast. You don't need all that logic, below I cast string and null to double precision in one CAST:

t=# with c(v) as (values('2.33'),(null),(3.222))
select cast (v as double precision) from c;
   v   
-------
  2.33

 3.222
(3 rows)

answered Aug 3, 2017 at 8:13

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Olesia Ilchuk Over a year ago

I got error message: ERROR: invalid input syntax for type double precision: "null"

Vao Tsun Over a year ago

what's your version of postgres?.. select version()

Olesia Ilchuk Over a year ago

PostgreSQL 9.6.3

Vao Tsun Over a year ago

t=# select version();                                                      version                                                      -----------------------------------------------------------------------------------------------------------------  PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit (1 row)

at least confusing...

Pavel Stehule · Accepted Answer · 2017-08-03 08:42:43Z

1

You should not to pass NULL as "NULL" string. NULL should be everywhere NULL value. You have to distinct between NULL and "NULL". If you cannot, then you have to eliminate "NULL" string first:

CREATE OR REPLACE FUNCTION to_double1(text)
RETURNS double precision AS $$
  SELECT CASE WHEN upper($1) = 'NULL' THEN NULL
         ELSE $1::double precision END
$$ LANGUAGE sql IMMUTABLE STRICT;

But the basic rule is hold NULL as real NULL not as string "NULL". It is design error.

For single line function is better to use SQL language instead PLpgSQL. There is big chance for inlining instead evaluation of function - what can be faster. Don't forget flags IMMUTABLE and STRICT. These flags can be used for query optimization.

postgres=# \pset null ****
Null display is "****".
postgres=# SELECT to_double1('10101.1');
┌────────────┐
│ to_double1 │
╞════════════╡
│    10101.1 │
└────────────┘
(1 row)

postgres=# SELECT to_double1(NULL); -- correct
┌────────────┐
│ to_double1 │
╞════════════╡
│       **** │
└────────────┘
(1 row)

postgres=# SELECT to_double1('NULL'); -- should not be
┌────────────┐
│ to_double1 │
╞════════════╡
│       **** │
└────────────┘
(1 row)

edited Aug 3, 2017 at 8:42

answered Aug 3, 2017 at 8:34

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

2 Comments

Olesia Ilchuk Over a year ago

That's not my database, I only can read from it, so I did not notice 'null' but not NULL in values. Thank, you for advice about functions

Pavel Stehule Over a year ago

Maybe you can use a function

CREATE OR REPLACE FUNCTION to_null(text) RETURNS text AS $$ SELECT CASE WHEN upper($1) = 'NULL' THEN NULL ELSE $1 END $$ LANGUAGE sql IMMUTABLE STRICT

.

Collectives™ on Stack Overflow

How cast field with null or text values to DOUBLE PRECISION in Postgres?

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related