pg_dump remote server tables

Je moet eerst even in pgpass.conf in %APPDATA% aanmaken.

Dus:
cd %APPDATA%
notepad pgpass.conf

Inhoud pgpass.conf

dnsentryname.local:5432:*:user:password
localhost:5432:*:postgres:password

Vervolgens:

pg_dump -h ipadres --column-inserts --data-only -t tablename -t othertablename --username=username --file=output.dmp --dbname=db -w

SQL: Update using a select

begin transaction;

update targettable
set "location" = CAST(ST_SetSRID( ST_Point(subquery.longitude_::float, subquery.latitude_::float), 4326) AS geography)
from (
select id, ajsonb -> 'location' as location_, ajsonb->'location'->'latitude' as latitude_, ajsonb->'location'->'longitude' as longitude_
from targettable) as subquery
where targettable.id = subquery.id
and subquery.location_ is not null

commit;
--rollback;