CTE - SQL Server vs PostgreSQL

With myCTE AS (SELECT * FROM dbo.MyTable)

SELECT *

  FROM myCTE 

  WHERE Id = 1;

In SQL Server CTEs are processed with the main query.
In PostgreSQL CTEs are processed separately from the main query

Implications of this difference are:

1. A query that should touch a small amount of data instead reads a whole table and possibly spills it to a tempfile.
2. You cannot UPDATE or DELETE FROM a CTE term, because it’s more like a read-only temp table rather than a dynamic view.
3. In PostgreSQL, in this query:

With myCTE AS (SELECT * FROM dbo.MyTable)

SELECT *

  FROM myCTE 

  WHERE Id = 1;

WHERE clauses aren’t applied until the execution of the main query, and it’s different than:

With myCTE AS (SELECT * FROM dbo.MyTable WHERE Id = 1)

SELECT *

  FROM AllPosts;