如何在 Postgres 中從 JSON 插入一行

我有兩張記錄公司市場數據的表格。

記錄股市符號的表格

CREATE TABLE appl.symbols (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  symbol VARCHAR(10) NOT NULL,
  exchange VARCHAR(10) NOT NULL,
  date_added DATE NOT NULL DEFAULT CURRENT_DATE,
  active BOOLEAN NOT NULL DEFAULT true,
)

還有一張包含許多列（大約 50 列）的表格，其中記錄了有關公司的詳細資訊。

CREATE TABLE appl.fundamentals_overview (
   overview_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   symbol_id INT,
   CONSTRAINT fk_symbol
     FOREIGN KEY(symbol_id) 
       REFERENCES appl.symbols(id),
   assettype VARCHAR(255),
   name VARCHAR(255),
   description VARCHAR(1500),
   cik VARCHAR(10),
   currency VARCHAR(10),
   country VARCHAR(10),
   sector VARCHAR(50),
   industry VARCHAR(100),
   address VARCHAR(100),
   fiscalyearend VARCHAR(10),
   latestquarter DATE,
   marketcapitalization BIGINT,
   ebitda BIGINT,
   peratio NUMERIC(20, 4),
   pegratio NUMERIC(20, 4),
   bookvalue NUMERIC(20, 4),
   dividendpershare NUMERIC(20, 4),
   dividendyield NUMERIC(20, 4),
   eps NUMERIC(20, 4),
   revenuepersharettm NUMERIC(20, 4),
   profitmargin NUMERIC(20, 4),
   operatingmarginttm NUMERIC(20, 4)
   ... many more columns
)

我試圖允許客戶端程式碼在不需要知道唯一 ID 並且不需要為所有列提供值的情況下創建一行。像下面這樣的東西需要是有效的（只要他們知道符號名稱）：

{"assettype": "Common Stock", 
 "name": "AcmeINC", 
 "cik": "1555752", 
 "currency": "USD", 
 "country": "USA", 
 "fiscalyearend": "December",
 "peratio": 235.56
}

我創建了一個函式，但我不知道如何以正確的順序獲取列名，然後為列名指定一個變數。這是據我所知

CREATE OR REPLACE FUNCTION appl.insert_fund_overview(sym TEXT, ex TEXT, js TEXT) RETURNS VOID AS
$func$
DECLARE
   symID BIGINT := 0;
   fullJSON JSON;
   tempJSON JSON;
   colNames TEXT;
BEGIN 
   symID := (SELECT appl.symbols.id FROM appl.symbols WHERE symbol = sym AND exchange = ex);
   tempJSON := json_build_object('symbol_id', symID);
   fullJSON := (js::jsonb) || (tempJSON::jsonb);
   colNames := (SELECT string_agg(elem, ',') FROM json_object_keys(fullJSON) elem);
   --The value of colNames contains the JSON key names, but in wrong order

   -- This does not work
   INSERT INTO appl.fundamentals_overview (colNames)  
   SELECT *  
   FROM json_populate_record(NULL::appl.fundamentals_overview, fullJSON::json);
END
$func$  LANGUAGE plpgsql;

ERROR:  column "colnames" of relation "fundamentals_overview" does not exist

如前所述，您不必列出json(b)_populate_record(). 在使用相同的行類型時，列可以保證匹配。

另外，我會簡化/優化一點：

CREATE OR REPLACE FUNCTION appl.insert_fund_overview(_sym text, _ex text, _js jsonb)  -- ②
 RETURNS void
 LANGUAGE plpgsql AS
$func$
DECLARE
  _sym_id jsonb;
BEGIN
  SELECT INTO _sym_id  to_jsonb(t.*)
  FROM  (SELECT id AS symbol_id FROM appl.symbols
         WHERE symbol = _sym AND exchange = _ex) t;
  
  IF NOT FOUND THEN  -- ③
     RAISE EXCEPTION 'Symbol not found in table appl.symbols!';
  END IF;

-- This works:
  INSERT INTO appl.fundamentals_overview OVERRIDING SYSTEM VALUE  -- ①
  SELECT *
  FROM   jsonb_populate_record(NULL::appl.fundamentals_overview, _js || _sym_id);  -- ②
END
$func$;

① 您似乎在為 提供一個值overview_id，這是一個IDENTITY帶有 的列GENERATED ALWAYS。您可以強制輸入值，如所示。

手冊關於OVERRIDING SYSTEM VALUE：

如果指定了此子句，則為標識列提供的任何值都將覆蓋預設的序列生成值。

或者，使用OVERRIDING USER VALUE：

如果指定了此子句，則為標識列提供的任何值都將被忽略，並應用預設的序列生成值。

與OVERRIDING SYSTEM VALUE底層SEQUENCE將不同步，您可能希望重新同步。看：

• 不同步時如何重置postgres的主鍵序列？

有關的：

• PostgreSQL如何預設分區標識列？

② Going with jsonbsince 允許簡單的連接jsonb || jsonb（而不是json）。

to_jsonb(t.*) FROM ( ... ) t比 略短和快json_build_object()。看：

• 在 SQL (Postgres) 中作為 JSON 對像數組返回

③ 如果找不到符號，我會引發異常。你可能想要也可能不想要。但是如果你刪除它，你需要特殊情況_sym_id IS NULL作為_js || NULLyield NULL。像：

_js || COALESCE(_sym_id, '{}')

或者，更詳細但更便宜：

CASE WHEN _sym_id IS NULL THEN _js ELSE _js || _sym_id) END

appl.symbols或者，如果新行尚不存在，您可以插入新行。*SELECT或INSERT*的情況。而且您可能希望在並發寫入負載下安全。看：

• 函式中的 SELECT 或 INSERT 是否容易出現競爭條件？

有關的：

• 如何使用動態 SQL 設置複合變數欄位的值

引用自：https://dba.stackexchange.com/questions/295489