Hello,
insert into my_table(data) values (pg_read_file('${local_file}')::jsonb)
on conflict do nothing;
SQL
I'm trying to build a few data pipelines with Unix tools but can't figure out how to insert in a slurp mode (e.g. not COPY line by line) content inside a variable.
Consider the following script (using a heredoc)
json_url="https://....file.json"
local_file="/tmp/a.json"
curl -s -m 10 -A 'Mozilla/5.0 (X11; Linux x86_64; rv:30.0) Gecko/20100101 Firefox/30.0' \
--max-redirs 0 -o ${local_file} ${json_url}
psql "$PG_URI" -qAt <<SQL
local_file="/tmp/a.json"
curl -s -m 10 -A 'Mozilla/5.0 (X11; Linux x86_64; rv:30.0) Gecko/20100101 Firefox/30.0' \
--max-redirs 0 -o ${local_file} ${json_url}
psql "$PG_URI" -qAt <<SQL
create table if not exists (data jsonb);
insert into my_table(data) values (pg_read_file('${local_file}')::jsonb)
on conflict do nothing;
SQL
The question is, how can I achieve the same result, without having to hit the disk due. to the temporary file.
I tried running by using pg_read_file('/dev/stdin')::jsonb
