How to insert a row from JSON in Postgres

All we need is an easy explanation of the problem, so here it is.

I have 2 tables recording company market data.
A table recording stock market symbols

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,
)

And a table with many columns (about 50) recording details about the company.

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
)

I am trying to allow client code to create a row without needing to know unique IDs, and not needing to give a value for all columns. Something like the following needs to be valid (as long as they know the symbol name):

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

I created a function, but I can not work out how to get the column names, in the right order, and then specify a variable for the column names. This is as far as I got

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As has been commented, you don’t have to list target columns for json(b)_populate_record(). While using the same row type, columns are guaranteed to match.

Plus, I would simplify / optimize a bit:

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$;

① You seem to be providing a value for overview_id, which is an IDENTITY column with GENERATED ALWAYS. You can force input values as demonstrated.
The manual about OVERRIDING SYSTEM VALUE:

If this clause is specified, then any values supplied for identity columns will override the default sequence-generated values.

Alternatively, use OVERRIDING USER VALUE:

If this clause is specified, then any values supplied for identity columns are ignored and the default sequence-generated values are applied.

With OVERRIDING SYSTEM VALUE the underlying SEQUENCE will be out of sync, and you may want to get back in sync. See:

Related:

② Going with jsonb since that allows the simple concatenation jsonb || jsonb (as opposed to json).

to_jsonb(t.*) FROM ( ... ) t is slightly shorter and faster than json_build_object(). See:

③ And I raise an exception if the symbol is not found. You may or may not want that. But if you remove it, you need to special-case _sym_id IS NULL as _js || NULL yields NULL. Like:

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

Or, more verbose but cheaper:

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

Alternatively, you might insert a new row into appl.symbols if it does not exist yet. A case of SELECT or INSERT. And you may want that safe under concurrent write load. See:

Related:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , , , ,

Leave a Reply