BUG #17188: Multiple rows are present with same values on a unique column - Mailing list pgsql-bugs
| From | PG Bug reporting form |
|---|---|
| Subject | BUG #17188: Multiple rows are present with same values on a unique column |
| Date | |
| Msg-id | 17188-93034073366358b6@postgresql.org Whole thread Raw |
| Responses |
Re: BUG #17188: Multiple rows are present with same values on a unique column
|
| List | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17188
Logged by: DBA TEAM
Email address: devops@getfareye.com
PostgreSQL version: 9.6.22
Operating system: Ubuntu 20.04.2 LTS
Description:
We are having a table Users in which multiple columns named emp_code and
company_id have a unique constraint applied to them.But for multiple rows
there are values which are equal for both of the columns.
Users table definition
Table "public.users"
Column | Type | Collation |
Nullable | Default | Storage | Stats target |
Description
-----------------------------+-----------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
id | integer | | not
null | nextval('users_id_seq'::regclass) | plain | |
login | character varying(50) | |
| | extended | |
password | character varying(100) | |
| | extended | |
first_name | character varying(50) | |
| NULL::character varying | extended | |
last_name | character varying(50) | |
| NULL::character varying | extended | |
email | text | |
| NULL::character varying | extended | |
activated | boolean | |
| false | plain | |
lang_key | character varying(5) | |
| | extended | |
activation_key | character varying(20) | |
| | extended | |
created_by | character varying(50) | | not
null | 'system'::character varying | extended | |
created_date | timestamp without time zone | | not
null | now() | plain | |
last_modified_by | character varying(50) | |
| | extended | |
last_modified_date | timestamp without time zone | |
| | plain | |
emp_code | character varying(64) | |
| | extended | |
mobile | character varying(16) | |
| NULL::character varying | extended | |
company_id | bigint | |
| 1 | plain | |
user_type_id | bigint | |
| 1 | plain | |
salt | character varying(255) | |
| | extended | |
city_id | bigint | |
| | plain | |
hub_id | bigint | |
| | plain | |
last_login_time | timestamp without time zone | |
| | plain | |
merchants | character varying(255) | |
| | extended | |
is_auto_push_mail_activated | boolean | |
| false | plain | |
wrong_attempts | integer | |
| 0 | plain | |
locked | boolean | |
| false | plain | |
locked_date_time | timestamp without time zone | |
| | plain | |
is_logged_in | boolean | |
| false | plain | |
service_provider_code | character varying(64) | |
| NULL::character varying | extended | |
profile_image | character varying(256) | |
| | extended | |
icdr | boolean | |
| false | plain | |
routing_json | text | |
| | extended | |
auth_user_id | integer | |
| | plain | |
user_id | character varying(255) | |
| | extended | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_company_id_emp_code_key" UNIQUE CONSTRAINT, btree (company_id,
emp_code)
"users_company_id_user_type_id_city_id_hub_id_activation_key_cre" btree
(company_id, user_type_id, city_id, hub_id, activation_key, created_date,
login)
"users_login_key" UNIQUE CONSTRAINT, btree (login)
Here is the sample data
select id,company_id,emp_code from users where
emp_code='4742_bpl-14_bpl';
id | company_id | emp_code
------+------------+-----------------
5065 | 8 | 4742_bpl-14_bpl
1594 | 8 | 4742_bpl-14_bpl
pgsql-bugs by date: