PostgreSQL priority between CTE and real table

Question

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 --> which table is this ?
;

Answer 1

PostgreSQL searches the CTE namespace with scanNameSpaceForCTE as the very first thing it does in searchRangeTableForRel

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

If there is no schema

1. check for a CTE
2. 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;