How to manipulate bytea data when using backup script as a template

Question

I have a project with a web app that has many configurable options, with the configurations stored in a PostgreSQL database. Most fields are text or ints, but there are a couple configurations stored as JSON decoded into bytea.

We have one "default configuration" setup script that my team (Software Quality Assurance) utilizes to put everyone's testing server on the same page. The script takes a few hours to manually create data and store configurations via the web app. I'd like to save our team hours of setup time by simply sharing a SQL file with the create / insert statements necessary to get the database to the same configuration in way less time.

So with a script such as:

CREATE DATABASE example;
...
CREATE TABLE example_table (
 id integer NOT NULL,
 ip_address text,
 domain text,
 configuration bytea
);
...
COPY example_table (id, ip_address, domain, configuration) FROM stdin;
1 0.0.0.0 invalid.domain.com \x7b277265666572656e63655f746f5f69705f61646472657373273a27302e302e302e30272c277265666572656e63655f746f5f646f6d61696e273a27696e76616c69642e646f6d61696e2e636f6d277d
\.
--- The above bytearray is "{'reference_to_ip_address':'0.0.0.0','reference_to_domain':'invalid.domain.com'}"

Originally, my thought was to replace "0.0.0.0" and "invalid.domain.com" with whatever the user wanted, and then the user would execute the script to setup their database with the common configuration. However, with the encoded data, the end result would have invalid references to the server's IP address and domain. Has anyone ever had a problem like this and figured out a solution? Is there a better way of doing this? I'm open to any suggestions other than changing the database organization (I can't swing that one).

Answer 1

Instead of using...

COPY example_table (id, ip_address, domain, configuration) FROM stdin;
1 0.0.0.0 invalid.domain.com \x7b277265666572656e63655f746f5f69705f61646472657373273a27302e302e302e30272c277265666572656e63655f746f5f646f6d61696e273a27696e76616c69642e646f6d61696e2e636f6d277d
\.

... I think you should be able to change your pattern, and do something like ...

INSERT INTO 
 example_table 
 (id, ip_address, domain, configuration) 
VALUES 
 (1, '0.0.0.0', 'invalid.domain.com', 
 $quote${"reference_to_ip_address":'0.0.0.0',
 "reference_to_domain":'invalid.domain.com'}$quote$::bytea) ;

... or, if you want to make things a little simpler, use a Common Table Expression, and you will have to change settings only in ONE place:

WITH reference_data(id, ip_address, domain) AS
(
 -- This is the only place that needs changing...
 VALUES (1, '0.0.0.0', 'invalid.domain.com')
)
INSERT INTO
 example_table 
 (id, ip_address, domain, configuration) 
SELECT
 id, ip_address, domain, 
 ('{"reference_to_ip_address":''' || ip_address || ''',' ||
 '"reference_to_domain":''' || domain || '''}')::bytea 
FROM
 reference_data ;

You can check the data

 SELECT 
 id, ip_address, domain, convert_from(configuration, 'utf8')
 FROM
 example_table ;

 id | ip_address | domain | convert_from

 -: | :--------- | :----------------- | :-------------------------------------------------------------------------------
 1 | 0.0.0.0 | invalid.domain.com | {"reference_to_ip_address":'0.0.0.0',"reference_to_domain":'invalid.domain.com'}
 1 | 0.0.0.0 | invalid.domain.com | {"reference_to_ip_address":'0.0.0.0',"reference_to_domain":'invalid.domain.com'}