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

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":...}, ...],
}, ...]

for a query such as SELECT * FROM orders_suppliers_contacts.

  • 1
    ARRAY[('tag', 'some value'), (...)]::contactinfo[] - this should work. – dezso Jan 21 '15 at 16:55
  • 1
    It seems you'd better use a JSON column directly or store the contactinfo in a hstore column. An array like that will be quite hard to handle. The hstore column will be much more flexible when you need to remove or add elements. – a_horse_with_no_name Jan 21 '15 at 16:55
  • Could you elaborate on that? I've never worked with hstore and have only played with pg for only a year. – Yanick Rochon Jan 21 '15 at 18:39
  • Here is an example: sqlfiddle.com/#!15/5c776/4 – a_horse_with_no_name Jan 21 '15 at 21:14
  • @a_horse_with_no_name thank you for the example. I am reading that, since JSON is now built-in, and hstore is an extension, some prefer JSON. Also, the adapter that I'm using for the project does not support hstore at the moment. Wich means that I'll probably go with a JSON object after all. – Yanick Rochon Jan 21 '15 at 21:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.