Change column type of Postegresql Database

Asked

Viewed 6,734 times

1

I need to change the column type of a table

I’m using the following code:

ALTER TABLE cadastro_remetente ALTER COLUMN ssl_smtp DROP DEFAULT;

ALTER TABLE cadastro_remetente ALTER COLUMN ssl_smtp SET DEFAULT FALSE;

ALTER TABLE cadastro_remetente ALTER ssl_smtp TYPE bool USING CASE WHEN ssl_smtp='TRUE' THEN TRUE ELSE FALSE END;'

smtp is currently Character Varying

is returning me the following error:

ERROR: check restriction "ssl_smtp" was breached by some record

********** Error **********

ERROR: Check constraint "ssl_smtp" breached by record SQL state: 23514

All records are TRUE or FALSE

someone can help me in this?

postgresql

  • If the column is Boolean, the values accepted are, t/f, true/false, 1/0. I don’t understand the ssl_smtp DROP DEFAULT nor the CASE. You want to change the column type and keep the values?

  • Check by pgAdmin if there is a column in this table with a Constraint/constraint.

  • CONSTRAINT pk_registration PRIMARY KEY (id). currently the table ssl_smtp is Character Varying and I want to change to Boolean, and that’s what I’m not getting.....

  • Maybe there’s some check or Domain in the column ssl_smtp of a verified.

  • Show the table structure with \d cadastro_remetente on psql

  • i searched on the internet and from what I read it will be necessary to create a new table... pass the information to there, delete the old table and then rename the new table...

Show 1 more comment

2 answers

1

ALTER TABLE cadastro_remetente
  ADD COLUMN "ssl_smtp" BOOLEAN NOT NULL DEFAULT FALSE;

  • This information is if I were to create... I don’t want to create, I want to CHANGE from Varying to Boolean. in that column there is two information either it will be FALSE or it will be TRUE and I want to turn it into Boolean

0

medium old but I believe it serves for future consultations.

if the column has data, and they don’t hurt the rules of the new type, something like this should solve.

alter table cadastro_remetente alter column ssl_smtp  type boolean

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.