ArcGIS Pro will share/export to PNG including transparency.
If you have set a background colour, there will be no transparency, but if there is no background and no basemap then it will export to PNG with a transparent background.
The following image was exported from ArcGIS Pro to PNG (Color Mode: 32-bit with Alpha) and then added to ArcMap
I then set the background colour in ArcMap to blue, behind the same PNG image
Which shows that the PNG was exported from ArcGIS Pro with transparency.
This is only the case in the Map itself. ArcGIS Pro does not currently support exporting with transparency in the Layout.
The key to this is to recognize that ST_AsPNG returns a bytea, ie a byte array, representing a png. So, you need to somehow convert those bytes to a file on the file system. There are a couple of ways of doing this as shown in the docs.
The oid approach, as outlined by @Stefan, works, but requires psql and is a bit obscure, in my opinion.
The other approach, is to use Pythonu (ie, a Python stored procecure) to convert the bytea to an actual png file.
CREATE OR REPLACE FUNCTION write_file (param_bytes bytea, param_filepath text)
RETURNS text AS $$
f = open(param_filepath, 'wb+')
f.write(param_bytes)
return param_filepath
$$ LANGUAGE plpythonu;
After which you can do:
WITH png (bytes) AS
(SELECT ST_AsPNG(ST_Union(geom))
FROM holdings
WHERE town = 'Macclesfield')
SELECT write_file(bytes, path_to_png) FROM png;
which is very clean.
As a note of warning, the Python stored procedure langauge is called plpythonu -- the final u stands for untrusted. As it states in the docs:
PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted variant plpython might become available in the future if a secure execution mechanism is developed in Python. The writer of a function in untrusted PL/Python must take care that the function cannot be used to do anything unwanted, since it will be able to do anything that could be done by a user logged in as the database administrator. Only superusers can create functions in untrusted languages such as plpythonu
so you will want to make sure you use it in an isolated environment, if you go this route.
EDIT: As has been pointed out by @jpmc26, the plpythonu approach will only work server-side, and you will need to install plpythonu (which is not in the standard package), both of which means that you will need to have admin/super user privileges on the server, both of which are reasonable observations. I would argue, however, that if you don't have access to the server, once you have understood that ST_AsPNG returns a byte array, it is would be reasonably straight forward to write something client-side to consume the return value -- which could even be plain vanilla python, via psycopg2 (or anything other programming language that can call a Postgres function and write the resulting byte stream to disk locally).
For anyone wanting to go this route, in Ubuntu/Debian, you need to install the plpythonu extension, and then create the extension, which can be done with (change for your version).
sudo apt install postgresql-plpython-9.6
followed by
CREATE LANGUAGE plpythonu;
either within psql, pgadmin, or from a shell prompt of some description.
Best Answer
In Windows:
In bash:
for i in *.tif; do gdal_translate -of GTIFF -a_nodata 0 $i $i~new_ndata.tif; done; for i in *~new_ndata.tif; do gdal_translate -of PNG -scale -co worldfile=no $i $i~cnvt.png; done;