Re: [GENERAL] Enforcing referential integrity against a HSTORE column
On Sat, Jan 2, 2016 at 12:19 PM, Adrian Klaver wrote: > On 01/02/2016 08:13 AM, Dane Foster wrote: > > Ccing list. > >> On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver >> mailto:adrian.kla...@aklaver.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 form. 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.kla...@aklaver.com > Dane
Re: [GENERAL] Enforcing referential integrity against a HSTORE column
On 01/02/2016 08:13 AM, Dane Foster wrote: Ccing list. On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver mailto:adrian.kla...@aklaver.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 form. 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 So given: CREATE TABLE xtra_fields( xfk SERIAL PRIMARY KEY, xtk INTEGER NOT NULL REFERENCES xtra_types, ... ); CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$ WITH keyz AS (SELECT skeys($1)::INT AS xfk) SELECT (SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk)) = (SELECT COUNT(*) FROM keyz) $$LANGUAGE SQL STABLE STRICT LEAKPROOF; CREATE TABLE foo( id INTEGER NOT NULL CHECK (id > 0), ... -- Extra fields where the keys are the xtra_fields.xfk values and the values are the -- data values for the specific xfk. xtra hstore CHECK (foo_xtra_fk(xtra)) ); is there a more efficient way of maintaining logical referential integrity? Yes, use a table:) I guess it comes down to the first question above and what you are trying to achieve by moving to hstore. I use hstore and it is very handy for storing ad-hoc data, however when I want all the the RI whistle and bells I use table structures. The work has been done for me by folks who know a lot more about this then I and it is one less thing for me to code/worry about. I agree. I should keep the table. While thinking deeply about your question it dawned on me that I can have it both ways. So my new solution is to create a view that looks like the foo table. This way I get built-in referential integrity via foreign keys and a programmer friendlier view. Thanks for shaking up my perspective, Dane -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enforcing referential integrity against a HSTORE column
On Sat, Jan 2, 2016 at 10:59 AM, Bill Moran wrote: > On Sat, 2 Jan 2016 07:30:38 -0800 > Adrian Klaver wrote: > > > > So given: > > > > > > CREATE TABLE xtra_fields( > > >xfk SERIAL PRIMARY KEY, > > >xtk INTEGER NOT NULL REFERENCES xtra_types, > > >... > > > ); > > > > > > CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$ > > > WITH keyz AS (SELECT skeys($1)::INT AS xfk) > > > SELECT > > >(SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk)) > > >= > > >(SELECT COUNT(*) FROM keyz) > > > $$LANGUAGE SQL STABLE STRICT LEAKPROOF; > > > > > > CREATE TABLE foo( > > >id INTEGER NOT NULL CHECK (id > 0), > > >... > > > -- Extra fields where the keys are the xtra_fields.xfk values and the > > > values are the > > > -- data values for the specific xfk. > > >xtra hstore CHECK (foo_xtra_fk(xtra)) > > > ); > > > > > > is ?there a more efficient way of maintaining logical referential > integrity? > > I second Adrian's comment on making sure that the benefit of HSTORE is > outweighing the drawback of having to write your own checks ... however, > if you decide that HSTORE is the right way to go, you may want to try > something more along the lines of this for your check: > SELECT true WHERE NOT EXISTS(SELECT 1 FROM keyz WHERE xfk NOT IN > (akeys($1))); > I also agree w/ Adrian's comments and to that end I will be keeping the original tables and building a view that does what I need on top of them. But I really like your SQL solution. It is quite elegant and I suspect it would run faster than mine for a large enough data set. So I'll be filing it away in memory for use at some future point in time. > > Not tested, so it's possible that I have some typo or something; but > overall > I've found that the NOT EXISTS construct can be very efficient in cases > like these. > > -- > Bill Moran > Thanks, Dane
Re: [GENERAL] Enforcing referential integrity against a HSTORE column
On Sat, 2 Jan 2016 07:30:38 -0800 Adrian Klaver wrote: > > So given: > > > > CREATE TABLE xtra_fields( > >xfk SERIAL PRIMARY KEY, > >xtk INTEGER NOT NULL REFERENCES xtra_types, > >... > > ); > > > > CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$ > > WITH keyz AS (SELECT skeys($1)::INT AS xfk) > > SELECT > >(SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk)) > >= > >(SELECT COUNT(*) FROM keyz) > > $$LANGUAGE SQL STABLE STRICT LEAKPROOF; > > > > CREATE TABLE foo( > >id INTEGER NOT NULL CHECK (id > 0), > >... > > -- Extra fields where the keys are the xtra_fields.xfk values and the > > values are the > > -- data values for the specific xfk. > >xtra hstore CHECK (foo_xtra_fk(xtra)) > > ); > > > > is ?there a more efficient way of maintaining logical referential integrity? I second Adrian's comment on making sure that the benefit of HSTORE is outweighing the drawback of having to write your own checks ... however, if you decide that HSTORE is the right way to go, you may want to try something more along the lines of this for your check: SELECT true WHERE NOT EXISTS(SELECT 1 FROM keyz WHERE xfk NOT IN (akeys($1))); Not tested, so it's possible that I have some typo or something; but overall I've found that the NOT EXISTS construct can be very efficient in cases like these. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enforcing referential integrity against a HSTORE column
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? So given: CREATE TABLE xtra_fields( xfk SERIAL PRIMARY KEY, xtk INTEGER NOT NULL REFERENCES xtra_types, ... ); CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$ WITH keyz AS (SELECT skeys($1)::INT AS xfk) SELECT (SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk)) = (SELECT COUNT(*) FROM keyz) $$LANGUAGE SQL STABLE STRICT LEAKPROOF; CREATE TABLE foo( id INTEGER NOT NULL CHECK (id > 0), ... -- Extra fields where the keys are the xtra_fields.xfk values and the values are the -- data values for the specific xfk. xtra hstore CHECK (foo_xtra_fk(xtra)) ); is there a more efficient way of maintaining logical referential integrity? Yes, use a table:) I guess it comes down to the first question above and what you are trying to achieve by moving to hstore. I use hstore and it is very handy for storing ad-hoc data, however when I want all the the RI whistle and bells I use table structures. The work has been done for me by folks who know a lot more about this then I and it is one less thing for me to code/worry about. Thank you for your consideration, Dane -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Enforcing referential integrity against a HSTORE column
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 So given: CREATE TABLE xtra_fields( xfk SERIAL PRIMARY KEY, xtk INTEGER NOT NULL REFERENCES xtra_types, ... ); CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$ WITH keyz AS (SELECT skeys($1)::INT AS xfk) SELECT (SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk)) = (SELECT COUNT(*) FROM keyz) $$LANGUAGE SQL STABLE STRICT LEAKPROOF; CREATE TABLE foo( id INTEGER NOT NULL CHECK (id > 0), ... -- Extra fields where the keys are the xtra_fields.xfk values and the values are the -- data values for the specific xfk. xtra hstore CHECK (foo_xtra_fk(xtra)) ); is there a more efficient way of maintaining logical referential integrity? Thank you for your consideration, Dane
