CODEWITHSUNDEEP
postgresql
Dejan Maksimovic
Dejan Maksimovic

Reputation: 507

Invalid input when converting varchar column to UUID

I would like to change data type of a column in PostgreSQL:

alter column camelCaseColumn TYPE uuid USING uuid(camelCaseColumn)

But I can't seem to find a way to do it. The trouble is when I use double quotes in uuid call, I get an error:

invalid input syntax for uuid: ""

Is there something wrong with my approach or is there a way to accomplish this.

Upvotes: 1

Views: 1104

Answers (1)

user330315
user330315

Reputation:

The error message seems to indicate that you are converting from a text (or varchar) column to uuid and you have empty strings ('') in that column. In that case you need to treat them as NULL. The name of the column is irrelevant for that error.

alter column camelCaseColumn TYPE uuid USING uuid(nullif(camelCaseColumn, ''));

However, if you really created mixed cased columns (which is a bad idea to start with) you have to use double quotes to refer to them:

alter column "camelCaseColumn" TYPE uuid USING uuid(nullif("camelCaseColumn", ''));

Upvotes: 4

Related Questions