How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
I was just wrestling with a similar problem myself, but didn't want the overhead of a function. I came up with the following query:
SELECT myfield::integer FROM mytable WHERE myfield ~ E'^\\d+$';
Postgres shortcuts its conditionals, so you shouldn't get any non-integers hitting your ::integer cast. It also handles NULL values (they won't match the regexp).
If you want zeros instead of not selecting, then a CASE statement should work:
SELECT CASE WHEN myfield~E'^\\d+$' THEN myfield::integer ELSE 0 END FROM mytable;
Cast string to number, interpreting null or empty string as 0
The types of values need to be consistent; coalescing the empty string to a 0 means that you cannot then compare it to null
in the nullif
. So either of these works:
# create table tests (orig varchar);
CREATE TABLE
# insert into tests (orig) values ('1'), (''), (NULL), ('0');
INSERT 0 4
# select orig, cast(coalesce(nullif(orig,''),'0') as float) as result from tests;
orig | result
------+--------
1 | 1
| 0
| 0
0 | 0
(4 rows)
# select orig, coalesce(cast(nullif(orig,'') as float),0) as result from tests;
orig | result
------+--------
1 | 1
| 0
| 0
0 | 0
(4 rows)
How to cast String into int in PostgreSql
Remove all non-digit characters, then you cast it to an integer:
regexp_replace(salary, '[^0-9]+', '', 'g')::int
But instead of trying to convert the value every time you select it, fix your database design and convert the column to a proper integer. Never store numbers in text columns.
alter table bad_design
alter salary type int using regexp_replace(salary, '[^0-9]+', '', 'g')::int;
Change type of varchar field to integer: cannot be cast automatically to type integer
There is no implicit (automatic) cast from text
or varchar
to integer
(i.e. you cannot pass a varchar
to a function expecting integer
or assign a varchar
field to an integer
one), so you must specify an explicit cast using ALTER TABLE ... ALTER COLUMN ... TYPE ... USING:
ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer);
Note that you may have whitespace in your text fields; in that case, use:
ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (trim(col_name)::integer);
to strip white space before converting.
This shoud've been obvious from an error message if the command was run in psql
, but it's possible PgAdmin-III isn't showing you the full error. Here's what happens if I test it in psql
on PostgreSQL 9.2:
=> CREATE TABLE test( x varchar );
CREATE TABLE
=> insert into test(x) values ('14'), (' 42 ');
INSERT 0 2
=> ALTER TABLE test ALTER COLUMN x TYPE integer;
ERROR: column "x" cannot be cast automatically to type integer
HINT: Specify a USING expression to perform the conversion.
=> ALTER TABLE test ALTER COLUMN x TYPE integer USING (trim(x)::integer);
ALTER TABLE
Thanks @muistooshort for adding the USING
link.
See also this related question; it's about Rails migrations, but the underlying cause is the same and the answer applies.
If the error still occurs, then it may be related not to column values, but indexes over this column or column default values might fail typecast. Indexes need to be dropped before ALTER COLUMN and recreated after. Default values should be changed appropriately.
Casting an array of strings to an array of integers
demo:db<>fiddle
SELECT
array_agg(elems::int)
FROM unnest(ARRAY['5', '6']) as elems
- Expand the array into one record per element
- Reaggregate cast integer values
To ensure the original order, you need to add WITH ORDINALITY
, which adds an index to the original array:
SELECT
array_agg(elems.value::int ORDER BY elems.index)
FROM unnest(ARRAY['5', '6']) WITH ORDINALITY as elems(value, index)
If you have a JSON array instead, the algorithm is the same, only the used functions have different names:
SELECT
json_agg(elems.value::int ORDER BY elems.index)
FROM json_array_elements_text('["5", "6"]'::json) WITH ORDINALITY as elems(value, index)
EDIT: According to comment:
this is my query. SELECT data->>'pid' FROM user_data where
data->>'pid' is not null How can I update pid to array of integers ?
demo:db<>fiddle
You have to expand and reaggregate nonetheless:
SELECT
json_agg(elems::int) -- 2
FROM user_data,
json_array_elements_text(data -> 'pid') as elems -- 1
WHERE data->>'pid' IS NOT NULL
GROUP BY id
Related Topics
SQL Insert into Database with Apostrophe
Differencebetween Group by and Order by in SQL
How to Write Update SQL with Table Alias in SQL Server 2008
Finding the Next Available Id in MySQL
Column Name or Number of Supplied Values Does Not Match Table Definition
Split Words with a Capital Letter in SQL
Query Runs Slow with Date Expression, But Fast with String Literal
Passing SQL "In" Parameter List in Jasperreport
How to See the Values of a Table Variable at Debug Time in T-Sql
Mysql: Group_Concat with Left Join
How to Read the Last Row with SQL Server
Modify Default Value in SQL Server
What's the Execute Order of the Different Parts of a SQL Select Statement
Built-In Function to Capitalise the First Letter of Each Word