php - Fastest query to insert multiple pairs of rows with cross-reference in PSQL?

908

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?

566

Answer

Solution:

You could reserve some ids :

select nextval('tbl_item_id_seq') from generate_series(1,200)

then manually assign the id/pair_id. This way, the inserts could even be a single COPY statement(if your Postgres driver supports it).

People are also looking for solutions to the problem: php - Laravel Ioc container in out of Laravel

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.