3 ms·
The other answers have confirmed that Postgres will do this with array fields, and it's good advice to follow. It's also in my view much easier to read than Mon
by matthewmacleod 8y ago
The other answers have confirmed that Postgres will do this with array fields, and it's good advice to follow. It's also in my view much easier to read than MongoDB's query language is!
CREATE TABLE documents (name text, tags text[]);
INSERT INTO documents VALUES ('Doc1', '{tag1, tag2}');
INSERT INTO documents VALUES ('Doc2', '{tag2, tag3}');
INSERT INTO documents VALUES ('Doc3', '{tag2, tag3, tag4}');
SELECT * FROM documents WHERE tags @> '{tag1}';
name | tags
------+-------------
Doc1 | {tag1,tag2}
SELECT * FROM documents WHERE tags @> '{tag2}';
name | tags
------+-------------
Doc1 | {tag1,tag2}
Doc2 | {tag2,tag3}
Doc3 | {tag2,tag3,tag4}
SELECT * FROM documents WHERE tags @> '{tag2, tag3}';
name | tags
------+------------------
Doc2 | {tag2,tag3}
Doc3 | {tag2,tag3,tag4}
Postgres certainly isn't perfect, but it's usually a good answer to "how do I store and query data" where you don't have any particular specialist requirements.
- tnolet 8y agoUsing this almost 1:1 in a production app where customers can filter by tags. Works great.
- willvarfar 8y agoPostgres array columns are super neat! As I already use MySQL in many projects, I do much the same thing with MySQL's json support. I believe json is now supported by Sqlite too. Basically, if you already have an up-to-date relational database, then the chances are you can already use it as a sane document store. Just saying, so nobody reading the threads thinks they have to go grab some new database if they are already using one!