CODEWITHSUNDEEP
jsonpostgresqlpsqlpg-dump
writes_on
writes_on

Reputation: 1885

psql can't import pg_dump file containing JSON

I'm trying to import a file created using the pg_dump Postgres tool into another database (same table structure as the source), and am having problems. The file produced by pg_dump has JSON data in it. When I try to load the data into Postgres using psql I get errors. Here's a simplified version of the machine.sql produced by pg_dump where the attr field is the json:

COPY equipment.machine (machine_id, name, attr) FROM stdin;
1   cutter  {"feed": ["Roll Fed Doug"], "colors": [1]}
\.

I'm using this command to load the file into the database:

psql <connection string/database> < machine.sql

When I run this command I get the following output errors:

COPY equipment.machine (machine_id, name, definition) FROM stdin;
ERROR:  P0002: query returned no rows
CONTEXT:  PL/pgSQL function equipment.machine_check_definition() line 5 at SQL statement
COPY machine, line 1: "1     cutter..."
LOCATION:  exec_stmt_execsql, pl_exec.c:3787

If I make a copy of the destination table and remove the JSONB field from that table, and copy my machine.sql file to another name and remove the son field from that file, then run the same psql command with the modified file, it works fine.

This makes me think the problem is related to the JSON data, but so far I haven't figured out how to determine what the problem is or how to get psql to accept the data.

Upvotes: 0

Views: 1159

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247535

You have a trigger function or a CHECK constraint calling a function on the table, and that function contains a SELECT ... INTO STRICT ... that returns an empty result.

You'll need to fix the function so that it doesn't throw an error.

If it is a CHECK constraint, you are doing the wrong thing anyway: such a constraint has no business accessing other data than the new row itself, otherwise that is a problem waiting to happen (as it just did).

Upvotes: 1

Related Questions