Handling case variatiions on a user defined type?

[stan]

I have several enumerated types that look like:

CREATE TYPE confidence_level AS ENUM ('HIGH' ,
'MEDIUM' ,
'LOW' ,
 'WAG');


I have someone creating data for this project in spreadsheets, and then am
importing the data using \copy.

Problem is the data in the spreadsheets is entered in mixed case.

Is there a way that I can use the type itself, to enforce something like a
to_upper(). Or is there a better way to  "human proof" this?

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

Re: Handling case variatiions on a user defined type?

[Olivier Gautherot]

El dom., 15 de septiembre de 2019 09:37, stan  escribió:

> I have several enumerated types that look like:
>
> CREATE TYPE confidence_level AS ENUM ('HIGH' ,
> 'MEDIUM' ,
> 'LOW' ,
>  'WAG');
>
>
> I have someone creating data for this project in spreadsheets, and then am
> importing the data using \copy.
>
> Problem is the data in the spreadsheets is entered in mixed case.
>
> Is there a way that I can use the type itself, to enforce something like a
> to_upper(). Or is there a better way to  "human proof" this?
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>

What about a trigger on INSERT, which would enforce the case?

>

Re: Handling case variatiions on a user defined type?

[stan]

On Sun, Sep 15, 2019 at 09:58:45AM -0300, Olivier Gautherot wrote:
> El dom., 15 de septiembre de 2019 09:37, stan  escribi??:
> 
> > I have several enumerated types that look like:
> >
> > CREATE TYPE confidence_level AS ENUM ('HIGH' ,
> > 'MEDIUM' ,
> > 'LOW' ,
> >  'WAG');
> >
> >
> > I have someone creating data for this project in spreadsheets, and then am
> > importing the data using \copy.
> >
> > Problem is the data in the spreadsheets is entered in mixed case.
> >
> > Is there a way that I can use the type itself, to enforce something like a
> > to_upper(). Or is there a better way to  "human proof" this?
> >
> > --
> > "They that would give up essential liberty for temporary safety deserve
> > neither liberty nor safety."
> > -- Benjamin Franklin
> >
> 
> What about a trigger on INSERT, which would enforce the case?

That sounds like a good idea, thanks.


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

Shouldn;t this trigger be called?

[stan]

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 cna tell if the function is called. Yet when I
do a :

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

Shouldn't this fucntion be aclled for that?



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

Re: Shouldn;t this trigger be called?

[stan]

On Sun, Sep 15, 2019 at 11:33:09AM -0400, 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 cna tell if the function is called. Yet when I
> do a :
> 
> \copy to bring data into this table, I do not see the notice.
> 
> Shouldn't this fucntion be aclled for that?

Sorry, found my mistake.

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

Re: Shouldn;t this trigger be called?

[Adrian Klaver]

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 cna 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?

For more info see:

https://www.postgresql.org/docs/11/sql-copy.html

which is the command that is being used by \copy.




Shouldn't this fucntion be aclled for that?






--
Adrian Klaver
adrian.kla...@aklaver.com

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

[stan]

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?

This line from the page you referenced implies this should work, but i must be
doing something wrong:

COPY FROM will invoke any triggers and check constraints on the destination
table. However, it will not invoke rules.


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

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

[Adrian Klaver]

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


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.



This line from the page you referenced implies this should work, but i must be
doing something wrong:

COPY FROM will invoke any triggers and check constraints on the destination
table. However, it will not invoke rules.





--
Adrian Klaver
adrian.kla...@aklaver.com

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

[stan]

On Sun, Sep 15, 2019 at 08:59:43AM -0700, Adrian Klaver wrote:
> On 9/15/19 8:55 AM, stan wrote:
> > 
> > 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.

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

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

[stan]

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

PostgreSQL License

[Ashkar Dev]

*Can anyone explain the PostgreSQL License to me?*
*Can I create a database with it and sell the database also preventing the
buyer from reselling it?*
*Can I change in the logos of the PostgreSQL system and its name?*

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

[Adrian Klaver]

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.kla...@aklaver.com

Re: Variable constants ?

[Peter J. Holzer]

On 2019-08-15 16:56:57 -0400, stan wrote:
> bossiness constants

On 2019-09-02 13:31:14 -0400, stan wrote:
> bossiness plan
> bossiness model

On 2019-09-13 05:57:33 -0400, stan wrote:
> bossiness work

I'm starting to think that this is not a typo :-)

SCNR,
hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

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

[stan]

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

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

[Olivier Gautherot]

Hi Stan,

On Sun, Sep 15, 2019 at 2:47 PM 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.
>
> 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/


Libre
de virus. www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: kind of a bag of attributes in a DB . . .

[Chris Travers]

On Sat, Sep 14, 2019 at 5:11 PM Albretch Mueller  wrote:

>  just download a bunch of json info files from youtube data Feeds
>
>  Actually, does postgresql has a json Driver of import feature?
>

Sort of  There  are a bunch of features around JSON and JSONB data
types which could be useful.

>
>  the metadata contained in json files would require more than one
> small databases, but such an import feature should be trivial
>

It is not at all trivial for a bunch of reasons inherent to the JSON
specification.  How to handle duplicate keys, for example.

However writing an import for JSON objects into a particular database is
indeed trivial.

>
>  C
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

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

[Tom Lane]

stan  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

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

[Adrian Klaver]

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.kla...@aklaver.com