How to delete records from CTE (Common Table Expression) in Postgres

Deleting from a CTE is not possible.

You can do like this:

demo:db<>fiddle

DELETE FROM employee
WHERE id IN (
    SELECT
        id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY firstname, lastname, country) row_num
        FROM 
            employee
    ) s
    WHERE row_num > 1
)

If you want to use a CTE nevertheless, you can move the subquery into one:

demo:db<>fiddle

WITH cte AS (
    SELECT
        id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY firstname, lastname, country) row_num
        FROM 
            employee
    ) s
    WHERE row_num > 1
)
DELETE FROM employee
WHERE id IN (SELECT * FROM cte)

You can not delete record from CTE table in postgresql. Although it is possible in other DB server like SQLSERVER. You can see CTE document for postgresql.

I would suggest using:

delete from employee e
    where e.id > (select min(e2.id)
                  from employee e2
                  where e2.firstname = e.firstname and
                        e2.lastname = e.lastname and 
                        e2.country = e.country
                 );

This is standard SQL and will work in both SQL Server and Postgres.

The only difference from the SQL Server version is how NULL values are treated. This treats them as all being "different" so a matching NULL values would not be considered a match. This is probably not an issue in your data, but you can use:

                  where e2.firstname is not distinct from e.firstname and
                        e2.lastname is not distinct from e.lastname and 
                        e2.country is not distinct from e.country

If you want NULL values to match.

Note that the original version should be able to make use of an index on (lastname, firstname, country, id) (in either database).

this worked for me as you cannot delete on cte on postgres:

CREATE TEMPORARY TABLE temp_table AS 
SELECT DISTINCT ON (id) * FROM your_table;
DELETE FROM your_table;
INSERT INTO your_table SELECT * FROM temp_table;

Simplified syntax of a DELETE statement:

[ WITH with_query [, ...] ]
DELETE FROM table_name [ [ AS ] alias ]
    [ USING from_item [, ...] ]
    [ WHERE condition ]

A parameter after the FROM keyword stands for:

The name (...) of the table to delete rows from.

The CTE is to be referenced in the WHERE clause.

As an addition to the existing S-Man's answer, the query can be rewritten by moving the auxiliary table expression into the USING clause. Which has the same syntax as the FROM clause of a SELECT statement. And implicitly joined with the source table from where records are deleted.

Also, the question can be interpreted as how to delete duplicate records from a table with unspecified constraints. While other answers works when the id column has a unique constraint, they fail if it doesn't. For example:

For this case the ctid system column can be used. Which is:

The physical location of the row version within its table.

So the suggested query is:

DELETE FROM employee
    USING (
        SELECT
            ctid
        FROM (
            SELECT
                ctid,
                ROW_NUMBER() OVER (
                    PARTITION BY
                        firstname,
                        lastname,
                        country
                    ORDER BY
                        id
                ) AS row_num
            FROM
                employee
        ) AS ranked
        WHERE
            row_num > 1
    ) AS to_delete
    WHERE
        employee.ctid = to_delete.ctid;

I think better case will be just to filter not needed rows on step of creating CTE (without any deleting)

WITH cte AS (
    SELECT 
        firstname, 
        lastname, 
        country, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                firstname, 
                lastname, 
                country
        ) row_num
     FROM employee
     WHERE row_num >1
)