I am currently using PostgreSQL 12.11 and have a view called previous_customers with the following definition (extracted from DDL):
CREATE VIEW previous_customers(customer_id, alumni) as
SELECT DISTINCT purchase.customer_id,
true AS alumni
FROM purchase p
WHERE (purchase.purchase_state = 'paid'
OR ((SELECT count(*) AS count
FROM purchase
WHERE (purchase.purchase_state NOT IN ('annulled', 'cancelled'))
AND purchase.customer_id = p.customer_id) > 1));
This view works fine, however the issue arises when I compare the results of this view with an identical query:
WITH customer_alu as(
SELECT DISTINCT purchase.customer_id,
true AS alumni
FROM purchase p
WHERE (purchase.purchase_state = 'paid'
OR ((SELECT count(*) AS count
FROM purchase
WHERE (purchase.purchase_state NOT IN ('annulled', 'cancelled'))
AND purchase.customer_id = p.customer_id) > 1)))
SELECT customer_alu.customer_id FROM customer_alu
WHERE customer_alu.customer_id NOT IN
(SELECT previous_customers.customer_id FROM public.previous_customers);
As both the CTE customer_alu and view previous_customers are identical I would imagine this query would return nothing, however I get 30 customer_id's. I am pretty stumped as to how this could happen.
From the Postgres docs:
CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query. link
This leads me to expect that both the view and CTE should execute identical queries, and return the same results.
Any help is appreciated.
The query results differed because of a create_views script, which created the view with an outdated definition. This view was then contrasting with the cached object on my connection to the Postgres DB. I noticed this after refreshing my connection to the DB and getting a conflict warning. The solution was to edit the create_views script and re-run it.