php - Fastest query to insert multiple pairs of rows with cross-reference in PSQL?
So, there is this situation when you have a table in which you want to insert rows in pairs with a reference to each other. Just like in double-entry accounting, every item has it's opposite as pair of it. There is this table:
CREATE SEQUENCE tbl_item_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
CREATE TABLE tbl_item (
id integer NOT NULL PRIMARY KEY DEFAULT nextval('tbl_item_id_seq'),
pair_id integer,
label character varying(50) NOT NULL,
FOREIGN KEY (pair_id) REFERENCES tbl_item (id)
);
ALTER SEQUENCE tbl_item_id_seq OWNED BY tbl_item.id;
The items are generated procedurally. Usually, there are multiple pairs generated at once, and the ultimate goal would be to insert all the pairs with one query. I have solved this with PHP where I inserted a row, returned it's id, inserted the other row with pair_id filled and updated the first row with the id of the second. This means 3 db query started from PHP, and since there are multiple pairs generated, it means number_of_pairs * 3 queries. When I have about 100 pairs, it means 300 queries and gives a nice overhead in processing time what I would like to minimize.
So, the question is given, what's the fastest way to insert pairs of rows with a reference to each other's id into a single table in PSQL?
Answer
Solution:
You could reserve some ids :
then manually assign the id/pair_id. This way, the inserts could even be a single COPY statement(if your Postgres driver supports it).