Hatena::ブログ(Diary)

Dieu merci


2019-01-27

how to get and put pdf in bytea (postgres)

only using sql and sql-command

<upload>

0)main table to store binary file below
prototype=# \d  storedpdf
                  Table "public.storedpdf"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 id          | integer           |           |          | 
 name    | character varying |           |          | 
 pdf        | bytea             |           |          | 

1)create temporary table

prototype=# create table media(val text);

2)copy hex text stream to table(text)

cat /test/get.pdf | xxd -p | tr -d '\n' | psql -d prototype -c "\COPY media(val)  FROM STDIN"

3)convert text to binary by decode function

prototype=# insert into storedpdf(pdf) select decode(val,'hex') from media;

<download>

psql -d prototype -c "\COPY (select pdf from storedpdf ) TO STDOUT" | xxd -r -p>get.pdf

トラックバック - http://d.hatena.ne.jp/annazola/20190127/p1
リンク元