Sunday, February 19, 2017

new command line tool pgimportdoc

More, more times I had to import some XML documents to Postgres. How do it simply? More, some XML documents are not in UTF8 encoding, so some conversion and modification is necessary.

<?xml version="1.0" encoding="windows-1250"?>
<enprimeur>
     <vino>
         <id>1</id>
         <nazev>Alter Ego de Palmer</nazev>
         <vyrobce>63</vyrobce>
         <rocnik>2012</rocnik>
         <cena0375>0</cena0375>
         <cena1500></cena1500>
         <cena3000>0</cena3000>

It is not a hard work, but it is manual work, and it is terrible work, because PostgreSQL has enough functionality, but this functionality is not accessible from psql console.

I wrote simple tool pgimportdoc, that can helps with import any text, json, binary (to bytea field) or XML document to Postgres.

Usage:
cat ~/Stažené/enprimeur.xml | ./pgimportdoc postgres -c 'insert into xmldata values($1)' -t XML
./pgimportdoc postgres -f ~/Stažené/enprimeur.xml -c 'insert into xmldata values($1)' -t XML

Supported formats are XML, BYTEA and TEXT. The TEXT format can be used for text, json, jsonb target formats.

Link: https://github.com/okbob/pgimportdoc