Upsert:
In SQL Server, we can achieve an upsert by using a MERGE statement. Postgres doesn’t support MERGE, but gives another option to implement this functionality, using ON CONFLICT.

INSERT INTO public.player(number, position, name)
VALUES (2, 'QB', 'Matt Ryan')
ON CONFLICT (number) DO NOTHING;

In this example, we attempt to insert a new Player record. If a record with jersey number 2 doesn’t exist, the record will be inserted. If the record already exists, then no action is taken.
The column specified in ON CONFLICT needs to have some sort of unique key defined. Alternatively, we can specify a constraint name here
We can also add an UPDATE to the statement, to replicate a true upsert. Refer to the EXCLUDED pseudo table with the values that we tried to insert.

INSERT INTO public.player(number, position, name)
VALUES (2, 'QB', 'Matt Ryan')
ON CONFLICT (number) DO
UPDATE SET position = EXCLUDED.position,
name = EXCLUDED.name;

Delete:
A second observation had to do with deleting a record, where we want to join to another table to determine the criteria.
In SQL Server, we can use DELETE … FROM, and simply join to multiple tables in the FROM.
In Postgres, we would use USING, and specify the columns to join in the WHERE clause.

DELETE FROM public.table1 as t1
USING public.table2 as t2
WHERE t1.column1 = t2.column1;

So this statement would delete all of the records in table1 where we found a match in table2 on the column1 value.

Links:
Postgresql Tutorial: Upsert
Postgresql Tutorial: Delete