Re: Check constraint failure messages

zim <zimtigerclaw@xxxxxxxxxxx> · Sun, 3 Oct 2021 23:14:09 +0200



    An error message that includes the table name and column name would
    also be helpful for non-bulk inserts: a single insert where multiple
    columns of a table have the same domain type. The problem gets worse
    when there are inserts into multiple tables that have the same
    domain types (multiple sql inserts in a statement).

    
    Am 06.04.21 um 22:58 schrieb Ron:

    
      On 4/6/21 3:50 PM, Miles Elam wrote:

      
          On Tue, Apr 6, 2021 at 1:03 PM Ron <ronljohnsonjr@xxxxxxxxx>
            wrote:

          
               On 4/6/21 2:40 PM, Miles Elam wrote:

                
                  I've got a domain that validates email
                    addresses. When inserting a bunch of entries I
                    simply get the error message
                    

                        ERROR:  value for domain po.email violates check constraint "email_check"
SQL state: 23514

                        
                    When inserting 1000+ entries in a batch,
                      finding the exact entry with the problem is
                      noticeably harder than with other error types. For
                      example when a column should be a uuid but you
                      pass in 'Mary had a little lamb', the error
                      message tells you what the invalid value is as
                      well as the column name you're trying to put it
                      into.
                    

                    Are there any quick hacks floating around out
                      there to solve or at least mitigate this?
                  
                
                Is it a deferred constraint?

              
            Plain ole domain CHECK constraint.
            

              CREATE DOMAIN po.email AS varchar
                CHECK (VALUE IS NULL OR (po.length_in(VALUE, 1,
                254) AND NOT po.email_expanded(VALUE) IS NULL));
            
          
            where "po" is another schema, po.length_in(...) is an
              IMMUTABLE range check, and po.email_expanded(...) is a
              function returning a record. Same behavior happens if I
              remove the functions and define the check constraint in
              place. The only info returned in a bulk insert is the name
              of the violated check constraint, aka email_check.
          
        
      The blunt force answer is to not use bulk inserts.  Try COPY; it's
      good at saying which record throws an error.

      
      -- 

        Angular momentum makes the world go 'round.