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

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

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

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

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

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