Skip site navigation (1) Skip section navigation (2)

Re: How to trap invalid enum input exception?

From: Leon Starr <leon_starr(at)modelint(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to trap invalid enum input exception?
Date: 2010-08-16 03:53:50
Message-ID: A3E047C6-7820-432B-91B5-54D77EE1DFF1@modelint.com (view raw or flat)
Thread:
Lists: pgsql-novice
No success!

Actually, big thanks, Tom, for the psql verbosity option - that's actually a big help, however....

I have tried catching following with no success:
	INVALID_TEXT_REPRESENTATION
	SQLSTATE '22P02'
	SQLSTATE '22000'
	DATA_EXCEPTION
	OTHERS

(lest we think I am completely incompetent ;), I have had success
catching all manner of FOREIGN_KEY_VIOLATION, UNIQUE_VIOLATION, CHECK_VIOLATION, NO_DATA_FOUND, etc. in other functions.   So something is very different about this particular exception!)

It seems to me that it is not a normal exception and evades the exception clause.  Any thoughts on where to go from here?

(I suppose I could just live with the system generated error or do some sort of check constraint and jettison enums altogether)

- Leon



On Aug 15, 2010, at 8:12 PM, Tom Lane wrote:

> Leon Starr <leon_starr(at)modelint(dot)com> writes:
>> db=# select test_input('blue');
>> ERROR:  invalid input value for enum side: "blue"
> 
>> 1) What is the actual name of the condition (and, yes, I looked through appendix A trying to figure it out) for invalid enum input?
> 
> [ looks at code... ] Try INVALID_TEXT_REPRESENTATION.
> 
>> 2) Is there a way to retrieve the error code so that I don't have to guess at the condition name?  (I've been playing this game a lot with other exceptions and I don't understand WHY the condition or code isn't provided when an untrapped error occurs)  Clearly, I'm missing something!
> 
> In psql, you can do this:
> 
> regression=# CREATE TYPE side AS ENUM ('right', 'left');
> CREATE TYPE
> regression=# select 'blue'::side;
> ERROR:  invalid input value for enum side: "blue"
> LINE 1: select 'blue'::side;
>               ^
> regression=# \set VERBOSITY verbose
> regression=# select 'blue'::side;
> ERROR:  22P02: invalid input value for enum side: "blue"
> LINE 1: select 'blue'::side;
>               ^
> LOCATION:  enum_in, enum.c:56
> 
> and after that, you can either look up the SQLSTATE 22P02 in appendix A,
> or consult the source code in enum_in().  If you're not using psql, the
> same information should be available through the client API you're using
> --- feel free to complain to its authors if not.
> 
> 			regards, tom lane
> 
> -- 
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

-----------------------------------------------------------------------------------------
Model Integration, LLC
Complex Application Requirements Analysis and Modeling
Expert UML/MDA Development, Training and Support

Leon Starr
Analyst / Model Engineer

+1 415 863 8649 office
+1 415 215 9086 cell

leon_starr(at)modelint(dot)com
www.modelint.com

www.linkedin.com/in/modelint

912 Cole Street, Suite 161
San Francisco, CA 94117
-----------------------------------------------------------------------------------------




In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-08-16 03:58:46
Subject: Re: How to trap invalid enum input exception?
Previous:From: Tom LaneDate: 2010-08-16 03:12:02
Subject: Re: How to trap invalid enum input exception?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group