'POSTGRESQL: inserting multiple values in second table using uuid from first insert
Tables
CREATE TABLE “transactions” (
“uuid” uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
“user_uuid” uuid,
“chain_uuid” uuid,
“block_number” int,
“timestamp” int,
“hash” text,
“from” text,
“to” text,
“address” text,
“value” numeric,
“value_fiat” numeric
“fees” numeric,
“fees_fiat” numeric,
“is_error” int,
“input” text,
“protocol” text,
“type” text,
“reviewed_status”,
“is_manual” boolean,
“is_transferring_native_asset” boolean
);
CREATE TABLE “transaction_assets” (
“uuid” uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
“transaction_uuid” uuid,
“asset_uuid” uuid,
“type” text,
“is_assetin”, boolean
“balance” numeric,
“price” numeric,
“value” numeric
--CONSTRAINT transaction_assets_pkey PRIMARY KEY (uuid)
);
My Query
Begin;
with tx as (
INSERT INTO
transactions (user_uuid, chain_uuid, ..blah blah blah)
VALUES($ 1, $ 2, $ 3,.... $ 16 ) RETURNING uuid)
INSERT INTO
transaction_assets (transaction_uuid, asset_uuid, type, is_assetin, balance, price,value
)
VALUES
((SELECT tx.uuid FROM tx),$ 17,$ 18,$ 19,$ 20,$ 21),
((SELECT tx.uuid FROM tx),$ 22,$ 23,$ 24,$ 25,$ 26);
// repeat with many transactions
Commit;
I am using pg-promise. I have an array of transaction objects in javascript, each transaction has an array of assets associated with it. I would like to insert the transactions into the transaction table and the assets associated with that transaction into the transaction_assets table, using the transaction UUID from the first insert. I have many transactions to insert. which is why I am using the begin/commit. I would like to insert many transactions at the same time but if it fails be able to role back all of them
my current query is getting the error
syntax error at or near "from"
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
