3

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

migrated from gis.stackexchange.com Apr 16 '18 at 16:21

This question came from our site for cartographers, geographers and GIS professionals.

  • I think it evaluates CTE's first, before going out and searching for database tables/views - likely a question for stackoverflow, etc., though... – DPSSpatial Apr 16 '18 at 16:09
  • 2
    @DPSSpatial we exist too. ;) – Evan Carroll Apr 16 '18 at 16:43
  • 1
    @EvanCarroll who...? – DPSSpatial Apr 16 '18 at 17:13
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.