How do I set a two-condition check constraint?

Question

PostgreSQL

I'm trying to organize adding a column like this

ALTER TABLE main_owner ADD COLUMN fsex varchar(1) NOT NULL  check ('M' or 'W');

I get this error
ERROR: invalid input syntax for type boolean: "M"
, how can I fix it and what is causing it?

Answer 1

ALTER TABLE main_owner ADD COLUMN fsex varchar(1) NOT NULL  check (fsex = 'M' or fsex = 'W');

Or like this:

ALTER TABLE main_owner ADD COLUMN fsex varchar(1) NOT NULL  check (fsex IN('M', 'W'));

Answer 2

For enums, you can use native enum

melkij=> create type gender as enum('M','W');
CREATE TYPE
melkij=> create table foo (f gender);
CREATE TABLE
melkij=> insert into foo values('M');
INSERT 0 1
melkij=> insert into foo values('F');
ОШИБКА:  неверное значение для перечисления gender: "F"
СТРОКА 1: insert into foo values('F');
                                 ^
melkij=> insert into foo values('');
ОШИБКА:  неверное значение для перечисления gender: ""
СТРОКА 1: insert into foo values('');

And an explicit check is not needed and it is easier to add values, especially if the field is used in more than one place in the database only.