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 ?
;

• Inactivated Account: I think it evaluates CTE's first, before going out and searching for database tables/views - likely a question for stackoverflow, etc., though...
• Evan Carroll: @DPSSpatial we exist too. ;)
• Inactivated Account: @EvanCarroll who...?

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;