How to Group By “Nothing” in SQL

The SQL standard knows a lesser known feature called GROUPING SETS. One particular side-effect of that feature is that we can group by "nothing" in SQL. E.g. when querying the Sakila database: SELECT count(*) FROM film GROUP BY () This will yield: count | ------| 1000 | What's the point, you're asking? Can't we just … Continue reading How to Group By “Nothing” in SQL

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 … Continue reading Selecting all Columns Except One in PostgreSQL

How to Run a Bulk INSERT .. RETURNING Statement With Oracle and JDBC

When inserting records into SQL databases, we often want to fetch back generated IDs and possibly other trigger, sequence, or default generated values. Let's assume we have the following table: -- DB2 CREATE TABLE x ( i INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, j VARCHAR(50), k DATE DEFAULT CURRENT_DATE ); -- PostgreSQL CREATE TABLE … Continue reading How to Run a Bulk INSERT .. RETURNING Statement With Oracle and JDBC