I'm working on a script that has to dump app data from a schema to json files. Here is what I have thus far:

export SCHEMA="citydb"
export DB="kv_db"
export PGPASSWORD=root

psql -U postgres -Atc "select tablename from pg_catalog.pg_tables where schemaname='$SCHEMA'" $DB |\
  while read TBL; do
    name=${TBL::-1} # for some reason a whitespace character is appended to the end of each name
    echo -e "Table name: $name";
    touch $name".json"
    psql -U postgres \o $name".json"
    psql -U postgres -c "COPY (SELECT row_to_json(t) FROM $SCHEMA.$name as t) TO 'c:/users/me/desktop/$name.json'" $DB > $name.json

I'm pretty much building up on information about how to export to csv's since I didn't find any information about dumping all the data to json.

I'm getting an error that says:

psql: warning: extra command-line argument "plant_cover.json" ignored
psql: error: FATAL:  database "o" does not exist
ERROR:  could not open file "c:/users/me/desktop/plant_cover.json" for writing: Permission denied
HINT:  COPY TO instructs the PostgreSQL server process to write a file. You may want a client-side facility such as psql's \copy.

Any ideas?

  • Hi, and welcome to dba.se! I'm baffled as to why you'd want to do this, but maybe you'd be better off exporting as .csv and then use an external tool to convert this to json? Not sure of my ground here, but if you want to do this within the server, you'd be better off using PL/pgSQL? Getting correct JSON is not all that easy - and ROW_TO_JSON doesn't actually produce correct JSON for a table - see here...
    – Vérace
    Commented Aug 5, 2021 at 13:16
  • Hello, and thanks for the welcome. I actually have a valid usecase for this. I need to use the data from the database to do a 3D visualization of some buildings via a library that understands JSON. In other words - I have the data, only thing is that it is not in the correct format. Thanks for your suggestings, I will look into them :)
    – tdranv
    Commented Aug 5, 2021 at 13:36


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.