3 ms·
Thanks for the info - I'm thinking more along the lines of constraining the data within the valid json. Just found this post which shows how you can do it.
by aidos 13y ago
Thanks for the info - I'm thinking more along the lines of constraining the data within the valid json.
Just found this post which shows how you can do it.
CREATE TABLE products (
data JSON,
CONSTRAINT validate_id CHECK ((data->>'id')::integer >= 1 AND (data->>'id') IS NOT NULL ),
CONSTRAINT validate_name CHECK (length(data->>'name') > 0 AND (data->>'name') IS NOT NULL ),
CONSTRAINT validate_description CHECK (length(data->>'description') > 0 AND (data->>'description') IS NOT NULL ),
CONSTRAINT validate_price CHECK ((data->>'price')::decimal >= 0.0 AND (data->>'price') IS NOT NULL),
CONSTRAINT validate_currency CHECK (data->>'currency' = 'dollars' AND (data->>'currency') IS NOT NULL),
CONSTRAINT validate_in_stock CHECK ((data->>'in_stock')::integer >= 0 AND (data->>'in_stock') IS NOT NULL )
}
http://blog.endpoint.com/2013/06/postgresql-as-nosql-with-data-validation.html http://blog.endpoint.com/2013/06/postgresql-as-nosql-with-da...
- richardwhiuk 13y agoWhy would you ever want to do this? Surely at this point you are better with a table with id, name, description, price, currency, in_stock and an extra data field for anything else - given you require the other fields.
- jlouis 13y agoDepends on how much the data is going to be altered. Changing columns is not a free operation in data stores. They require you to rewrite all the rows in many cases. This can be more dynamic short term.
- aidos 13y agoMaybe - I need to test the performance, but for my application breaking things out into a table is a little slow. In all probability the constraints will be slower, but it's something to try. At the moment I have all my data in mongo but over time it's fallen out of shape. There's a large chunk of it that just needs to be stores (json field) but it would be nice to constrain some of it.
- aychedee 13y agoOh cool, that's very interesting! Thanks for sharing.