Re: [BUGS] BUG #14526: no unique or exclusion constraint matching theON CONFLICT

Here it goes:

                                                                   Table "public.accounts"
             Column              |            Type             |                       Modifiers
|Storage  | Stats target | Description 

----------------------------------+-----------------------------+-------------------------------------------------------+----------+--------------+-------------
id                               | integer                     | not null default nextval('accounts_id_seq'::regclass)
|plain    |              | 
type                             | character varying           |
|extended |              | 
identifier                       | character varying           |
|extended |              | 
person_id                        | integer                     |
|plain    |              | 
business_id                      | integer                     |
|plain    |              | 
Indexes:
   "accounts_pkey" PRIMARY KEY, btree (id)
   "index_accounts_on_type_and_identifier" UNIQUE, btree (type, identifier)
   "uniq_bank_accounts" UNIQUE, btree (type, identifier) WHERE type::text = 'BankAccount'::text
   "uniq_business_accounts" UNIQUE, btree (type, business_id) WHERE type::text = 'BusinessAccount'::text
   "uniq_person_accounts" UNIQUE, btree (person_id) WHERE type::text = 'PersonAccount'::text
   "index_accounts_on_business_id" btree (business_id)
   "index_accounts_on_person_id" btree (person_id)
Foreign-key constraints:
   "fk_rails_156241f05a" FOREIGN KEY (business_id) REFERENCES businesses(id)
   "fk_rails_777d10a224" FOREIGN KEY (person_id) REFERENCES persons(id)

> On 7 Feb 2017, at 09:03, Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Thu, Feb 2, 2017 at 6:07 AM,  <tiago.babo@gmail.com> wrote:
>> INSERT INTO accounts (type, person_id) VALUES ('PersonAccount', 1) ON
>> CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET
>> updated_at = EXCLUDED.updated_at RETURNING *
>>
>> I also have an unique INDEX:
>>
>> CREATE UNIQUE INDEX uniq_person_accounts ON accounts USING btree (type,
>> person_id) WHERE ((type)::text = 'PersonAccount'::text);
>
> Can you show the table definition? From psql, "\d+ accounts"
>
>
> --
> Peter Geoghegan



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs