Re: BUG #16868: Cannot find sqlstat error codes. - Mailing list pgsql-bugs

Here is the code with the required error and details. I tested this in all Postgres versions.

create table bipin
(id bigint , val1 character varying(1000) not null , val2 int);
insert into bipin
select i , 'test' || i , i+1 from generate_series(1,1000) dt(i);
alter table bipin add constraint pk_error_logging primary key (id);

with bipin_test as
(select 1 , 'test99' , 1
 union all
select 1001 , null , 1
union all
select 1002 , 'test99' , 1 )
insert into bipin
select * from bipin_test;

(Executing the query gives error duplicate keys but its not showing the SQLSTATE error code. This is needed when the application throws error for easy troubleshooting.
postgres=> with bipin_test as
postgres-> (select 1 , 'test99' , 1
postgres(>  union all
postgres(> select 1001 , null , 1
postgres(> union all
postgres(> select 1002 , 'test99' , 1 )
postgres-> insert into bipin
postgres-> select * from bipin_test;
ERROR:  duplicate key value violates unique constraint "pk_error_logging"
DETAIL:  Key (id)=(1) already exists.
postgres=>

Issue: 

We use aurora-data-api with postgresql. In the backend lambdas, SQLAlchemy is used as an ORM (shouldn't matter, but pointng it out anyway).
Right now, when I insert duplicate values for example, I get a root error of type `botocore.errorfactory.BadRequestException` which isn't really helpful. Our current way to deal with these is to look for some substring of the error message (i.e. if "duplicate key value" in err: ...), however it clearly isn't proper exception handling, as it forces us to code our own error mapping to some "arbirary" strings instead of a well-defined error codes map.

Postgresql does have a list of error codes: https://www.postgresql.org/docs/current/errcodes-appendix.html#ERRCODES-TABLE

How can I get that SQLSTATE code errors ? When i am manually running from psql or pgadmin i dont get the code .I only get the ERROR. Please advice for any workaround for such type of behaviour.