Insert data into multiple tables using one SQL query

Question

I want to use these tables to store data into PostgreSQL

CREATE TABLE SETTING
    (`id` int, `description` varchar(16)
     , `constrained` varchar(5), `data_type` varchar(12)
     , `min_value` varchar(6) NULL , `max_value` varchar(6) NULL)
;

INSERT INTO SETTING
    (`id`, `description`, `constrained`, `data_type`, `min_value`, `max_value`)
VALUES
    (10, 'Favourite Colour', 'true', 'alphanumeric', NULL, NULL),
    (11, 'Item Max Limit', 'false', 'integer', '0', '9001'),
    (12, 'Item Min Limit', 'false', 'integer', '0', '9000')
;

CREATE TABLE ALLOWED_SETTING_VALUE
    (`id` int, `setting_id` int, `item_value` varchar(7)
     , `caption` varchar(6))
;

INSERT INTO ALLOWED_SETTING_VALUE
    (`id`, `setting_id`, `item_value`, `caption`)
VALUES
    (123, 10, '#0000FF', 'Blue'),
    (124, 10, '#FFFF00', 'Yellow'),
    (125, 10, '#FF00FF', 'Pink')
;

CREATE TABLE USER_SETTING
    (`id` int, `user_id` int, `setting_id` int
     , `allowed_setting_value_id` varchar(6) NULL
     , `unconstrained_value` varchar(6) NULL)
;

INSERT INTO USER_SETTING
    (`id`, `user_id`, `setting_id`, `allowed_setting_value_id`, `unconstrained_value`)
VALUES
    (5678, 234, 10, '124', NULL),
    (7890, 234, 11, NULL, '100'),
    (8901, 234, 12, NULL, '1')
;

Can I insert data into all tables using one SQL query? For example multiple SQL insert statements with one SQL query?

Is this possible in PostgreSQL?

Possible duplicate of [Insert data in 3 tables at a time using Postgres](http://stackoverflow.com/questions/20561254/insert-data-in-3-tables-at-a-time-using-postgres) — Tim Biegeleisen, May 12 '16 at 13:18
Oracle have a nice clean syntax for inserting data into multiple tables: Example: http://stackoverflow.com/questions/21817181/oracle-multi-table-insert-syntax — Peter Henell, May 12 '16 at 14:04
Almost any DBMS have the nice feature as "transaction". As an alternative: stored functions, triggers, CTE, ... — Abelisto, May 12 '16 at 14:28
I agree that I can use trigger or function but I need to a lot of advanced knowledge, — Peter Penzov, May 12 '16 at 14:29

score 1 · Answer 1 · answered May 13 '16 at 08:10

The data is too dissimilar to cascade insertions, but if it was more regular (e.g. if your colours would be one for each setting rather than the three of them for the same setting, whilst there's one user setting for each setting) then it would make more sense. It can be done, though, like this (although it is quite silly):

CREATE temp TABLE SETTING (id int, description varchar(16), constrained varchar(5), data_type varchar(12), min_value varchar(6) NULL , max_value varchar(6) NULL);
CREATE TABLE ALLOWED_SETTING_VALUE (id int, setting_id int, item_value varchar(7), caption varchar(6));
CREATE TABLE USER_SETTING (id int, user_id int, setting_id int, allowed_setting_value_id varchar(6) NULL, unconstrained_value varchar(6) NULL);

-- the following is a single statement that inserts all 9 records
with
  allowed AS (
    INSERT INTO ALLOWED_SETTING_VALUE (id, setting_id, item_value, caption)
    VALUES (123, 10, '#0000FF', 'Blue'), (124, 10, '#FFFF00', 'Yellow'), (125, 10, '#FF00FF', 'Pink')
    RETURNING *),
  data(id, description, constrained, data_type, min_value, max_value, us_id, user_id, allowed_setting_value_id, unconstrained_value) as (
    VALUES
        (10, 'Favourite Colour', 'true', 'alphanumeric', NULL, NULL, 5678, 234, (select id::text from allowed where caption = 'Yellow'), NULL),
        (11, 'Item Max Limit', 'false', 'integer', '0', '9001', 7890, 234, NULL, '100'),
        (12, 'Item Min Limit', 'false', 'integer', '0', '9000', 8901, 234, NULL, '1')),
  settings as (
    INSERT INTO SETTING (id, description, constrained, data_type, min_value, max_value)
    SELECT id, description, constrained, data_type, min_value, max_value
    FROM data
    RETURNING *)
INSERT INTO USER_SETTING (id, user_id, setting_id, allowed_setting_value_id, unconstrained_value)
SELECT d.us_id, d.user_id, s.id, d.allowed_setting_value_id, d.unconstrained_value
FROM settings s
JOIN data d ON (d.id = s.id);

One way to have the whole thing running as if it was a single statement is enclosing it in a DO command:

DO $do$ BEGIN
  ... the whole script here ...
END; $do$;

Insert data into multiple tables using one SQL query

1 Answers1