How to add new check type column in postgres?

0

I am working under a database that already has content, so I just need to add a role column in the user table, this column should only allow 3 values: [alumno,academico,administrador] .

When I create a table I usually only do the following:

CREATE TABLE public.users
(
  id integer NOT NULL DEFAULT nextval('users_id_seq'),
  username character varying(255) NOT NULL,
  password character varying(255) NOT NULL,
  persona_id integer NOT NULL,
  estado character varying(255) NOT NULL DEFAULT 'enable'::character varying,
//la siguiente linea es la ue deseo agregar.
  rol character varying(255) NOT NULL DEFAULT 'estudiante'::character varying,
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT users_persona_id_foreign FOREIGN KEY (persona_id)
      REFERENCES public.personas (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT users_username_unique UNIQUE (username),
  CONSTRAINT users_estado_check CHECK (estado::text = ANY (ARRAY['enable'::character varying::text, 'disable'::character varying::text])),
//aqui limito sus valores
  CONSTRAINT users_rol_check CHECK (rol::text = ANY (ARRAY['estudiante'::character varying::text, 'academico'::character varying::text, 'administrador'::character varying::text]))
)
WITH (
  OIDS=FALSE
);

Of course the current table does not have the role column, which is what I want to add, try it in the following way, but it does not work for me:

ALTER TABLE users ADD rol VARCHAR(20) NULL,
CONSTRAINT users_rol_check CHECK (rol::text = ANY (ARRAY['estudiante'::character varying::text, 'academico'::character varying::text, 'administrador'::character varying::text]))

Of course, the line of ALTER TABLE users ADD rol VARCHAR(20) NULL; works perfect, so the question would be how to add the following line:

  

CONSTRAINT users_rol_check CHECK (rol::text = ANY (ARRAY['estudiante'::character varying::text, 'academico'::character varying::text, 'administrador'::character varying::text]))

    
asked by Shassain 07.04.2018 в 02:05
source

0 answers