Error while changing data type of a column using alter column in postgresql

Question

I have a table where columns are like this:

system_name varchar(255),
from_package_name varchar(255),
from_version varchar(255)

The from_version column is of varying characters type right now which contains the version of a certain package. The versions should be compatible with this Semantic Versioning Spec but often times they are not.

So when I am trying to change the data type of this column from varchar to semver (Semver is a Postgres extension which is a data type for semantic versioning) using the following query:

ALTER TABLE relations
ALTER COLUMN from_version TYPE SEMVER
USING CAST((COALESCE(from_version,NULL)) AS SEMVER);

I am getting the following error:

ERROR:  bad semver value '2.16.0-27020175.00aeff62': semver prerelease numbers can't start with 0 

SQL state: XX000

which is understandable because not all values in the column are Semver compatible and I want to replace the values that cannot be converted to Semver type with 'NULL' (that's why tried using the COALESCE stmt).

How can I skip the errors and change the data type for all values of the column?

1) COALESCE(from_version,NULL) is not going to work as from_version is not a NULL value so it will the value returned. For more information see COALESCE. 2) You will need to clean up the values in the existing form of the table before doing the ALTER. — Adrian Klaver
– Adrian Klaver, Commented Aug 13, 2023 at 19:11

jjanes · Accepted Answer · 2023-08-13 19:42:49Z

2

Fortunately semver supplies a function suitable for this purpose, is_semvar.

alter table relations alter column from_version type semver using 
   case when is_semver(from_version) then semver(from_version) end

But I would think you would want to record and investigate, not just silently discard the broken ones.

answered Aug 13, 2023 at 19:42

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Error while changing data type of a column using alter column in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related