It's got a lot of stuff going on in there.
This sounds amazing. Does anyone have a link to some docs for this?
That's correct, and that's why PostgreSQL has ADD CONSTRAINT.
The constraints describe the actual schema of the data; INDEX is an DBMS-specific implementation* for improving performance (including index types, etc.).
* Those since the standard does not yet cover some thing partial unique constraints, these have to be done as INDEX in PostgreSQL.
That's not a bug, that's a feature. Deriving from the deliberate intent to make physical independence (which was simply nonexistent at the time the model was conceived) a reality.
Call it a "hint", if you will, the meaning of which is implementation defined. But standardize the syntax for pete's sake.
And "hints" in the language are not exactly going to improve "interoperability" if their meaning is still allowed to be implementation-defined, are they ?
Sure, but the non-standard enhancements like JSON support are part of what sets Postgres apart from the competition IMO.
The release is planned for roughly next September/October.
[1] https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf, for example
(Edited for formatting.)
In PG, a JSON column is so well integrated that you can do all sorts of crazy stuff (indices over JSON queries is my favorite). You could build an entire RDBMS on top of PG's JSON column.
Postgrest works very well as this shim layer, though I've moved on to writing sql directly in the client and communicating through a web socket shim. In terms of reducing code complexity and improving performance this is absolutely unbeatable, you just need to parse incoming sql to sanitize it and make sure there is no role escalation. Because of postgres's foreign data wrappers this method can provide a consistent surface for basically all your enterprise data. The only gotcha with FDWs is that some of them don't "push down" many query clauses, so you end up doing much slower queries on the remote system and filtering locally, which is terrible for obvious reasons. That being said, the FDWs are pretty much all open source, so you can just implement push down support for those missing clauses yourself.
"Just"? I guess I'm skeptical of a statement that begins "you just have to parse sql".
Is this actually easier than I'm imagining it? I'd be curious to hear more about the security and authorization model of this approach.
A good tool for this purpose is https://github.com/JavaScriptor/js-sql-parser as it will fail to parse complex statements that are likely to include an attack vector.
In terms of authorization, you can either create per user connection pools if using web sockets and log the user in directly that way (which makes things easy) or if you must use rest, use a single connection pool with a master user then use some form of token to tell the shim who to set role to before executing the query.
https://github.com/PostgREST/postgrest
Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well.