BUG #16868: Cannot find sqlstat error codes.

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.

Thanks for the detailed explanation. I did the following test and this is the issue in see in Postgres.

Not able to get the SQLSTATE code for Postgres flavours. Any config changes needed at client or db side.

1. Test on Serverless Postgres with parameter  log_error_verbosity=verbose
SELECT name, setting FROM pg_settings WHERE name LIKE 'log_error_verbosity';
drop  table bipin;
create table bipin (id int);
insert into bipin values(1);
alter table bipin add constraint pk_error_logging primary key (id);
  insert into bipin values(1);(No SQL State captured).  ERROR: duplicate key value violates unique constraint "pk_error_logging" Detail: Key (id)=(1) already exists.

2. Test on Serverless Aurora-Mysql.    same code. You see its captured.
Database error code: 1062. Message: Duplicate entry '1' for key 'PRIMARY'

3.Test on Mysql (non-serverless).
mysql> insert into bipin value(1); same code. You see its captured.  
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

4. Test on Postgres with parameter ==> log_error_verbosity=verbose
SELECT name, setting FROM pg_settings WHERE name LIKE 'log_error_verbosity';

postgres=> create table bipin (id int);
ERROR:  relation "bipin" already exists
postgres=> insert into bipin values(1);
ERROR:  duplicate key value violates unique constraint "pk_error_logging"    ==> NO SQL STATE captured.
DETAIL:  Key (id)=(1) already exists.

But when i run the following it shows.
postgres=> \errverbose
ERROR:  23505: duplicate key value violates unique constraint "pk_error_logging"
DETAIL:  Key (id)=(1) already exists.
SCHEMA NAME:  public
TABLE NAME:  bipin
CONSTRAINT NAME:  pk_error_logging
LOCATION:  _bt_check_unique, nbtinsert.c:573

So this is the exact issues faced by developer :
rdsdataservice client to make "execute_statement()" call, but when we insert duplicate values for example, boto3 client does not return valid error message with PostgreSQL Error Codes.
I tried setting the Boto3 logs to full logging, and running the same query showed this in the logs:

> 2021-02-16 15:18:11,091 botocore.parsers
[DEBUG] Response body:
b'{"message":"ERROR: duplicate key value violates unique constraint \\"site_site_name_key\\"\\n  Detail: Key (site_name)=(f) already exists."}'

> 2021-02-16 15:18:11,096 botocore.parsers
[DEBUG] Response headers:
{'x-amzn-RequestId': 'd0d366f8-0291-492e-aadb-58d4b1e48dfa', 'x-amzn-ErrorType': 'BadRequestException:XXXrdsdataservice/', 'Content-Type': 'application/json', 'Content-Length': '137', 'Date': 'Tue, 16 Feb 2021 20:18:10 GMT', 'Connection': 'close'}

So we need a workaround wherein it can show the SQLSTATE code and want to know if its limitation at Postgres DB level.

thank you ,

Bipin

As its not showing the errorcode , thats the reason boto3 is not able to capture the error code.

Please advice if you have any thoughts on this as its confusing and developer thinks its some limitations on RDS side.