On 01/02/2016 08:13 AM, Dane Foster wrote:
Ccing list.
On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
