I'm trying to write a query to insert filenames to the following table table
CREATE SEQUENCE path_id_seq; CREATE TABLE path ( id INTEGER PRIMARY KEY DEFAULT NEXTVAL('path_id_seq'), parent_id INTEGER, name TEXT NOT NULL); CREATE UNIQUE INDEX path_parent_id_name_ix ON path (COALESCE(parent_id, 0), name); ALTER TABLE path ADD CONSTRAINT ios_path_parent_id_fk FOREIGN KEY (parent_id) REFERENCES path (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
For example, given filenames
Assets/images/b.png I'd like to insert the following rows.
id | parent_id | name ----+-----------+------------------- 1 | ¤ | Assets 2 | 1 | images 3 | 2 | a.png 4 | 2 | b.png
Inserting the parent is easy;
INSERT INTO path (name) VALUES ('Assets') ON CONFLICT (COALESCE(parent_id, 0), name) DO UPDATE SET name = EXCLUDED.name -- this strange update is so that we get rows back RETURNING id, parent_id, name;
id | parent_id | name ----+-----------+-------- 1 | ¤ | Assets
But I can't seem to put together a recursive CTE to use the parent's id as the parent_id in the next INSERT statement.
It will probably look somethings like this invalid query below.
WITH RECURSIVE names AS ( SELECT name, lag(name, 1) OVER () as previous_name FROM (SELECT unnest(string_to_array('Assets/images/232.png', '/')) as name) names), paths(id, name) AS ( INSERT INTO path (name) SELECT name FROM names WHERE previous_name IS NULL ON CONFLICT (COALESCE(parent_id, 0), name) DO UPDATE SET name = EXCLUDED.name RETURNING id, name UNION INSERT INTO path (parent_id, name) SELECT paths.id, names.name FROM paths JOIN names ON names.previous_name = paths.name ON CONFLICT (COALESCE(parent_id, 0), name) DO UPDATE SET name = EXCLUDED.name RETURNING id, name) SELECT * FROM paths;
But I just can't seem to get it to work. If anyone sees a simpler way to do this it would be much appreciated.