Selecting all Columns Except One in PostgreSQL

Google’s BigQuery has a very interesting SQL language feature, which I’ve missed many times in other databases: select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, …] ) ] [ REPLACE ( expression [ AS ] column_name [, …] ) ] | expression [ [ AS ] alias ] } [, …] [ FROM from_item [, …] ] [ WHERE bool_expression ] … Notice the two keywords EXCEPT and REPLACE that can be used along with an asterisked expression. An Example For example,…

Read More

PostgreSQL 11’s Support for SQL Standard GROUPS and EXCLUDE Window Function Clauses

Exciting discovery when playing around with PostgreSQL 11! New SQL standard window function clauses have been supported. If you want to play with this, you can do so very easily using docker: docker pull postgres:11 docker run –name POSTGRES11 -e POSTGRES_PASSWORD=postgres -d postgres:11 docker run -it –rm –link POSTGRES11:postgres postgres psql -h postgres -U postgres See also: https://hub.docker.com/r/_/postgres The frame clause When working with window functions, in some cases you want to add the optional frame clause. For example, to get a sliding average over your data, you will write:…

Read More

Rene Dudfield: Using PostgreSQL as a cache?

In the article on his blog Peter asks “How much faster is Redis at storing a blob of JSON compared to PostgreSQL?“. Answer: 14x slower. Seems about right. Usually Redis is about 4x faster for a simple query like that compared to using PostgreSQL as a cache in my experience. It’s why so many people use Redis as a cache. But I’d suggest PostgreSQL is good enough to act as a cache for many people. Django is pretty slow at fetching from PostgreSQL compared to other python options, so this…

Read More

Mapping PostgreSQL Interval to Java Duration with Hibernate

Introduction In this article, we are going to see how to map the PostgreSQL interval column type to a Java Duration object using Hibernate and the hibernate-types project. Another very useful feature introduced by the hibernate-types project is that all types extending the ImmutableType can now be treated as standard org.hibernate.type.Type, therefore enabling a much better Hibernate Core API integration. Domain Model Assuming we have the following book database table that defines a presale_period column of the interval type. We can map the book table to a Book JPA entity…

Read More

Peter Bengtsson: How much faster is Redis at storing a blob of JSON compared to PostgreSQL?

tl;dr; Redis is 16 times faster and reading these JSON blobs.* In Song Search when you’ve found a song, it loads some affiliate links to Amazon.com. (In case you’re curious it’s earning me lower double-digit dollars per month). To avoid overloading the Amazon Affiliate Product API, after I’ve queried their API, I store that result in my own database along with some metadata. Then, the next time someone views that song page, it can read from my local database. With me so far? The other caveat is that you can’t…

Read More