ERROR: operator does not exist: varchar >= integer when changing column type int to varchar in PostgreSQL

Question

I have a task to create a Liquibase migration to change a value affext in table trp_order_sold, which is right now int8, to varchar (or any other text type if it's more likely to be possible).

The script I made is following:

ALTER TABLE public.trp_order_sold
    ALTER COLUMN affext SET DATA TYPE VARCHAR
    USING affext::varchar;

I expected that USING affext::text; part is gonna work as a converter, however with or without it I am getting this error:

ERROR: operator does not exist: varchar >= integer
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Any hints on what I'm doing wrong? Also I am writing a PostgreSQL script but a working XML equivalent would be fine for me as well.

This column could be used in a generated column, a constraint or a trigger, for example. Can you share the DDL for this table? EDIT: A check constraint could do that. demo — Zegarek
– Zegarek, Commented Feb 7, 2023 at 16:27
1) It should work with just ALTER TABLE public.trp_order_sold ALTER COLUMN affext TYPE VARCHAR; 2) This ERROR: operator does not exist: varchar >= integer looks like it is coming from something else. Is there another operation in the migration that is touching the table? — Adrian Klaver
– Adrian Klaver, Commented Feb 7, 2023 at 16:46

Zegarek · Accepted Answer · 2023-02-08 09:50:25Z

2

These would most typically use or depend on your column:

a generated column
a trigger
a trigger's when condition
a view or a rule
a check constraint

In my test (online demo) only the last one leads to the error you showed:

create table test_table(col1 int);
--CREATE TABLE
alter table test_table add constraint test_constraint check (col1 >= 1);
--ALTER TABLE
alter table test_table alter column col1 type text using col1::text;
--ERROR:  operator does not exist: text >= integer
--HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

You'll have to check the constraints on your table with \d+ command in psql, or by querying the system tables:

SELECT con.*
FROM pg_catalog.pg_constraint con
    INNER JOIN pg_catalog.pg_class rel
               ON rel.oid = con.conrelid
    INNER JOIN pg_catalog.pg_namespace nsp
               ON nsp.oid = connamespace
WHERE nsp.nspname = 'your_table_schema'
     AND rel.relname = 'your_table_name';

Then you will need to drop the constraint causing the problem and build a new one to work with your new data type.

Since integer 20 goes before integer 100, but text '20' goes after text '100', if you plan to keep the old ordering behaviour you'd need this type of cast:

case when affext<0 then '-' else '0' end||lpad(ltrim(affext::text,'-'),10,'0')

and then make sure new incoming affext values are cast accordingly in an insert and update trigger. Or use a numeric ICU collation similar to this.

edited Feb 8, 2023 at 9:50

answered Feb 7, 2023 at 16:52

Zegarek

30k5 gold badges27 silver badges32 bronze badges

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

1 Comment

Jan Přibyl Over a year ago

Thank you, this worked. The problem indeed was the constraint.

Collectives™ on Stack Overflow

ERROR: operator does not exist: varchar >= integer when changing column type int to varchar in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related