FW: Re: FW: Re: Shouldn;t this trigger be called?

On 9/15/19 10:46 AM, stan wrote:

Forgot to cc the list again. Have to look at settings in mutt.

Sorry forgot to cc the list

On Sun, Sep 15, 2019 at 08:42:02AM -0700, Adrian Klaver wrote:

On 9/15/19 8:33 AM, stan wrote:

I have defined this function:

CREATE FUNCTION fix_customer_types_case()

and this trigger:

CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case();

and I put a RAISE NOTICE so I can tell if the function is called. Yet when I
do a :

\copy to bring data into this table, I do not see the notice.

What is the actual command you are using?

\COPY customer(name, location, status , c_type , bill_attention , bill_addresse , bill_address_1 , bill_address_2 , bill_city , bill_state , bill_country , ship_attention , ship_addresse , ship_address_1 , ship_address_2, ship_city ,ship_state ) from '/home/stan/pm_db/live_data/ready/customer.csv' DELIMITER ',' CSV HEADER ;

and here is the function

CREATE FUNCTION fix_customer_types_case()
RETURNS trigger AS $$
BEGIN
if NEW.c_type IS NOT NULL
THEN
NEW.c_type := upper(cast( NEW.c_type AS TEXT));
END IF ;
if NEW.status IS NOT NULL
THEN
RAISE NOTICE 'Called With %', NEW.status;
NEW.status := upper(cast( NEW.status AS TEXT));
END IF ;
RAISE NOTICE 'Left With With %', NEW.status;
RAISE NOTICE 'Left With With %', NEW.c_type;
return NEW;
END;
$$
LANGUAGE PLPGSQL;

if I do an insert this function is called. However it IS NOT called for the
above copy command. How can I fix that?

I thought you said it was fixed now.

I discovered that the function was not getting defined, and fixed that. Then I
rashly posted to the list that it was fixed, as i was certain that was the
only issue. But after I reported that, I tried testing, with he results in
this email.

Works for INSERT, but does not fire on this \copy command.

More interesting data. I used vi to correct the incorrect case in the CSV file
being imported, and re-ran the \copy command. At this point in time, I did
see the messages from notice. I deleted the rows, re-edited back to the
incorrect case in the csv file, and the import ((\copy) failed.

So, my test tell me that the validity check is done BEFORE an attempt to
insert (thus firing the trigger) occurs.

What validity check?

Interesting, but not helpful for my application.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Stan,

On Sun, Sep 15, 2019 at 2:47 PM stan <stanb@panix.com> wrote:

Forgot to cc the list again. Have to look at settings in mutt.

Sorry forgot to cc the list

On Sun, Sep 15, 2019 at 08:42:02AM -0700, Adrian Klaver wrote:

On 9/15/19 8:33 AM, stan wrote:

I have defined this function:

CREATE FUNCTION fix_customer_types_case()

and this trigger:

CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR

UPDATE ON customer

FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case();

and I put a RAISE NOTICE so I can tell if the function is

called. Yet when I

do a :

\copy to bring data into this table, I do not see the notice.

What is the actual command you are using?

\COPY customer(name, location, status , c_type , bill_attention ,

bill_addresse , bill_address_1 , bill_address_2 , bill_city , bill_state ,
bill_country , ship_attention , ship_addresse , ship_address_1 ,
ship_address_2, ship_city ,ship_state ) from
'/home/stan/pm_db/live_data/ready/customer.csv' DELIMITER ',' CSV HEADER ;

and here is the function

CREATE FUNCTION fix_customer_types_case()
RETURNS trigger AS $$
BEGIN
if NEW.c_type IS NOT NULL
THEN
NEW.c_type := upper(cast( NEW.c_type AS TEXT));
END IF ;
if NEW.status IS NOT NULL
THEN
RAISE NOTICE 'Called With %', NEW.status;
NEW.status := upper(cast( NEW.status AS TEXT));
END IF ;
RAISE NOTICE 'Left With With %', NEW.status;
RAISE NOTICE 'Left With With %', NEW.c_type;
return NEW;
END;
$$
LANGUAGE PLPGSQL;

if I do an insert this function is called. However it IS NOT called

for the

above copy command. How can I fix that?

I thought you said it was fixed now.

I discovered that the function was not getting defined, and fixed that.

Then I

rashly posted to the list that it was fixed, as i was certain that was

the

only issue. But after I reported that, I tried testing, with he results

in

this email.

Works for INSERT, but does not fire on this \copy command.

More interesting data. I used vi to correct the incorrect case in the CSV
file
being imported, and re-ran the \copy command. At this point in time, I did
see the messages from notice. I deleted the rows, re-edited back to the
incorrect case in the csv file, and the import ((\copy) failed.

So, my test tell me that the validity check is done BEFORE an attempt to
insert (thus firing the trigger) occurs.

Interesting, but not helpful for my application.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Maybe you could try awk on your input:
https://thomas-cokelaer.info/blog/2018/01/awk-convert-into-lower-or-upper-cases/

<https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail&gt;
Libre
de virus. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail&gt;
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:

On 9/15/19 10:46 AM, stan wrote:

Forgot to cc the list again. Have to look at settings in mutt.

Sorry forgot to cc the list

On Sun, Sep 15, 2019 at 08:42:02AM -0700, Adrian Klaver wrote:

On 9/15/19 8:33 AM, stan wrote:

I have defined this function:

CREATE FUNCTION fix_customer_types_case()

and this trigger:

CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case();

and I put a RAISE NOTICE so I can tell if the function is called. Yet when I
do a :

\copy to bring data into this table, I do not see the notice.

What is the actual command you are using?

\COPY customer(name, location, status , c_type , bill_attention , bill_addresse , bill_address_1 , bill_address_2 , bill_city , bill_state , bill_country , ship_attention , ship_addresse , ship_address_1 , ship_address_2, ship_city ,ship_state ) from '/home/stan/pm_db/live_data/ready/customer.csv' DELIMITER ',' CSV HEADER ;

and here is the function

CREATE FUNCTION fix_customer_types_case()
RETURNS trigger AS $$
BEGIN
if NEW.c_type IS NOT NULL
THEN
NEW.c_type := upper(cast( NEW.c_type AS TEXT));
END IF ;
if NEW.status IS NOT NULL
THEN
RAISE NOTICE 'Called With %', NEW.status;
NEW.status := upper(cast( NEW.status AS TEXT));
END IF ;
RAISE NOTICE 'Left With With %', NEW.status;
RAISE NOTICE 'Left With With %', NEW.c_type;
return NEW;
END;
$$
LANGUAGE PLPGSQL;

if I do an insert this function is called. However it IS NOT called for the
above copy command. How can I fix that?

I thought you said it was fixed now.

I discovered that the function was not getting defined, and fixed that. Then I
rashly posted to the list that it was fixed, as i was certain that was the
only issue. But after I reported that, I tried testing, with he results in
this email.

Works for INSERT, but does not fire on this \copy command.

More interesting data. I used vi to correct the incorrect case in the CSV file
being imported, and re-ran the \copy command. At this point in time, I did
see the messages from notice. I deleted the rows, re-edited back to the
incorrect case in the csv file, and the import ((\copy) failed.

So, my test tell me that the validity check is done BEFORE an attempt to
insert (thus firing the trigger) occurs.

What validity check?

The check to see if it is the type enum.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

stan <stanb@panix.com> writes:

On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:

On 9/15/19 10:46 AM, stan wrote:

So, my test tell me that the validity check is done BEFORE an attempt to
insert (thus firing the trigger) occurs.

What validity check?

The check to see if it is the type enum.

Indeed, a trigger cannot fix an input-validity error, because that
will happen while trying to form the row value that would be passed
to the trigger. So I guess that when you say "the trigger doesn't
fire" you really mean "this other error is raised first".

However, I still don't understand your claim that it works the
way you wanted in an INSERT statement. The enum input function
is going to complain in either context.

Generally you need to fix issues like this before trying to
insert the data into your table. You might try preprocessing
the data file before feeding it to COPY. Another way is to
copy into a temporary table that has very lax column data types
(all "text", perhaps) and then transform the data using
INSERT ... SELECT from the temp table to the final storage table.

regards, tom lane

On 9/15/19 6:04 PM, stan wrote:

On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:

On 9/15/19 10:46 AM, stan wrote:

Forgot to cc the list again. Have to look at settings in mutt.

What validity check?

The check to see if it is the type enum.

This would get solved a lot quicker if full information was provided:

1) Schema of the table.
Including associated triggers

2) The actual check code.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Sep 16, 2019 at 12:12:34AM -0400, Tom Lane wrote:

stan <stanb@panix.com> writes:

On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:

On 9/15/19 10:46 AM, stan wrote:

So, my test tell me that the validity check is done BEFORE an attempt to
insert (thus firing the trigger) occurs.

What validity check?

The check to see if it is the type enum.

Indeed, a trigger cannot fix an input-validity error, because that
will happen while trying to form the row value that would be passed
to the trigger. So I guess that when you say "the trigger doesn't
fire" you really mean "this other error is raised first".

However, I still don't understand your claim that it works the
way you wanted in an INSERT statement. The enum input function
is going to complain in either context.

Generally you need to fix issues like this before trying to
insert the data into your table. You might try preprocessing
the data file before feeding it to COPY. Another way is to
copy into a temporary table that has very lax column data types
(all "text", perhaps) and then transform the data using
INSERT ... SELECT from the temp table to the final storage table.

regards, tom lane

Thanks for educating me. I thought I had tested and seen that this worked on
an INSERT, but once you told me it does not, I re tested to convince myself
that my test was invalid. let me show you what I was trying to do:

CREATE FUNCTION fix_customer_types_case()
RETURNS trigger AS $$
BEGIN
if NEW.c_type IS NOT NULL
THEN
NEW.c_type := upper(cast( NEW.c_type AS TEXT));
END IF ;
if NEW.status IS NOT NULL
THEN
/*
RAISE NOTICE 'Called With %', NEW.status;
*/
NEW.status := upper(cast( NEW.status AS TEXT));
END IF ;
/*
RAISE NOTICE 'Left With With NEW.status %', NEW.status;
RAISE NOTICE 'Left With With NEW.c_type %', NEW.c_type;
*/
return NEW;
END;
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case();

all of this is to deal with columns defined as this user defined type.

CREATE TYPE activity_status AS ENUM ('ACTIVE' ,
'INACTIVE');

Can you think of a better way to make the system "human data entry friendly"?

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

On 9/16/19 11:52 AM, stan wrote:

On Mon, Sep 16, 2019 at 12:12:34AM -0400, Tom Lane wrote:

stan <stanb@panix.com> writes:

On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:

On 9/15/19 10:46 AM, stan wrote:

So, my test tell me that the validity check is done BEFORE an attempt to
insert (thus firing the trigger) occurs.

What validity check?

The check to see if it is the type enum.

Indeed, a trigger cannot fix an input-validity error, because that
will happen while trying to form the row value that would be passed
to the trigger. So I guess that when you say "the trigger doesn't
fire" you really mean "this other error is raised first".

However, I still don't understand your claim that it works the
way you wanted in an INSERT statement. The enum input function
is going to complain in either context.

Generally you need to fix issues like this before trying to
insert the data into your table. You might try preprocessing
the data file before feeding it to COPY. Another way is to
copy into a temporary table that has very lax column data types
(all "text", perhaps) and then transform the data using
INSERT ... SELECT from the temp table to the final storage table.

regards, tom lane

Thanks for educating me. I thought I had tested and seen that this worked on
an INSERT, but once you told me it does not, I re tested to convince myself
that my test was invalid. let me show you what I was trying to do:

So was it invalid?

CREATE FUNCTION fix_customer_types_case()
RETURNS trigger AS $$
BEGIN
if NEW.c_type IS NOT NULL
THEN
NEW.c_type := upper(cast( NEW.c_type AS TEXT));
END IF ;
if NEW.status IS NOT NULL
THEN
/*
RAISE NOTICE 'Called With %', NEW.status;
*/
NEW.status := upper(cast( NEW.status AS TEXT));
END IF ;
/*
RAISE NOTICE 'Left With With NEW.status %', NEW.status;
RAISE NOTICE 'Left With With NEW.c_type %', NEW.c_type;
*/
return NEW;
END;
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case();

all of this is to deal with columns defined as this user defined type.

CREATE TYPE activity_status AS ENUM ('ACTIVE' ,
'INACTIVE');

Can you think of a better way to make the system "human data entry friendly"?

--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Sep 16, 2019 at 12:44:49PM -0700, Adrian Klaver wrote:

On 9/16/19 11:53 AM, stan wrote:

On Sun, Sep 15, 2019 at 09:16:35PM -0700, Adrian Klaver wrote:

On 9/15/19 6:04 PM, stan wrote:

On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:

On 9/15/19 10:46 AM, stan wrote:

Forgot to cc the list again. Have to look at settings in mutt.

What validity check?

The check to see if it is the type enum.

This would get solved a lot quicker if full information was provided:

1) Schema of the table.
Including associated triggers

2) The actual check code.

OK, please let me know if what I put in my reply to Tom Lane is not sufficient.

It was not sufficient, you did not include the table schema or the check
code.

OK, understood here is the table:

/* Contains one record for each customer */

CREATE TABLE customer (
customer_key integer DEFAULT nextval('customer_key_serial')
PRIMARY KEY ,
cust_no smallint NOT NULL UNIQUE ,
name varchar UNIQUE ,
c_type customer_type ,
location varchar ,
bill_address_1 varchar ,
bill_address_2 varchar ,
bill_city varchar ,
bill_state varchar(2) ,
bill_zip us_postal_code NOT NULL DEFAULT '00000',
bill_country varchar ,
bill_attention varchar ,
bill_addresse varchar ,
ship_address_1 varchar ,
ship_address_2 varchar ,
ship_addresse varchar ,
ship_attention varchar ,
ship_city varchar ,
ship_state varchar(2) ,
ship_zip us_postal_code NOT NULL DEFAULT '00000',
office_phone_area_code numeric(3),
office_phone_exchange numeric(3),
office_phone_number numeric(4),
office_phone_extension numeric(4),
cell_phone_area_code numeric(3),
cell_phone_exchange numeric(3),
cell_phone_number numeric(4),
ship_phone_area_code numeric(3),
ship_phone_exchange numeric(3),
ship_phone_number numeric(4),
ship_phone_extension numeric(4),
fax_phone_area_code numeric(3),
fax_phone_exchange numeric(3),
fax_phone_number numeric(4),
status activity_status NOT NULL DEFAULT 'ACTIVE',
modtime timestamptz NOT NULL DEFAULT current_timestamp
);

I am not certain what you mean by the check. As you can see, there is nor
specific check clause. I was referring to the built in check of data being
entered versus the legal values for the user defined type. Make sense?

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

On 9/16/19 12:55 PM, stan wrote:

On Mon, Sep 16, 2019 at 12:44:49PM -0700, Adrian Klaver wrote:

On 9/16/19 11:53 AM, stan wrote:

On Sun, Sep 15, 2019 at 09:16:35PM -0700, Adrian Klaver wrote:

On 9/15/19 6:04 PM, stan wrote:

On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:

On 9/15/19 10:46 AM, stan wrote:

Forgot to cc the list again. Have to look at settings in mutt.

What validity check?

The check to see if it is the type enum.

This would get solved a lot quicker if full information was provided:

1) Schema of the table.
Including associated triggers

2) The actual check code.

OK, please let me know if what I put in my reply to Tom Lane is not sufficient.

It was not sufficient, you did not include the table schema or the check
code.

OK, understood here is the table:

/* Contains one record for each customer */

status activity_status NOT NULL DEFAULT 'ACTIVE',
modtime timestamptz NOT NULL DEFAULT current_timestamp
);

I am not certain what you mean by the check. As you can see, there is nor
specific check clause. I was referring to the built in check of data being
entered versus the legal values for the user defined type. Make sense?

To be clear you are seeing an error because a value of say 'active' is
being rejected before your trigger has a chance to upper case it, correct?

Also this happens whether you use \copy or manually INSERT the values?

--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Sep 16, 2019 at 03:19:27PM -0700, Adrian Klaver wrote:

On 9/16/19 12:55 PM, stan wrote:

On Mon, Sep 16, 2019 at 12:44:49PM -0700, Adrian Klaver wrote:

On 9/16/19 11:53 AM, stan wrote:

On Sun, Sep 15, 2019 at 09:16:35PM -0700, Adrian Klaver wrote:

On 9/15/19 6:04 PM, stan wrote:

On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:

On 9/15/19 10:46 AM, stan wrote:

Forgot to cc the list again. Have to look at settings in mutt.

What validity check?

The check to see if it is the type enum.

This would get solved a lot quicker if full information was provided:

1) Schema of the table.
Including associated triggers

2) The actual check code.

OK, please let me know if what I put in my reply to Tom Lane is not sufficient.

It was not sufficient, you did not include the table schema or the check
code.

OK, understood here is the table:

/* Contains one record for each customer */

status activity_status NOT NULL DEFAULT 'ACTIVE',
modtime timestamptz NOT NULL DEFAULT current_timestamp
);

I am not certain what you mean by the check. As you can see, there is nor
specific check clause. I was referring to the built in check of data being
entered versus the legal values for the user defined type. Make sense?

To be clear you are seeing an error because a value of say 'active' is being
rejected before your trigger has a chance to upper case it, correct?

Also this happens whether you use \copy or manually INSERT the values?

That is correct. Sorry this was not clear from the beginning.

Any suggestions, including changing the design here, are welcome.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

On 9/17/19 2:31 AM, stan wrote:

I am not certain what you mean by the check. As you can see, there is nor
specific check clause. I was referring to the built in check of data being
entered versus the legal values for the user defined type. Make sense?

To be clear you are seeing an error because a value of say 'active' is being
rejected before your trigger has a chance to upper case it, correct?

Also this happens whether you use \copy or manually INSERT the values?

That is correct. Sorry this was not clear from the beginning.

Any suggestions, including changing the design here, are welcome.

Suggestions:

1) Per Tom's post clean the data before it hits the database.

2) Enter the data directly into the database using something that forces
the user to enter only the correct ENUM values.

3) If the status field is only ever going to be ACTIVE/INACTIVE change
it to a BOOLEAN field active_status and be done with the ENUM dance.

4) If status may ever be more then ACTIVE/ACTIVE then change it to
varchar and use the trigger to set case(if still important) and/or
verify correct entries.

--
Adrian Klaver
adrian.klaver@aklaver.com

You can also look at citext type to avoid the casting.

customer_key integer DEFAULT
nextval('customer_key_serial') PRIMARY KEY ,
cust_no smallint NOT NULL UNIQUE ,
name varchar UNIQUE ,

Why do you have a surrogate primary key generated by a sequence when you
have a natural key of either cust_no or name? Why not just declare the
customer number to be the PK? Where does customer number come from anyway?
Using smallint seems potentially short-sighted on potential future growth,
but changing the type later should be minimal work as long as you don't
have this customer_number denormalized many places, or use it as the FKey
after dropping customer_key surrogate key.

On Thu, Sep 19, 2019 at 03:54:40PM -0600, Michael Lewis wrote:

You can also look at citext type to avoid the casting.

Oh, that looks really useful I think I will go back and use that type quite
a bit.

Thanks for pointing it out to me.

customer_key integer DEFAULT
nextval('customer_key_serial') PRIMARY KEY ,
cust_no smallint NOT NULL UNIQUE ,
name varchar UNIQUE ,

Why do you have a surrogate primary key generated by a sequence when you
have a natural key of either cust_no or name? Why not just declare the
customer number to be the PK?

At the moment, the customer (who is a small startup) really does not have a
customer number. It is really a place holder at the moment, with the
sequence being the "real" key. For all I know, the customer number may be
alphanumeric. in the final implementation.

Where does customer number come from anyway?
Using smallint seems potentially short-sighted on potential future growth,
but changing the type later should be minimal work as long as you don't
have this customer_number denormalized many places, or use it as the FKey
after dropping customer_key surrogate key.

Thanks for your suggestion.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

I see that you've already been pointed at citext, but I don't think a CHECK
constraint has been mentioned. In case it hasn't, what about something like
this?

ADD CONSTRAINT check_activity_status
CHECK (activity_status = 'ACTIVE' OR activity_status = 'INACTIVE');

I'm kind of allergic to ENUM...maybe that's just me. But since you're
considering it, maybe it's the perfect time to consider all of your
options. Such as a linked lookup table of defined allowed values (feels
silly with two values), a domain (not entirely fit to purpose), or the
CHECK constraint above. And, yeah, if it's only ever ACTIVE or INACTIVE,
I'd normally make a Boolean named something like active, as Adrian Klaver
mentioned. That's easy to reason about, and it makes it unambiguous that
there are two and only two possible states..

On Thu, Sep 19, 2019 at 03:54:40PM -0600, Michael Lewis wrote:

You can also look at citext type to avoid the casting.

customer_key integer DEFAULT
nextval('customer_key_serial') PRIMARY KEY ,
cust_no smallint NOT NULL UNIQUE ,
name varchar UNIQUE ,

I am confysed. I am running version 11 which is current I beleive, but when
I try to use this type, I get:

ERROR: type "citext" does not exist
LINE 8: unit citext UNIQUE NOT NULL ,

Do I somehow need to enable this type?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

On Fri, Sep 20, 2019 at 08:52:00PM +1000, Morris de Oryx wrote:

I see that you've already been pointed at citext, but I don't think a CHECK
constraint has been mentioned. In case it hasn't, what about something like
this?

ADD CONSTRAINT check_activity_status
CHECK (activity_status = 'ACTIVE' OR activity_status = 'INACTIVE');

I'm kind of allergic to ENUM...maybe that's just me. But since you're
considering it, maybe it's the perfect time to consider all of your
options. Such as a linked lookup table of defined allowed values (feels
silly with two values), a domain (not entirely fit to purpose), or the
CHECK constraint above. And, yeah, if it's only ever ACTIVE or INACTIVE,
I'd normally make a Boolean named something like active, as Adrian Klaver
mentioned. That's easy to reason about, and it makes it unambiguous that
there are two and only two possible states..

Thanks you.

I actually have a number of these cases, and I sullied the simplest one,
which just has 2 values. I guess my "C: background is showing here.

I do have some similar situations where I did use a table of allowed
conditions. I am thinking citext may be the best solution here.

I am having an issue getting it to work, though. I don't have to do
anything special to enable this type, do I?

What I am really trying to do is "human proof" this input :-)

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

citext is an extension, so you have to install it:

CREATE EXTENSION citext;

That's the simplest form. you can install it into a specific schema, test
for existence, etc. Check out the CREATE EXTENSION docs here:

https://www.postgresql.org/docs/current/sql-createextension.html