One of the table I have looks like this
CREATE TABLE orders_suppliers_contacts
(
supplier_id bigint NOT NULL,
name character varying(64) NOT NULL,
phone character varying(18),
notes character varying(2000),
last_modified timestamp without time zone NOT NULL,
CONSTRAINT "FK_orders_suppliers_contacts" FOREIGN KEY (supplier_id)
REFERENCES orders_suppliers (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
I need to change the field phone character varying
into contacts contactinfo[]
whose custom type is defined as
CREATE TYPE contactinfo AS (
tag VARCHAR(8),
"value" VARCHAR(64)
)
What would be the insert query for that?
INSERT INTO orders_suppliers_contacts VALUES
(1, 'John Smith', ???, null, NOW());
Also, once retrieved, the data is sent through a web API as JSON, will the column need to pass through a JSON function or is it already compatible? The required output (HTTP response) should be, something like
[{
"supplier_id": 1,
"name": "John Smith",
"contact": [{"tag":..., "value":...}, ...],
"notes":...,
"last_modified":...
}, ...]
for a query such as SELECT * FROM orders_suppliers_contacts
.
ARRAY[('tag', 'some value'), (...)]::contactinfo[]
- this should work. – dezso Jan 21 at 16:55JSON
column directly or store the contactinfo in ahstore
column. An array like that will be quite hard to handle. Thehstore
column will be much more flexible when you need to remove or add elements. – a_horse_with_no_name Jan 21 at 16:55hstore
and have only played with pg for only a year. – Yanick Rochon Jan 21 at 18:39hstore
is an extension, some prefer JSON. Also, the adapter that I'm using for the project does not supporthstore
at the moment. Wich means that I'll probably go with a JSON object after all. – Yanick Rochon Jan 21 at 21:54