postgres / alter column / look closer
Just now, I tried to convert an integer column in a PostgreSQL database to one of type VARCHAR. I knew you had to do an explicit cast, so I was a bit stumped that I still wasn’t allowed to perform the ALTER TABLE.
mydb=> ALTER TABLE mytable ALTER COLUMN mycolumn TYPE VARCHAR(31) USING mycolumn::text;
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.
Wait what? A >= operator?
mydb=> \d mytable
...
Check constraints:
"mytable_mycolumn_check" CHECK (mycolumn >= 0)
...
In the Netherlands we call it looking further than one’s nose is long.
mydb=> ALTER TABLE DROP CONSTRAINT mytable_mycolumn_check;
ALTER TABLE
mydb=> ALTER TABLE mytable ALTER COLUMN mycolumn TYPE VARCHAR(31) USING mycolumn::text;
ALTER TABLE
Better..