I am getting the users data from UUID WHERE empl_user_pub_uuid = 'e2bb39f1f28011eab66c63cb4d9c7a34'
.
Since I don't want to make an additional query to fetch additional user data I'm trying to sneak them through the INSERT
.
WITH _u AS (
SELECT
eu.empl_user_pvt_uuid,
ee.email,
ep.name_first
FROM employees.users eu
LEFT JOIN (
SELECT DISTINCT ON (ee.empl_user_pvt_uuid)
ee.empl_user_pvt_uuid,
ee.email
FROM employees.emails ee
ORDER BY ee.empl_user_pvt_uuid, ee.t DESC
) ee ON eu.empl_user_pvt_uuid = ee.empl_user_pvt_uuid
LEFT JOIN (
SELECT DISTINCT ON (ep.empl_user_pvt_uuid)
ep.empl_user_pvt_uuid,
ep.name_first
FROM employees.profiles ep
) ep ON eu.empl_user_pvt_uuid = ep.empl_user_pvt_uuid
WHERE empl_user_pub_uuid = 'e2bb39f1f28011eab66c63cb4d9c7a34'
)
INSERT INTO employees.password_resets (empl_pwd_reset_uuid, empl_user_pvt_uuid, t_valid, for_empl_user_pvt_uuid, token)
SELECT 'f70a0346-a077-11eb-bd1a-aaaaaaaaaaaa', '6efc2b7a-f27e-11ea-b66c-de1c405de048', '2021-04-18 19:57:47.111365', _u.empl_user_pvt_uuid, '19d65aea-7c4a-41bc-b580-9d047f1503e6'
FROM _u
RETURNING _u.empl_user_pvt_uuid, _u.email, _u.name_first;
However I get:
[42P01] ERROR: missing FROM-clause entry for table "_u" Position: 994
What am I doing wrong?
It's true, as has been noted, that the
RETURNING
clause of anINSERT
only sees the inserted row. More specifically, quoting the manual here:Bold emphasis mine.
So nothing keeps you from adding a correlated subquery to the
RETURNING
list:This is also much more efficient than the query you had (or what was proposed) for multiple reasons.
We don't run the subqueries
ee
andep
over all rows of the tablesemployees.emails
andemployees.profiles
. That would be efficient if we needed major parts of those tables, but we only fetch a single row of interest from each. With appropriate indexes, a correlated subquery is much more efficient for this. See:We don't add the overhead of one or more CTEs.
We only fetch additional data after a successful
INSERT
, so no time is wasted if the insert didn't go through for any reason. (See quote at the top!)Plus, possibly most important, this is correct. We use data from the row that has actually been inserted - after inserting it. (See quote at the top!) After possible default values, triggers or rules have been applied. We can be certain that what we see is what's actually in the database (currently).
You have no
ORDER BY
forprofiles.name_first
. That's not right. Either there is only one qualifying row, then we need noDISTINCT
norLIMIT 1
. Or there can be multiple, then we also need a deterministicORDER BY
to get a deterministic result.And if
emails.t
can be NULL, you'll want to addNULLS LAST
in theORDER BY
clause. See:Indexes
Ideally, you have these multicolumn indexes (with columns in this order):
users (empl_user_pub_uuid, empl_user_pvt_uuid)
emails (empl_user_pvt_uuid, email)
profiles (empl_user_pvt_uuid, name_first)
Then, if the tables are vacuumed enough, you get three index-only scans and the whole operation is lightening fast.
Get pre-
INSERT
values?If you really want that (which I don't think you do), consider: