Postgresql

Postgresql:使用具有唯一約束的相同 ID 將“插入”到兩個表(垂直分區)中

  • September 14, 2021

我有兩張表,一張包含所有熱門列,一張包含靜態列。靜態表具有唯一約束。當唯一約束的衝突僅觸發另一個表中的熱列時,應使用靜態表中的 id 更新,如果沒有,那麼我們希望在 tag 中創建一行,在 tag_attributes 中創建一行相同的身份加入他們。由於它是使用 python 腳本完成的,因此我可以執行“從標籤中選擇 id …”之類的操作,並對是否獲得 ID 做出反應,但這感覺不太好。

為了更清楚一些程式碼:https ://dbfiddle.uk/?rdbms=postgres_13&fiddle=027a104bd2cbbec6d70de1a5766e9c76

我想要實現的是更新插入(所以如果唯一的不存在並且如果唯一的確實存在插入(或更新)_hot 表,則在兩個表中插入。

任何幫助將不勝感激:)

我假設您的問題是關於生成錯誤消息的查詢。如果不是這種情況,請編輯您的問題並明確您的問題,並提供一個最小、完整和可驗證的範例

要解決查詢錯誤,請在頂層而不是子選擇中定義 CTE:

with s as (
   select id, column1, column2
   from tag
   where column1 = 'key1' and column2 = 'value1'
), i as (
   insert into tag (column1, column2)
   select 'key1', 'value1'
   where not exists (select 1 from s)
   returning id
)
insert into tag_attributes (id, hot1, hot2, hot3) 
select id, 'var1', 'var1', 'var1'
from i
union all
select id, 'var1', 'var1', 'var1'
from s;

參考:https ://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING

也就是說,為了使您的架構按描述工作,tag_attributes.id應該定義為bigint而不是bigserial. 如果由於某種原因,您需要向tag_attributes中沒有匹配行的行添加行,請tag更改tag.idbigint,使用 創建單獨的序列CREATE SEQUENCE,並使用它為兩個表生成 ID。否則,序列將發生衝突,混亂將接踵而至。

編輯 1

所以你想更新tag_attributes是否id已經存在?只需添加ON CONFLICT DO UPDATE到您的最終INSERT

with s as (
   select id, column1, column2
   from tag
   where column1 = 'key1' and column2 = 'value1'
), i as (
   insert into tag (column1, column2)
   select 'key1', 'value1'
   where not exists (select 1 from s)
   returning id
)
insert into tag_attributes (id, hot1, hot2, hot3) 
select id, 'var1', 'var1', 'var1'
from i
union all
select id, 'var1', 'var1', 'var1'
from s
on conflict (id) do update
set hot1=EXCLUDED.hot1, hot2=EXCLUDED.hot2, hot3=EXCLUDED.hot3;

EXCLUDED是具有新數據的魔術表名稱,並(id)指定哪個約束觸發DO操作(在本例中為主鍵)。

參考:https ://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

引用自:https://dba.stackexchange.com/questions/299490