How to INSERT a new record into 2 tables with FKEY inheritance?

Question

Inheritance has been setup with FKEY instead of INHERITS due to FKEY limitation.
My question: How to INSERT a new record into bonds table?

CREATE TABLE IF NOT EXISTS instruments (
  id SERIAL,
  created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
  ticker varchar(255),
  type varchar(255),
  PRIMARY KEY (id)
);
  
CREATE TABLE IF NOT EXISTS bonds (
  id int NOT NULL,
  maturity date,
  PRIMARY KEY (id),
  CONSTRAINT fk_instruments FOREIGN KEY (id) REFERENCES instruments(id)
);

CREATE TABLE IF NOT EXISTS equities (
  id int NOT NULL,
  name varchar(255),
  PRIMARY KEY (id),
  CONSTRAINT fk_instruments FOREIGN KEY (id) REFERENCES instruments(id)
);

Answer 1

Maybe you need in something similar to this:

WITH 
source_data (ticker, type, maturity) AS (
  SELECT 'ticker 1', 'type 2', '2024-01-02' :: DATE 
  UNION ALL
  SELECT 'ticker 2', 'type 2', '2024-02-03' :: DATE 
  ),
insert_into_instruments AS (
  INSERT INTO instruments (ticker, type)
  SELECT source_data.ticker, source_data.type
  FROM source_data
  LEFT JOIN instruments ON (source_data.ticker, source_data.type) = (instruments.ticker, instruments.type)
  WHERE instruments.id IS NULL
  RETURNING id, ticker, type
    )
INSERT INTO bonds
SELECT instruments.id, source_data.maturity
FROM source_data
JOIN ( SELECT id, ticker, type
       FROM instruments 
       UNION ALL
       SELECT id, ticker, type
       FROM insert_into_instruments
       ) instruments ON (source_data.ticker, source_data.type) = (instruments.ticker, instruments.type);

fiddle

But I'd recommend you to create unique index by instruments (ticker, type) and use ON CONFLICT clause in insert_into_instruments.