1

I'm new at PostgreSQL. I'm trying to import JSON file into PostgreSQL table. I created an empty table:

covid19=# CREATE TABLE temp_cov(
covid19(# data jsonb
covid19(# );

and tried to copy my data from JSON in this table with this command in Command line:

cat output.json | psql -h localhost -p 5432 covid19 -U postgres -c "COPY temp_cov (data) FROM STDIN;"

The output was just "COPY 1" and when I open my table in psql with

SELECT * FROM temp_cov;

But this command goes without an end and with this output.

output

Unfortunately, I couldn't find an answer or some similar problem solution. Thank you in advance for your advices.

Also my json file is already modified to "not pretty" form and it has over than 11k lines.

Anastasia Tiu
  • 21
  • 1
  • 4
  • Does your json file end with a newline? – Bergi Jul 31 '20 at 01:41
  • Have a look at https://dba.stackexchange.com/questions/8172/sql-to-read-xml-from-file-into-postgresql-database and https://dba.stackexchange.com/questions/1742/how-to-insert-file-data-into-a-postgresql-bytea-column. You can probably do something similar for JSON. You can also use `\lo_import` with `psql`. – Bergi Jul 31 '20 at 01:50
  • Does this answer your question? [How can I import a JSON file into PostgreSQL?](https://stackoverflow.com/questions/39224382/how-can-i-import-a-json-file-into-postgresql) – Bergi Jul 31 '20 at 01:55
  • @Bergi no, my json file doesn't end with a newline. It also shouldn't, isn't it? Thank you for your help! I've tried 1000 of ways already and will look links you've sent! – Anastasia Tiu Jul 31 '20 at 02:07
  • Yeah, you're right, it doesn't need a newline just the end-of-file. Judging from [this answer](https://stackoverflow.com/a/57445995/1048572) your approach should work, and the output `COPY 1` suggests that one row was inserted. Why it would come up empty afterward I have no idea. – Bergi Jul 31 '20 at 02:09
  • "*But this command goes without an end*" - ah, it doesn't actually say there are 0 rows but says it skips displaying the whole content. (I had missed your edit.) I think the import has just worked, now you need to work with the data. Try selecting some small part of the JSON object, try inserting objects as records in tables, do your aggregations, and then print the output of that. – Bergi Jul 31 '20 at 02:12
  • Also try `SELECT COUNT(*) FROM temp_cov;` or `SELECT json_typeof(data) FROM temp_cov;` – Bergi Jul 31 '20 at 02:13
  • `COPY 1` means, one row was inserted. `psql` tries to format the output, that's where your "weird" output comes from. –  Jul 31 '20 at 05:35

1 Answers1

0

Your data is there. psql is sending the row to the pager (likely more?), and the pager can't deal with it very usably because it is too big. You can turn off the pager (\pset pager off inside psql) or set the pager to a better program (PAGER=less or PSQL_PAGER=less as environment variables), but really none of those is going to be all that useful for viewing giant JSON data.

You have your data in PostgreSQL, now what do you want to do with it? Just looking at it within psql's pager is unlikely to be interesting.

jjanes
  • 37,812
  • 5
  • 27
  • 34