15 ms·
Modern SQL in PostgreSQL
- leoh 12y agoInteresting. I don't think django implements a lot of this in their ORM.
- hcarvalhoalves 12y agoDjango's ORM is very limited, it doesn't even expose GROUP BY. I don't think there will ever be support for more powerful constructs like these.
- lastofus 12y agoYou are right in that the Django ORM will always be relativly simple, but it does have some GROUP BY support: https://docs.djangoproject.com/en/1.7/topics/db/aggregation/#topics-db-aggregation https://docs.djangoproject.com/en/1.7/topics/db/aggregation/...
- hcarvalhoalves 12y agoI'm aware, I was already years deep into Django when they released it. But since it doesn't expose GROUP BY (the aggregation API only covers a subset of the uses), when you need it you have to drop to raw SQL, in which case your query will not return a QuerySet anymore, breaking compatibility with the rest of the codebase, defeating the point of an ORM in the first place. An uphill battle.
- jsmeaton 12y agoWe're getting closer to full support for custom/advanced queries with full queryset support. See the release notes for 1.8 expressions: https://docs.djangoproject.com/en/1.8/ref/models/expressions/ https://docs.djangoproject.com/en/1.8/ref/models/expressions... The GROUP BY still isn't exposed directly, but neither are joins or subqueries. The rules that make up what should be included in the group by are fairly elaborate. However, writing custom expressions will now allow you to craft some pretty cool SQL, and allow the sub-expression to specify whether it should contribute to GROUP BY or not. Disclaimer: I did a lot of the work for query expressions in 1.8. The ORM team is expanding, and people are actively working on bringing more powerful features to the ORM.
- meric 12y agoYou can use a Meta.managed=False model and a Postgresql view and map your model to that view for custom SQL. And just query from that model as normal.
- collyw 12y agoGroup by is kind of implicit depending on the model you start with. Model_a.objects.all().select_related(extra_tables).aggregate(arg_params) will group by the Model_a table.
- qooleot 12y agoI work on several Django apps, and also write advanced SQL in Postgres (CTEs, plv8 functions, materialized views, etc.). One thing the ORM allows though is writing a custom sql query and its pretty easy to 'cast' the sql results into a Django model. With that said, its still sometimes a struggle to justify if almost all the queries are highly custom, and the app doesn't require Django Admin. Certainly there are advantages, but the minimalism of Flask and focusing on the REST interface for integration and building micro services is more appealing in some cases.
- IndianAstronaut 12y agoIf one wants to use these Postgres features, Flask without an ORM is the way to go. Django is too constraining, especially with its primary key limiting.
- collyw 12y agoIf you use Django without the ORM, why would it be any more constraining than Flask? I usually try to keep my queries within the ORM, as it means things like sorting and pagination become very easy, but its lacking in certain areas.
- Iftheshoefits 12y agoIt's not necessarily more constraining, but from my perspective there are two reasons to choose Django over other python frameworks: the ORM and the out-of-the-box Admin (which depends on the ORM). If you take those away, there is no good reason, in my opinion, to choose Django over, say, Tornado. I wouldn't choose Flask for anything; it's too slow, even by the relatively poor standards set by Tornado and Django.
- IndianAstronaut 12y ago> wouldn't choose Flask for anything; it's too slow, even by the relatively poor standards set by Tornado and Django. What are your picks for good and fast web frameworks which are also light weight?
- jfb 12y agoSQLAlchemy supports many of these advanced concepts.
- mjrpes 12y agoOne thing that would be nice is if SQL provided first class support for sub records. So instead of "SELECT name, (SELECT GROUP_CONCAT(CONCAT_WS(',', post_id, post) SEPARATOR ';') FROM posts p WHERE p.user_id = u.user_id) AS 'posts' FROM users u WHERE u.user_id = 1", you could do "SELECT name, (SELECT post_id, post FROM posts p WHERE p.user_id = u.user_id) AS 'posts' FROM users u WHERE u.user_id = 1". and the query result would be { name : 'Todd', posts : [ { post_id : 1, post : 'My Comment' } ] }. Obviously this is a simple example and could have been rewritten as a query on the posts table, inner joined on the user table, and duplicating the user's name in the result. But it becomes much nicer to have as queries get more complex. A query that supports sub records would gives you flexibility to structure data like a JSON object and simplify the server end of REST apis.
- qooleot 12y agoIf you're just trying to get JSON out (for a simple query to REST api, or in a node.js environment), have you considered converting the record/recordset to json in the subquery? The json functions in 9.3+ are pretty handy for that sort of thing. Andrew (core developer who wrote most of that functionality) and I decided to keep the api pretty lightweight, as its easy to also add your own functions to suit your needs.
- mjrpes 12y agoJSON functions do look pretty helpful and go a long way, even if not first class. As you can tell from syntax I'm stuck in the MySQL/MariaDB world, so not up with everything in PostgreSQL. This and the features in the slideshare are an eye opener to me. Thanks!
- zapov 12y agoPostgres/Oracle supports such constructs. And Revenj has been using it for years: https://github.com/ngs-doo/revenj https://github.com/ngs-doo/revenj And yes, Revenj now comes with an offline compiler ;)
- ddorian43 12y ago
- rosser 12y agoOne of my favorite idioms lately for doing bulk updates without incurring lock contention is to chain CTEs, like so: with candidate_rows as ( select id from table where conditions limit 1000 for update nowait ), update_rows as ( update table set column = value from candidate_rows where candidate_rows.id = table.id returning table.id ) select count(1) from update_rows; ...and loop on issuing that query until the "count(1)" returns zero some for number of iterations (three works pretty well). Want to add a column to your "orders" table and populate it without blocking concurrent writes for as long as it will take to rewrite a multi-million row table? Want to re-hash user passwords using something stronger than MD5, but not prevent users from ... you know, logging in for the duration? CTEs are all that and the bag of chips.
- lastofus 12y agoDo you have any resources for learning cool advanced SQL like this? I've used CTEs in the past, just from reading about them in the documentation, but I feel like I'm only scratching the surface of what's possible out there.
- Vivtek 12y agoSecond that request. What can I read to go beyond my SELECT * from t order by c-level knowledge?
- AlisdairO 12y agoApologies for the self-plug, but you might find some stuff in the later-stage Aggregates and Recursive exercises on http://pgexercises.com http://pgexercises.com . There's a bunch of stuff on CTEs and window functions in there.
- jklowden 12y agoI like Date's SQL and Relational Theory (http://shop.oreilly.com/product/0636920022879.do http://shop.oreilly.com/product/0636920022879.do). It won't tell you about the latest changes to the standard, but it will help you get a handle on the relational model, which is the key to using SQL intelligently.
- xvirk 12y agois it even possible ?
- dtech 12y agoMySQL (and thus MariaDB I presume) and SQLite seem to be pretty poor in supporting these "new" features. SQLite seems logical because it needs to be kept lean for embedding purposes, but do people know why MySQL is lagging behind so much?
- weaksauce 12y agoOracle influence? Postgres is the leader now in mindshare and features for the opensource offerings. If you like MySQL you could check out mariadb
- barrkel 12y agoMy experience with PostgreSQL and MySQL is that PostgreSQL supports lots of features, but performance needs a very careful eye - often a neat feature isn't as useful as it seems because optimization isn't good enough. Whereas MySQL tends to be very good at what it can do, and makes it easy to do silly things, like turn off referential integrity in a slow query to boost performance. It almost feels like a worse-is-better story. As a programmer, PostgreSQL is much better to work with; more tools, better EXPLAIN, more features, more types, more of almost everything. But to use in the heat of battle, it's less clear-cut. PostgreSQL's replication story is complicated. MySQL master-master replication is fairly easy to set up, and if you use a master as a hot failover, it all mostly just works; when the primary site comes back up, it resyncs with the failover. PostgreSQL has a lot of different replication stories - without a strong central narrative, it's hard to gain confidence.
- noehp 12y agoThis doesn't match my experience at all. MySQL performance is highly variable, and it's incredibly easy to write simple queries that have truly abysmal performance. Postgres on the other hand, has always tended towards making things work well (a step that mysql often skips), and then work quickly thereafter. A lot of MySQL acolytes say Postgres is slow because, unlike MySQL, it doesn't ship with unsafe defaults. MySQL doesn't just allow you to do dumb things, it starts off with many of those settings as the defaults. To me, the real problem is that people likebarrkel exist. He doesn't know what he's on about, but he likes MySQL. Most of wheat he wrote is flatly false, but he said it confidently. And he's employed someplace that probably uses MySQL. MySQL got adoption for two reasons: 1) it used to be easier to install; and 2) it has unsafe defaults that mean if an idiot runs a benchmark, it wins. That's it. That's how it won market. After that, it was network effects, and nothing else. MySQL is a turd. It requires substantial expertise to use MySQL because it is such an awful and dangerous tool. It slows you down as you get better. But most of the people who use it don't know any better, or (like barrkel) they spew nonsense that is the opposite of reality. So it wins. Network effects suck.
- ecopoesis 12y agoAnd yet, despite all these cool features, Postgres still doesn't support upsert or merge, which even MySQL manages to get right.
- rosser 12y agoWhat's really funny about this comment is that, while all of the features discussed in TFA are fully standards-compliant SQL, MySQL implements none of them, and its "upsert" isn't. EDIT: And before one slags PostgreSQL too hard for not currently supporting upsert, one might peruse the relevant pg wiki page [1] to better understand where development stands and why we don't yet have it. (Hint: it's actually kinda complicated, assuming you want it done, you know, "right".) [1] https://wiki.postgresql.org/wiki/UPSERT https://wiki.postgresql.org/wiki/UPSERT
- ecopoesis 12y ago"Get right" probably isn't the right term. Mostly I'm just annoyed that I can't choose an open source DB that has upsert (or merge if you want to do the standards version) and CTEs. I generally have uses for both of these in every project I work on.
- jimktrains2 12y agoYou can use writable CTEs as a kludge for an upsert depending on your use case.
- tveita 12y agoBoth MySQL and SQLite's implementations work satisfactorily, so I tend to think that PostgreSQL's reluctance is the perfect being the enemy of good. It's a pattern that shows up often when you're mirroring data from a third-party, so it's a shame that the programmer has to do conflict handling for an operation that the database could easily do atomically.
- jimktrains2 12y ago
- nroose 12y agoI was interested in NoSQL. Went to a Mongo pres. Both examples would have been easier and faster in SQL. Even the SQL I used 20 years ago. I asked for an example that would show performance advantage. I got a tired vague statement about the vague performance advantage. Seems like snake oil to me.
- cgh 12y agoUnfortunately you were downvoted, but it wasn't so long ago on HN that every second story was NoSQL this, NoSQL that. There were even "SQL is dead"/"relational DBs are dead" posts, just ridiculous. So it's nice to see stories like this. I've been writing a lot of recursive queries for Postgresql lately using CTEs. Quite cool though a little mindbending at times.
- michaelchisari 12y agoI remember those days. I was never sold on NoSQL, but if anything can be said, it lit a fire under SQL and competition in the space was ultimately a good thing. Glad I didn't go all in on NoSQL, though.
- crdoconnor 12y agoHN is very fad-oriented.
- andybak 12y agoExcept some fads turn out to be long-term trends, so it's worth keeping one eye open.
- crdoconnor 12y agoAbsolutely, but it pays to be very skeptical and to pay closer attention to the negative "I have used this technology and it sucks because [links to the bug tracker]" articles than the ones that gush about how great they are.
- gamesbrainiac 12y agoAnyone know where I can get the full video lecture for this? I'd really like to see it.
- fatalmind 12y agoI've given this talk on Saturday in Moscow again and it was video taped there. Organizers told me they'll publish the English original as well as the Russian voice over version. I'm keen for the second one ;) I keep you posted.
- jiphex 12y agoIt was given at FOSDEM, the videos should be available "soon".
- juliangregorian 12y agoI often find myself wishing that SQL had a more uniform syntax. So much of it resembles COBOL to me, with all the capital letters and "english-like" arbitrary word order. At the same time, relational algebra exists and is very elegant, in an APL-like way. Would be very interested if there was a project to expose such an api to SQL databases.
- collyw 12y agoSQL is case insensitive. It is a bit quirky though.
- juliangregorian 12y agoTrue, but very few people write it as anything but.
- domoarevil 12y agoIndeed, something people forget sometimes leading to collation problems in the (odd) instance when CS is set.
- ris 12y agoSQLAlchemy (for python) tries to sanitize the syntax away from SQL with some level of success.
- liprais 12y agoAccording to my understanding ,Teradata has already supported these so-called "modern" features for several years.
- headgasket 12y agoPostgresql 9.4 with jsonb sends mongo to the dustbin, IMHO. If you have to write it in js close to the data or if plpgsql is too steep of a learning curve, you can play with the experimental plv8. But you should really pick up plpgsql, it's "python" powerful, with the an awesome db (and has a python 2.x consistent API, sadly, but the doc is very good) There is a great sublime 2.0 package that makes the writing and debugging of functions in one file just awesome. Write an uncalled dumb function that has a lot of the API in it at the top of your file, and you'll get autocomplete on this part of the API. Specifically no not miss getting acquainted with json and hstore, specifically using json as a variable size argument passing and returning mechanism, it's just hilariously effective. cheers, and keep making this place(not only HN, our blue dot) better, F