invalid input syntax for integer: "1" postgresql

Question

PostgreSql gives me this error when i try to cast a TEXT colum to a integer.

select pro_id::integer  from mmp_promocjas_tmp limit 1;

This colum contains only digits, valid integer. How can "1" be invalid integer?

select pro_id, length(pro_id) ,length(trim(pro_id)) from mmp_promocjas_tmp limit 1;

outputs:

1 | 2 | 2

Query select pro_id from mmp_promocjas_tmp where trim(pro_id) = '1' shows nothing.

I tried to remove whitespaces, without no result:

select pro_id  from mmp_promocjas_tmp  where  regexp_replace(trim(pro_id), '\s*', '', 'g')

Attach query that is giving you the error. What datatype is column pro_id? Could you list also length(pro_id) ? — Kamil Gosciminski
– Kamil Gosciminski, Commented Feb 29, 2016 at 20:10
Is pro_id equal to '1' or '"1"'? And what is the exact error message? — mu is too short
– mu is too short, Commented Feb 29, 2016 at 20:27

Daniel Vérité · Accepted Answer · 2016-02-29 20:31:28Z

1

There are probably spurious invisible contents in the column. To make them visible, try a query like this:

 select pro_id, c,lpad(to_hex(ascii(c)),4,'0') from (
      select pro_id,regexp_split_to_table(pro_id,'')  as c
       from (select pro_id from mmp_promocjas_tmp limit 10) as s
 ) as g;

This will show the ID and each character its contains, both as a character and as its hexadecimal code in the repertoire.

answered Feb 29, 2016 at 20:31

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

1 Comment

Damian Over a year ago

Problem affected only the first row (imported from csv) so I updated value to '1' and it worked. I can't really check if your solution helps, but I presume so.

Collectives™ on Stack Overflow

invalid input syntax for integer: "1" postgresql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related