This can happen ever so easily. You adapt a table by adding a new column: ALTER TABLE payments ADD code NUMBER(3); You go on, implementing your business logic - absolutely no problem. But then, later on (perhaps in production), some batch job fails because it makes some strong assumptions about data types. Namely, it assumes … Continue reading How to use SQL PIVOT to Compare Two Tables in Your Database
Tag: PostgreSQL
The Awesome PostgreSQL 9.4 / SQL:2003 FILTER Clause for Aggregate Functions
Sometimes when aggregating data with SQL, we'd love to add some additional filters. For instance, consider the following world bank data: GDP per capita (current US$) 2009 2010 2011 2012 CA 40,764 47,465 51,791 52,409 DE 40,270 40,408 44,355 42,598 FR 40,488 39,448 42,578 39,759 GB 35,455 36,573 38,927 38,649 IT 35,724 34,673 36,988 33,814 … Continue reading The Awesome PostgreSQL 9.4 / SQL:2003 FILTER Clause for Aggregate Functions
A True SQL Gem You Didn’t Know Yet: The EVERY() Aggregate Function
We've just added support for the EVERY() aggregate function (#1391) to jOOQ, and would like to take the opportunity of letting you know of this true SQL gem that can come in handy EVERY(now and then) (pun intended). Let's assume we have four books in our table: INSERT INTO book VALUES (1, 1, '1984'); INSERT … Continue reading A True SQL Gem You Didn’t Know Yet: The EVERY() Aggregate Function
Do You Really Understand SQL’s GROUP BY and HAVING clauses?
There are some things in SQL that we simply take for granted without thinking about them properly. One of these things are the GROUP BY and the less popular HAVING clauses. Let's look at a simple example. For this example, we'll reiterate the example database we've seen in this previous article about the awesome LEAD(), … Continue reading Do You Really Understand SQL’s GROUP BY and HAVING clauses?
Integrating jOOQ with PostgreSQL: Partitioning
Introduction jOOQ is a great framework when you want to work with SQL in Java without having too much ORM in your way. At the same time, it can be integrated into many environments as it is offering you support for many database-specific features. One such database-specific feature is partitioning in PostgreSQL. Partitioning in PostgreSQL … Continue reading Integrating jOOQ with PostgreSQL: Partitioning
Frightening Facts about MySQL
So you might've seen Destroy all Software's talk about JavaScript: Here's a similar talk (less funny more scary) about MySQL: https://www.youtube.com/watch?v=emgJtr9tIME
PostgreSQL’s Table-Valued Functions
Table-valued functions are an awesome thing. Many databases support them in one way or another and so does PostgreSQL. In PostgreSQL, (almost) everything is a table. For instance, we can write: CREATE OR REPLACE FUNCTION f_1 (v1 INTEGER, v2 OUT INTEGER) AS $$ BEGIN v2 := v1; END $$ LANGUAGE plpgsql; ... and believe it … Continue reading PostgreSQL’s Table-Valued Functions
Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
(Sorry for that click-bait heading. Couldn't resist ;-) ) We're on a mission. To teach you SQL. But mostly, we want to teach you how to appreciate SQL. You'll love it! Getting SQL right or wrong shouldn't be about that You're-Doing-It-Wrong™ attitude that can be encountered often when evangelists promote their object of evangelism. Getting … Continue reading Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
jOOQ Newsletter: April 16, 2014 – Monthly, Yearly, Perpetual licenses now available
Subscribe to this newsletter here Tweet of the Day Our customers, users, and followers are sharing their love for jOOQ to the world. Here are: Mahmud who cannot wait to make more magic with jOOQ. https://twitter.com/bigthingist/status/455985890125287424 Peter Kopfler who, after hearing about jOOQ and SQL in Vienna is thrilled to take a deep dive into the … Continue reading jOOQ Newsletter: April 16, 2014 – Monthly, Yearly, Perpetual licenses now available
How can I do This? – With SQL of Course!
Haven't we all been wondering: How can I do this? I have these data in Excel and I want to group / sort / assign / combine ... While you could probably pull up a Visual Basic script doing the work or export the data to Java or any other procedural language of choice, why … Continue reading How can I do This? – With SQL of Course!