INSERT или UPDATE таблицы с первичным ключом SERIAL

Question

Простой вопрос,

Как я могу UPSERT в таблицах PostgreSQL, которые имеют первичные ключи SERIAL (также известные как автоинкремент)?

Я не мог найти действительно правдоподобный источник, поэтому я спрашиваю здесь.

Это то, что я получил до сих пор, но это не работает.

INSERT INTO public.friendship (Username_1, Username_2, Status) 
VALUES ("User1", "User2", "Accepted") 
ON CONFLICT (Username_1, Username_2) 
DO UPDATE SET (Status) = ("Accepted")   
WHERE Username_1 = "User1" AND Username_2 = "User2";

Полная схема (ER-диаграмма). Таблица - это "Friendship", но UMLStar экспортирует странный SQL с "public.Friendship", как имена таблиц. Но это не имеет значения, это работает с другими случаями (я успешно зарегистрировался).

Команда \ d, Список отношений

 Schema |              Name               |   Type   |  Owner
--------+---------------------------------+----------+----------
 public | activitydata                    | table    | postgres
 public | activitydata_activitydataid_seq | sequence | postgres
 public | chatroom                        | table    | postgres
 public | chatroom_chatroomid_seq         | sequence | postgres
 public | country                         | table    | postgres
 public | friendship                      | table    | postgres
 public | friendship_friendshipid_seq     | sequence | postgres
 public | message                         | table    | postgres
 public | message_messageid_seq           | sequence | postgres
 public | participates                    | table    | postgres
 public | participates_participatesid_seq | sequence | postgres
 public | user                            | table    | postgres
(12 rows)

messenger=# \d Дружба

Table "public.friendship"
    Column    |  Type   |                             Modifiers
--------------+---------+-------------------------------------------------------------------
 friendshipid | integer | not null default nextval('friendship_friendshipid_seq'::regclass)
 username_1   | text    | not null
 username_2   | text    | not null
 status       | text    | not null
Indexes:
    "friendship_pkey" PRIMARY KEY, btree (friendshipid)
    "friendship_username_1_idx" btree (username_1)
    "friendship_username_2_idx" btree (username_2)

Выполненная команда:

messenger=# INSERT INTO public.friendship (Username_1, Username_2, Status)
messenger-# VALUES ('User1', 'User2', 'Accepted')
messenger-#  ON CONFLICT (Username_1, Username_2)
messenger-#  DO UPDATE SET (Status) = ('Accepted')
messenger-#  WHERE Username_1 = 'User1' AND Username_2 = 'User2';
ERROR:  column reference "username_1" is ambiguous
LINE 5:  WHERE Username_1 = 'User1' AND Username_2 = 'User2';

Answer 1

Table "public.friendship"
    Column    |  Type   |                             Modifiers
--------------+---------+-------------------------------------------------------------------
 friendshipid | integer | not null default nextval('friendship_friendshipid_seq'::regclass)
 username_1   | text    | not null
 username_2   | text    | not null
 status       | text    | not null
Indexes:
    "friendship_pkey" PRIMARY KEY, btree (friendshipid)
    "friendship_username_1_idx" btree (username_1)
    "friendship_username_2_idx" btree (username_2)

Это не показывает уникальный ключ на (username_1, username_2)

Что вы должны сделать, это

BEGIN;
  DROP INDEX friendship_username_1_idx,
  CREATE UNIQUE INDEX ON foo(username_1, username_2);
COMMIT;

Если вы никогда не запрашиваете username_2 без запроса username_1 тогда вы должны бросить friendship_username_2_idx и пусть один составной УНИКАЛЬНЫЙ индекс работает на вас.

Для справки мы хотели \d Friendship потому что если бы у вас был уникальный индекс, он бы показал...

      Table "public.foo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | integer | 
Indexes:
    "foo_a_b_idx" UNIQUE, btree (a, b)

Без чего-то там, как вы можете получить конфликт?

Answer 2

Извините, если я упустил сложность вопроса, но это кажется проще:

      ALTER TABLE public.friendship 
add column id serial;