PostgreSQL priority between CTE and real table Is there some kind of priority ranks assigned to CTE and table name ? For instance, if I have a table called table_a in the public schema, and I create a CTE table named table_a using WITH, which table will be taken if I use table_a in the SELECT query ?

--as an example
CREATE TABLE table_a (
  id serial
);

WITH table_a AS (
     SELECT id
     FROM another_table
     )
SELECT *
FROM table_a --&gt; which table is this ?
;
PostgreSQL searches the CTE namespace with scanNameSpaceForCTE as the very first thing it does in searchRangeTableForRel

if (!relation-&gt;schemaname)
{
    cte = scanNameSpaceForCTE(pstate, refname, &amp;ctelevelsup);
    if (!cte)
        isenr = scanNameSpaceForENR(pstate, refname);
}


If there is no schema


check for a CTE
check for a "Ephemeral Named Relation"


This is similar to Variable Shadowing if there is no namespace, and what the spec otherwise demands.

Everything in PostgreSQL has a namespace, if you want to address the table as compared to the CTE, consider providing (qualifying) the namespace.

CREATE TABLE foo AS VALUES (0);

WITH foo AS ( VALUES (1) )
SELECT *
FROM ( VALUES (2) ) AS foo   -- inline virtual-table
UNION TABLE foo              -- CTE
UNION TABLE public.foo;      -- explicitly qualified the namespace;