Re: Enforcing referential integrity against a HSTORE column

On Sat, Jan 2, 2016 at 12:19 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 01/02/2016 08:13 AM, Dane Foster wrote:
>
> Ccing list.
>
>> On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver
>> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> On 01/01/2016 07:47 PM, Dane Foster wrote:
>>
>> Hello,
>>
>> I'm moving a MySQL database to PostgreSQL and redesigning parts
>> of it to
>> take advantage of PostgreSQL's richer type system and other
>> advance
>> features. Currently I am attempting to replace a table of
>> name/value
>> pair data w/ a hstore column. But now that the data will no
>> longer be
>> flattened out in a table I need to manually handle referential
>> integrity
>>
>>
>> And the benefit is?
>> ​
>>
>> ​
>> The benefit is supposed to be client side simplicity. The data in these
>> particular tables are ultimately consumed by JavaScript as JSON on the
>> front end to populate/maintain a dynamic HTML for​m. So I was attempting
>> to build a model that more closely reflects how the data is used because
>> the people using the data aren't SQL folks and the code that converts
>> the data from table/rows to JSON is not straight forward for my audience.
>>
>
> In that case you may want to look at the JSON types, json and/or
> jsonb(depending on Postgres version):
>
>
> http://www.postgresql.org/docs/9.4/interactive/datatype-json.html
>
> ​That's exactly what I did. The json_object_agg function sealed the deal.​

> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

Dane
​