xxd_and_postgresql_cheatsheet.md

Sometimes I need to copy some text file into the DB as bytea or get bytea from the DB as text file.

For the examles I'll use the Firebase credentials file (in JSON format).

Table

CREATE TABLE system_properties (
  "name" varchar NOT NULL,
  value varchar NULL,
  data_value bytea NULL
);

Copy file to DB as bytea:

Transform the file to hex using xxd:

xxd -p /tmp/fcm_credentials.json | tr -d '\n' > /tmp/fcm_credentials.hex

Then insert the file into the DB (using intermediate table):

CREATE TABLE hexdump (hex text);

\copy hexdump FROM '/tmp/fcm_credentials.hex';

INSERT INTO system_properties ("name", value, data_value) VALUES ('FCM_CREDENTIALS', NULL, (SELECT decode(hex, 'hex') FROM hexdump));

DROP TABLE hexdump;

Read bytea from DB in original format:

In psql:

\copy (SELECT encode(data_value, 'hex') FROM system_properties WHERE name = 'FCM_CREDENTIALS') TO '/tmp/fcm_credentials.hex';

Then in shell:

~> xxd -p -r /tmp/fcm_credentials.hex > /tmp/fcm_credentials.json