POSTGRESQL: Insert into 2 tables in the same query, using ID generated from first insert in the seco-CodePudding

Tables

CREATE TABLE "assets"
(
    "uuid" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    "symbol" text NOT NULL,
    "name" text NOT NULL,
    "decimal" numeric DEFAULT 18,
    "img_small" text DEFAULT '',
    "img_large" text DEFAULT '',
    "gecko_id" text
)

CREATE TABLE "chain_asset"
(
    "uuid" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    "chain_uuid" uuid NOT NULL,
    "asset_uuid" uuid NOT NULL,
    "contract" text NOT NULL,
    "chain_contract" text NOT NULL,    
)

My Query

    `WITH x AS (
    INSERT INTO assets (symbol, name, decimal) VALUES ($1, $2, $3) RETURNING uuid
     )
    INSERT INTO chain_asset (chain_uuid, asset_uuid, contract, chain_contract) VALUES ($4, x.uuid ,$5, $6)
`,

I'm using pg promise. I would like to insert into 2 tables using the same query. I need the uuid from the insert into the asset table in order to insert into the chain_asset table. using x.uuid is not working, I'm getting the following error

Error: missing FROM-clause entry for table "x"

CodePudding user response：

You don't need the VALUES(), Just SELECT the literals in a select-clause:

WITH x AS (
    INSERT INTO assets(symbol,name,decimal) VALUES ($1, $2, $3)
    RETURNING uuid
    )
INSERT INTO chain_asset(chain_uuid,asset_uuid,contract,chain_contract)
SELECT $4,x.uuid,$5,$6
FROM x
;