I have an integer column size in my table product in PostgreSQL 10.5 database. I want to change it's type from int to varchar(20) using alter table if possible.

The obvious way which worked for me with other types is to execute statement below.

alter table product alter column "size" SET DATA type varchar(20) using size::varchar;

Unfortunately, this way of conversion doesn't work and the error returned:

ERROR:  operator does not exist: character varying > integer
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
SQL state: 42883

I tried a bit of googling and searching through StackExchange network, but I haven't found a solution. With this error I'm not sure that creating a new column, transfering data into it will work because I still have to cast information from one column to another.

  • Can you try with cast? using cast("size" as varchar) or cast("size" as text) – Gerard H. Pille Oct 21 '18 at 13:52
  • Why do you double-quote "size"? I've set up a dbfiddle an it works. Have you tried with text datatype instead of varchar()? – McNets Oct 21 '18 at 14:06
  • 3
    are there any constraint on the table? eg: check( size > 0) – Jasen Oct 21 '18 at 19:46
  • 1
    There was a check for it! thank you! After removing a constraint everything got working as it should! If you'd write this as an answer, I'll accept it – Eir Nym Oct 21 '18 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.