6 ms·
PostgreSQL 9.2 beta adds JSON
- agotterer 14y agoWill the JSON type have document indexing or is it just a text store that will validate that it's JSON.
- pyrotechnick 14y agoLooks like it's just validation: http://www.postgresql.org/docs/devel/static/datatype-json.htm http://www.postgresql.org/docs/devel/static/datatype-json.ht...
- sickpig 14y agojust validation, json core type it's intentionally very minimal (see postgres hackers mailing list archives for more details) anyway as a possible workaround just see andrew dustan's post: http://people.planetpostgresql.org/andrew/index.php?/archives/249-Using-PLV8-to-index-JSON.html http://people.planetpostgresql.org/andrew/index.php?/archive...
- fusiongyro 14y agoYou don't necessarily need special support for that. There's no special XML indexing support, but you can use the XPath and XSLT functions to extract the data of interest and index that: CREATE TABLE foo (a_doc xml); CREATE INDEX doc_title ON foo ((xpath('/root@title', a_doc)); I don't see any JSON functions for extracting data in the devel documentation, but that's what you would be waiting on, and it could probably be added by an extension without having to wait for built-in support.
- pygy_ 14y agoThere's no native function, but it can easily be implemented. See sickpig's answer (sibling to your post) for a Javascript-based solution.
- diminish 14y agoI was suddenly excited for a JSON API support directly from PostgreSQL, been too naive. But I am still certain about the future.
- deleted 14y ago[deleted]
- deleted 14y ago[deleted]
- gphil 14y agoI think the bigger deal (at least for most existing deployments) are the index-only scans. This is one of the only features of Oracle that I missed after switching to Postgres.
- benhoyt 14y agoCan you please explain index-only scans, and what the advantages are (or why this isn't the default already)?
- gphil 14y agoSure. It's not the default already because the functionality did not exist at all in previous versions. Say you have a table with a bunch of columns: (column1...column15) and you have an index on (column3, column4) and you run a query like: select column4 from table where column3 = x; the DB will only read from the index (and not look at the table at all.) This is much faster as the index is much smaller in size than the table.
- benhoyt 14y agoThanks, that's interesting. So it only kicks in when your index covers all the columns in the "what" (select) clause. That tends not to be true for tables I've designed/worked with. I guess you could index differently to take advantage of this though.
- jeltz 14y agoYes, databases which support index-only scans tend to encourage this pattern of adding extra columns in your indexes. In some cases this can be a big gain, but larger indexes means that the indexes are slower for queries which do not need the extra columns or need to read the entire row anyway. Another set of queries which benefit from index only scans are those with EXISTS or JOINs just used for filtering the result set. In PostgreSQL 9.1 they always needed to read part of the actual row to check the visibility information. In 9.2 it will for VACUUMED pages only need to check the index and the visibility map.
- brlewis 14y agoMore discussion at http://news.ycombinator.com/item?id=3472177 http://news.ycombinator.com/item?id=3472177
- fauigerzigerk 14y agoCovering indexes is really good news!
- stephen 14y agoWow, range types with non-overlapping constraints: http://www.postgresql.org/docs/devel/static/rangetypes.html http://www.postgresql.org/docs/devel/static/rangetypes.html CREATE TABLE room_reservation ( room TEXT, during TSRANGE, EXCLUDE USING gist (room WITH =, during WITH &&) ); INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)'); INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)'); ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl" DETAIL: Key (room, during)=(123A, [ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )) conflicts with existing key (room, during)=(123A, [ 2010-01-01 14:00:00, 2010-01-01 15:00:00 )). Used to have to rely on the application to do these sorts of constraints.
- j-kidd 14y agoYeah, this is another big one. I first came across the temporal project [1] in year 2008. Glad to see that it finally makes it to release. [1] http://pgfoundry.org/projects/temporal/ http://pgfoundry.org/projects/temporal/
- pimeys 14y agoIt's kind of annoying to read so much good about PostgreSQL when you have hundreds of gigabytes of data in MySQL and no real reason to migrate to another database because of the expenses. I loved postgres in my previous project when I needed spatial indexing and I started to like its interface a lot. It seems to be that Postgres is solving the problem of having different databases for your app. I would love to see the Redis datatypes built into it and Postgres might then become the strongest choice when selecting tools for my next app.
- bsg75 14y agoWhich Redis types specifically? Have you seen?: https://github.com/dpage/redis_fdw https://github.com/dpage/redis_fdw
- pimeys 14y agoSeems pretty experiemental. But just for a key-value store and using the json datatype, how good is the support for all these features in libraries like ActiveRecord pg adapter? I remember that two years ago features like bounding box selects required plain sql, although some quite bad libraries existed.
- jeffdavis 14y ago"I would love to see the Redis datatypes built into it and Postgres might then become the strongest choice when selecting tools for my next app." Have you considered writing an extension to do that? It may be easier than you think; postgresql has a powerful extension mechanism (you can even do specialized indexing!). As far as I can tell, JSON only made it into core because there are expected to be many users very quickly. But that doesn't scale -- it's better for users to look at pgxn.org and developers to use the extension mechanism. Once that becomes a matter of course, we'll see an explosion of specialized extensions.