Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
ARRAY[('tag', 'some value'), (...)]::contactinfo[] - this should work. –  dezso Jan 21 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 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 at 18:39
    
Here is an example: sqlfiddle.com/#!15/5c776/4 –  a_horse_with_no_name Jan 21 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 at 21:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.