I learned Python/ArcGIS programming with the Zandbergen Esri Press book. PostGIS was a tailor-made course with some booklets that explain the most common hurdles that you'll run into going blind, but it was in Dutch so I don't think it'll be of much help. Honestly - there are likely a bunch of good free tutorials out there, and the rest you can learn by doing a lot of googling that starts with "postgis" followed by some geoprocessing function you already know how to use in ArcGIS.

There's a lot of useful PG code on GIS Stack Exchange, but I would strongly recommend you put in the effort to figure out what all the puzzle pieces do in other people's code answers. PG for day to day GIS work is best learnt by doing. One piece of tribal knowledge: you can rename columns on the fly, simply by having any text after it. The official syntax is

    SELECT table.columname AS newcolumname,

but the much more common lazy variant is to drop the 'as' and to use very short aliases for tables. So this is valid and common to see, because it's much faster to refer to a table by 1 letter even if it's harder to read for others:

   SELECT b.columname a FROM schema.table b 

Basically, PostGIS is a big sack of useful functions and it's up to you to assemble them correctly. The biggest increases in productivity beyond basic operations, has been to understand a) how to cast data (e.g. '1234'::int becoming the int 1234), b) understanding when to use nested queries (mostly whenever your joins become too large for your memory to handle), and c) mastering the GROUP BY.

The best learning tool for me has been to create one "Cheat Sheet" file. Every time I learn a new function or way of solving a problem, I add it to that file with a little one-line comment. Below a few useful ones for the most common geodata edits I keep at the top, which are the below.

  -- find out your current PG version
   SELECT PostGIS_full_version();
  --create column index
  create index idx_cbs_woonkernen_reistijd_weg_woonkern on bo_cbs.cbs_woonkernen_reistijd_weg using btree(woonkern); 
  create index sidx_hx_tmp_geom ON hx_tmp USING gist(geom);
  --add primary key
  alter table bo_cbs.cbs_woonkernen_reistijd_weg add id serial primary key; 
  --change coordinate system
  alter table bo_cbs.cbs_woonkernen_reistijd_weg alter column geom type geometry(polygon,28992)   using st_transform(geom,28992); 
  -- Set geometry type and drop z axis from points that have them
  alter table pr_dm_eindhoven_2022.deelautos_benchmark alter column geom type   geometry(point,4326) using st_force2d(geom); 
  -- alter geometry type 
  ALTER TABLE my_table ALTER COLUMN geom TYPE geometry(MultiPoint,4326) USING ST_Multi(geom);
  ALTER TABLE pr_dm_heuvelrug_2019.tankstations ALTER COLUMN geom TYPE geometry(Point,28992);
  ALTER TABLE pr_dm_engie_2021.lms_wegen_2030 ALTER COLUMN wkb_geometry TYPE geometry(LineString,28992)  using st_transform(st_setsrid(wkb_geometry, 4326),28992);
  select updategeometrysrid('pr_dm_engie_2021', 'lms_wegen_2030', 'wkb_geometry', 28992)