Search code examples
postgresqlcastingliquibase

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

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.

Solution

  • These would most typically use or depend on your column:

    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.