Postgres "Missing From-Clause Entry" Error on Query with With Clause

How to Fix: ERROR: missing FROM-clause entry for table in postgres

You don't need the derived table. Just put the geoboundaries directly in the FROM clause and give an alias to the target table:

UPDATE public.geonames gn
  SET new_shp_id = gb.shapeid
FROM "public"."geoBoundaries" gb
WHERE ST_Intersects(gn.geom, gb.geom);

Postgres Error: missing FROM-clause entry for table

You have defined the table alias pcz within the sub-select however the alias no longer exists when the outside the sub-select. At the point you have used it the appropriate alias is the one for the entire sub-select, in this case vac. So: vac.zip = = bcz.zip

left outer join ( select pup.brokerage_code, pcz.zip, count (pup.aggregate_id) as VerifiedAgentCount
                    from partner_user_profiles pup
                    join partner_user_roles pure on pure.user_profile_id = pup.id
                    join profile_coverage_zips pcz on pcz.profile_id = pup.id
                    where lower(pure.role) = 'agent'
                    and pup.verification_status like 'Verified%'
                    group by pup.brokerage_code, pcz.zip
                ) vac on vac.brokerage_code = b.brokerage_code 
                     and vac.zip = bcz.zip

Postgres “missing FROM-clause entry” error on distinct select query

try this

select distinct user_id from 
(SELECT 
    fb.id,
    fb.Bid,
    fb.offer_id,
    fov.user_id,
    fov.offer_id
FROM
    public.firstapp_bid as fb
inner JOIN public.firstapp_offer_Vendor as fov
    ON fov.offer_id = fb.offer_id)  as foo;

PostgreSQL missing FROM-CLAUSE entry

Your CTE is prefacing the state column with an identifier which does not exist. And, you can simplify your final query:

WITH sb AS (
    SELECT shiftId, b.state, visibleAt
    FROM bookings b
    INNER JOIN memberConnections m
        ON b.memberId = m.memberId
    WHERE shiftId = 1714
)

SELECT
    COALESCE(COUNT(*), 0) AS matched
FROM sb
WHERE
    state = 'MATCHED';

The quoted version of your CTE:

WITH sb AS (
    SELECT "shiftId", b."state", "visibleAt"
    FROM "bookings" b
    INNER JOIN "memberConnections" m
        ON b."memberId" = m."memberId"
    WHERE "shiftId" = 1714
)

ERROR: missing FROM-clause entry for table max_table

Even if it is obvious to you, you have to explicitly state in your main query that you select from the CTE.

Think of a CTE as a view defined just for a single query. You'd need a FROM clause to indicate the view you select from.

RETURNING causes error: missing FROM-clause entry for table

It's true, as has been noted, that the RETURNING clause of an INSERT only sees the inserted row. More specifically, quoting the manual here:

The optional RETURNING clause causes INSERT to compute and return
value(s) based on each row actually inserted (or updated, if an ON CONFLICT DO UPDATE clause was used). This is primarily useful for
obtaining values that were supplied by defaults, such as a serial
sequence number. However, any expression using the table's columns
is allowed. The syntax of the RETURNING list is identical to that
of the output list of SELECT. Only rows that were successfully
inserted or updated will be returned. [...]

Bold emphasis mine.

So nothing keeps you from adding a correlated subquery to the RETURNING list:

INSERT INTO employees.password_resets AS ep
       (empl_pwd_reset_uuid                  , empl_user_pvt_uuid                    , t_valid                     , for_empl_user_pvt_uuid, token)
SELECT 'f70a0346-a077-11eb-bd1a-Postgres "Missing From-Clause Entry" Error on Query with With Clauseaaaa', '6efc2b7a-f27e-11ea-b66c-de1c405de048', '2021-04-18 19:57:47.111365', eu.empl_user_pvt_uuid , '19d65aea-7c4a-41bc-b580-9d047f1503e6'
FROM   employees.users eu
WHERE  empl_user_pub_uuid = 'e2bb39f1f28011eab66c63cb4d9c7a34'
RETURNING for_empl_user_pvt_uuid AS empl_user_pvt_uuid  -- alias to meet your org. query
        , (SELECT email
           FROM   employees.emails
           WHERE  empl_user_pvt_uuid = ep.empl_user_pvt_uuid
           ORDER  BY t DESC  -- NULLS LAST ?
           LIMIT  1
          ) AS email
        , (SELECT name_first
           FROM   employees.profiles
           WHERE  empl_user_pvt_uuid = ep.empl_user_pvt_uuid
           -- ORDER  BY ???
           LIMIT  1
          ) AS name_first;

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 and ep over all rows of the tables employees.emails and employees.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:

  • Efficient query to get greatest value per group from big table
  • Two SQL LEFT JOINS produce incorrect result
  • Select first row in each GROUP BY group?
  • Optimize GROUP BY query to retrieve latest row per user

• 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 for profiles.name_first. That's not right. Either there is only one qualifying row, then we need no DISTINCT nor LIMIT 1. Or there can be multiple, then we also need a deterministic ORDER BY to get a deterministic result.

And if emails.t can be NULL, you'll want to add NULLS LAST in the ORDER BY clause. See:

• Sort by column ASC, but NULL values first?

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:

• Return pre-UPDATE column values using SQL only

---

Related Topics