Обсуждение: Postgres read jsonb content from stdin
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
2020年12月26日(土) 20:19 Markur Sens <markursens@gmail.com>: > > Hello, > > 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 COPYline 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 > 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 It can be done like this: $ curl http://localhost/json.txt {"bar": "baz", "balance": 7.77, "active": false} $ psql -v jsonval="`curl -s http://localhost/json.txt`" -d 'host=localhost dbname=postgres user=postgres' <<SQL INSERT INTO json_test values(:'jsonval') SQL INSERT 0 1 Time: 0.374 ms though TBH if I were doing that on a regular basis, I'd do it via a script which could cope with errors retrieving the remote file, etc. If the data source (URL) is constant, you could try something along these lines with file_fdw: CREATE EXTENSION file_fdw; CREATE SERVER json_curl FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE json_src ( json_data jsonb ) SERVER json_curl OPTIONS ( PROGRAM 'curl -s http://localhost/json.txt' ); Better alternatives may be available. Regards Ian Barwick -- EnterpriseDB: https://www.enterprisedb.com
Hello,
Hadn't really thought of using a foreign table up at this point...
thanks for that.
Will the first solution handle formatting issues (e.g. single quotes) gracefully?
I think I'd tried it in the past and it didn't work.
PD: I have such a script that handle's the intricacies but it'd still emit to stdout. (hence the curl simplified in the example)
On Sat, Dec 26, 2020 at 2:40 PM Ian Lawrence Barwick <barwick@gmail.com> wrote:
2020年12月26日(土) 20:19 Markur Sens <markursens@gmail.com>:
>
> Hello,
>
> 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
> 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
It can be done like this:
$ curl http://localhost/json.txt
{"bar": "baz", "balance": 7.77, "active": false}
$ psql -v jsonval="`curl -s http://localhost/json.txt`" -d
'host=localhost dbname=postgres user=postgres' <<SQL
INSERT INTO json_test values(:'jsonval')
SQL
INSERT 0 1
Time: 0.374 ms
though TBH if I were doing that on a regular basis, I'd do it via a script which
could cope with errors retrieving the remote file, etc.
If the data source (URL) is constant, you could try something along these lines
with file_fdw:
CREATE EXTENSION file_fdw;
CREATE SERVER json_curl FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE json_src (
json_data jsonb
)
SERVER json_curl
OPTIONS (
PROGRAM 'curl -s http://localhost/json.txt'
);
Better alternatives may be available.
Regards
Ian Barwick
--
EnterpriseDB: https://www.enterprisedb.com
On Sat, Dec 26, 2020 at 3:19 AM Markur Sens <markursens@gmail.com> wrote:
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.<snip>The question is, how can I achieve the same result, without having to hit the disk due. to the temporary file.