I am using postgreSQL 9.1 and I want to delete duplicates from my table using this tip: https://stackoverflow.com/a/3822833/2239537
So, my query looks like that:
WITH cte
AS (SELECT ROW_NUMBER()
OVER (PARTITION BY code, card_id, parent_id
ORDER BY id DESC) RN
FROM card)
DELETE FROM cte
WHERE RN > 1
But it shows me
ERROR: relation "cte" does not exist
SQL state: 42P01
Character: 157
However this statement works fine:
WITH cte
AS (SELECT ROW_NUMBER()
OVER (PARTITION BY code, card_id, parent_id
ORDER BY id DESC) RN
FROM merchantcard)
SELECT * FROM cte
WHERE RN > 1
Any ideas how to get it work? Thanks!
that's because CTE in PostgreSQL works differently than CTE in SQL Server. In SQL Server CTE are like an updatable views, so you can delete from them or update them, in PostgreSQL you cannot.
you can join cte and delete, like:
On the other hand, you can write DDL statements inside CTE in PostgreSQL (see documentation) and this could be very handy. For example, you can delete all rows from
card
and then insert only those having row_number = 1:I know, you are asking how you can solve your problem using the WITH statement, and got a good answer already. But I suggest looking at alternatives in the same question you linked.
What about this one?
For me it worked Like this in Postgres/GreenPlum :