Is there a way to export postgres table data as json to a file? I need the output to be line by line, like:


EDIT: postgres version: 9.3.4


Try here for a basic intro to PostgreSQL and JSON.

Also, PostgreSQL documentation is pretty good, so try it here. Check out the pretty_bool option.

Your original question was "Is there a way to export postgres table data as JSON". You wanted it in this format


I didn't have a running instance of PostgreSQL so I downloaded, compiled and installed 9.4.

To answer this, I first CREATEed a table (fred)

CREATE TABLE fred (mary INT, jimmy INT, paulie VARCHAR(20));

INSERT INTO fred VALUES (2,    43, 'asfasfasfd'      );
INSERT INTO fred VALUES (3,   435, 'ererere'         );
INSERT INTO fred VALUES (6, 43343, 'eresdfssfsfasfae');

Then, to check:

test=# select * from fred;

 mary | jimmy |      paulie      
    2 |    43 | asfasfasfd
    3 |   435 | ererere
    6 | 43343 | eresdfssfsfasfae

Then I issued this command

test(# FROM (SELECT * FROM fred) t) 
test-# TO '/paulstuff/sware/db/postgres/inst/myfile';

I then quit psql and listed the file myfile.

test=# \q
[pol@polhost inst]$ more myfile 
[pol@polhost inst]$

(you can experiment with the output from

COPY (SELECT ROW_TO_JSON(t, TRUE)  -- <-- Note addition of "TRUE" here!

at your leisure).

It was pointed out by @offby1 that the output (while corresponding to the OP's question) is not correct JSON. @EvanCarroll pointed out that \o is also a way of outputting to a file, so I combined the solutions to these two niggles in this statement (with help from here):

test=# \o out.json
test=# SELECT array_to_json(array_agg(fred), FALSE) AS ok_json FROM fred;
                                     -- <-- "TRUE" here will produce plus
                                        ("+) signs in the output. "FALSE"
                                        is the default anyway.
test=# \o


[pol@polhost inst]$ more out.json 
(1 row)
[pol@polhost inst]$ 

FINALLY, there is the backslash (\) problem alluded to by @AdamGent in his post. This was a bit tricky, but it is possible without resorting to post-query processing. Voilà:

INSERT INTO fred VALUES (35, 5, 'wrew\sdfsd');
INSERT INTO fred VALUES (3, 44545, '\sdfs\\\sfs\\gf');

And using REGEXP_REPLACE thus (note the cast ::TEXT) removes the excess blackslashes.

test=# \o slash.json
test=# SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '\\\\', '\\', 'g') 
test=# FROM (SELECT * FROM fred) AS t;  -- I found that using a CTE was helpful for legibility
test=# \o
test=# \q


[pol@polhost inst]$ more slash.json 
(5 rows)
[pol@polhost inst]$ 

(p.s. As for @Zoltán 's comment - this may be a version thing - unable to reproduce!).

  • 2
    That seems to be exactly what the original poster wanted. Note, however, that while each row is proper JSON, the collection of rows isn't, since it lacks commas separating the rows, and square brackets surrounding them.
    – offby1
    Apr 30 '16 at 16:44
  • 3
    This will NOT work if you have any backslash in your columns!!!! Read carefully the COPY doc as it does special things for backslash characters (like adding another backslash).
    – Adam Gent
    Aug 25 '16 at 2:37
  • READ @AdamGent 's answer below to solve the backslash issue
    – FacePalm
    Feb 8 '17 at 7:29
  • 1
    So... year 2017 and NO WAY TO EXPORT JSON with COPY command PostgreSQL?? There are CSV option, TXT option... Why not an JSON option? Nov 5 '17 at 11:50
  • 1
    Thanks @Vérace. And sorry, now I tested a COPY with complex JSONb and the proceded JSON was fine, "proper JSON"! Nov 5 '17 at 12:15

If you're using psql then there is no reason to use \COPY at all.

\o file.json
SELECT row_to_json(r) FROM my_table AS r;

This is the same method we use to get png/jpgs/tifs out of the database with PostGIS for quick tests, and also to generate script files with PostgreSQL extensions.

  • 1
    Great! As usual COPY command "not allow relative path", the psql-native-commands is the easiest way to copy into relative path! PS: there are a "terminal way" to use real COPY command with relative path, see here. psql -h remotehost -d remote_mydb -U myuser -c "COPY (SELECT '{\"x\":1,\"y\":[\"a\",2]}'::json AS r) TO STDOUT" > ./relative_path/file.csv Nov 5 '17 at 12:28
  • Note that the output in the file file.json is not JSON, rather, ever line in that file is a JSON object that represents a row. But the file altogether is not valid JSON, you would need to wrap the whole file in between [ and ] and add the needed commas at the end of lines.
    – Flimm
    Sep 25 '20 at 17:01

Please find below the only answer that outputs actually valid JSON (i.e. array of objects).

\o data.json
select json_agg(t) FROM (SELECT * from table) t;


  • Should be higher up. Thank you!
    – backus
    Jul 13 at 18:43

For me @Vérace's answer didn't maintain the column names, but assigned default names (f1, f2, etc.) instead. I am using PostgreSQL 9.1 with the JSON extension.

If you want to export the entire table, there is no need for a subquery. In addition, this will maintain the column names. I used the folowing query:

COPY (SELECT row_to_json(t) FROM fred as t) to '/home/pol/Downloads/software/postgres/inst/myfile';
  • 1
    It did maintain column names! CREATE TABLE fred (mary INT, jimmy INT, paulie VARCHAR(20)); and the result: {"mary":2,"jimmy":43,"paulie":"asfasfasfd"} - field names are mary, jimmy, paulie... and NOT (f1, f2, etc.)...
    – Vérace
    Nov 30 '17 at 20:58

I will add a special caveat to Verace's answer. You need to do post processing on the outputted JSON file if you have text columns with backslash characters: \.

Otherwise you will get duplicate (\ -> \\) at best and completely invalid JSON at worse ie:


{ "f1" : "crap\""}.


{ "f1" : "crap\\""}.

Which looks fine but is completely invalid JSON.

You can replace the \\ into \ with sed:

sed -i -e 's/\\\\/\\/g' PG_OUT_JSON_FILE.json

From Postgres COPY where they round about mention it:

Presently, COPY TO will never emit an octal or hex-digits backslash sequence, but it does use the other sequences listed above for those control characters. Any other backslashed character that is not mentioned in the above table will be taken to represent itself. However, beware of adding backslashes unnecessarily, since that might accidentally produce a string matching the end-of-data marker (.) or the null string (\N by default). These strings will be recognized before any other backslash processing is done.

It is strongly recommended that applications generating COPY data convert data newlines and carriage returns to the \n and \r sequences respectively. At present it is possible to represent a data carriage return by a backslash and carriage return, and to represent a data newline by a backslash and newline. However, these representations might not be accepted in future releases. They are also highly vulnerable to corruption if the COPY file is transferred across different machines (for example, from Unix to Windows or vice versa).

COPY TO will terminate each row with a Unix-style newline ("\n"). Servers running on Microsoft Windows instead output carriage return/newline ("\r\n"), but only for COPY to a server file; for consistency across platforms, COPY TO STDOUT always sends "\n" regardless of server platform. COPY FROM can handle lines ending with newlines, carriage returns, or carriage return/newlines. To reduce the risk of error due to un-backslashed newlines or carriage returns that were meant as data, COPY FROM will complain if the line endings in the input are not all alike.

  • I have dealt with this one in the answer - I hope you find it satisfactory. If not, let me know.
    – Vérace
    Sep 9 '19 at 13:58

For a generic (MySQL, Postgres, SQLite..) and free solution that you don't have to install any software for (except Docker), see https://github.com/function61/sql2json

Full disclosure: I wrote that software.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.