Re: BUG #16868: Cannot find sqlstat error codes.

Thank you for your response.

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.

On Tue, Feb 16, 2021 at 6:11 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

>
> On Monday, February 15, 2021, PG Bug reporting form <
> noreply(at)postgresql(dot)org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 16868
>> Logged by: bipsy Nair
>> Email address: nbipin29(at)gmail(dot)com
>> PostgreSQL version: 12.4
>> Operating system: RDS and EC2
>> Description:
>>
>> Our developer needs a Error code generated so that they can trap in the
>> applications incase of any errors.
>> Please advice and provide a workaround.
>>
>
> Might want to provide a full,example of the code involved in executing the
> SQL and processing the errors. This is all very db client-specific.
>
> David J.
>