CODEWITHSUNDEEP
sqlpostgresqlpgadmin
Nix
Nix

Reputation: 211

Postgres treats character varying as integer

I have a column in a relation schema of data type character varying. Though the column is of character varying I have been inserting data of integer type and did not realize the error until I actually needed to input data of character varying type. This is the error I get

insert into my_table(bad_column) values ('bad column');  

Error: invalid input syntax for integer: "bad column".

What might be the problem? I have confirmed the data type of the column by running the code below and it actually returns character varying

select pg_typeof(bad_column) FROM my_table limit 10

Upvotes: 1

Views: 1657

Answers (1)

klin
klin

Reputation: 121889

You should check whether the table definition has a check constraint, e.g.

create table test(bad_column varchar check (bad_column::int > 0));
insert into test (bad_column) values ('a text');

ERROR:  invalid input syntax for integer: "a text"

The other option is that there is a cast to integer of the column in a trigger.

Both cases are rather bizzare solutions, you should alter the type of the column to integer.

Upvotes: 1

Related Questions