CODEWITHSUNDEEP
sqlpostgresql
Andrey Butenko
Andrey Butenko

Reputation: 11

Change the column type and data conversion

There is a column planned_duration that stores the time in minutes and is of type integer. You need to change the type to interval and to convert existing data. For this I wrote this SQL code:

ALTER TABLE shift ALTER COLUMN planned_duration TYPE INTERVAL USING planned_duration * INTERVAL '60 sec';

But unfortunately it does not work. I get this error:

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

What you need to do to properly change the column type and convert the data? Thanks.

Upvotes: 0

Views: 50

Answers (1)

Andrey Butenko
Andrey Butenko

Reputation: 11

The problem was to in constraint:

CONSTRAINT shift_planned_duration_check CHECK (planned_duration >= 0)

Solved the problem of removing this constraint before change column:

ALTER TABLE shift DROP CONSTRAINT shift_planned_duration_check;

Upvotes: 1

Related Questions