Here's what I have:
=# SELECT * FROM users;
id | name
----+---------------
1 | Paul Gilbert
2 | Bob Dylan
3 | Cliff Downing
=# SELECT * FROM regs;
reg_id | user_id
--------+---------
1 | 1
11 | 1
111 | 1
2 | 2
22 | 2
3 | 3
Here's what I want to obtain:
=# SELECT * FROM joined;
id | name | regs
----+---------------+-------------------
1 | Paul Gilbert | {1, 11, 111}
2 | Bob Dylan | {2, 22}
3 | Cliff Downing | {3}
MWE:
-- create users table:
DROP TABLE users;
CREATE TABLE users(
id INT PRIMARY KEY NOT NULL,
name VARCHAR NOT NULL
);
-- populate users table:
INSERT INTO users (id, name)
VALUES (1, 'Paul Gilbert'),
(2, 'Bob Dylan'),
(3, 'Cliff Downing');
-- reg table:
DROP TABLE regs;
CREATE TABLE regs(
reg_id INT PRIMARY KEY NOT NULL,
user_id INT NOT NULL
);
-- populate reg table:
INSERT INTO regs (reg_id, user_id)
VALUES (1, 1),
(11, 1),
(111, 1),
(2, 2),
(22, 2),
(3, 3);
-- joined table:
DROP TABLE joined;
CREATE TABLE joined(
id INT,
name VARCHAR,
regs INT[]
);
Here's my effort so far:
BEGIN;
INSERT INTO joined (id, name)
SELECT id, name
FROM users;
INSERT INTO joined (regs)
SELECT ARRAY(
SELECT reg_id
FROM regs
INNER JOIN joined
ON joined.id = regs.user_id
);
COMMIT;
It doesn't work:
id | name | regs
----+---------------+-----------------------------------
1 | Paul Gilbert |
2 | Bob Dylan |
3 | Cliff Downing |
| | {1,11,111,2,22,3}
Edit
Solution by a_horse_with_no_name works great.
Do you think there's a way to add yet another join in a single expression?
CREATE TABLE ldap.univer_users(
first_name VARCHAR,
middle_name VARCHAR,
last_name VARCHAR,
code VARCHAR,
doc_number VARCHAR,
reg_numbers VARCHAR[]
);
BEGIN;
INSERT INTO ldap.univer_users (first_name, middle_name, last_name, code, reg_numbers)
SELECT
d."firstName", d."middleName", d."lastName", d.uid, array_agg(r."regNumber")
FROM
student_data d
JOIN student_reg_numbers r ON d.uid = r.student_datum_uid
GROUP BY d.uid;
UPDATE ldap.univer_users
SET doc_number = p.doc_number
FROM (
SELECT student_datum_uid, substring(number FROM '....$') AS doc_number
FROM student_passports
WHERE "passportType" IN ('Паспорт РФ', 'Паспорт иностранного гражданина')
) p
WHERE code = p.student_datum_uid;
COMMIT;
This works with my data, but I'm curious is there a way to put it into a single insert
statement.
Edit 2
Trying to unite all in a single expression:
INSERT INTO ldap.univer_users (first_name, middle_name, last_name, code, doc_number, reg_numbers)
SELECT
d."firstName", d."middleName", d."lastName", d.uid, substring(p.number FROM '....$'), array_agg(r."regNumber")
FROM
student_data d
JOIN student_reg_numbers r ON d.uid = r.student_datum_uid
JOIN student_passports p ON d.uid = p.student_datum_uid
AND p."passportType" IN ('Паспорт РФ', 'Паспорт иностранного гражданина')
GROUP BY d.uid;
Postgres says
column "p.number" must appear in the GROUP BY clause or be used in an aggregate function
Best Answer
You need to use a single
insert
statement:Your two step approach requires an
update
as the second step, not aninsert
: