Home » Tag Archives: Databases (page 5)

Tag Archives: Databases

PL/SQL backtraces for debugging

software-development-2-logo

For many PL/SQL developers, this might be common sense, but for one of our customers, this was an unknown PL/SQL feature: Backtraces. When your application raises an error somewhere deep down in the call stack, you don’t get immediate information about the exact source of the error. For large PL/SQL applications, this can be a pain. One workaround is to ...

Read More »

Integrating jOOQ with PostgreSQL: Partitioning

postgresql-logo

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 is mainly used for performance ...

Read More »

A Wonderful SQL Feature: Quantified Comparison Predicates (ANY, ALL)

software-development-2-logo

Have you ever wondered about the use-case behind SQL’s ANY (also: SOME) and ALL keywords? You have probably not yet encountered these keywords in the wild. Yet they can be extremely useful. But first, let’s see how they’re defined in the SQL standard. The easy part:           8.7 <quantified comparison predicate> Function Specify a quantified comparison. ...

Read More »

SQL Tip of the Day: Be Wary of SELECT COUNT(*)

software-development-2-logo

Recently, I’ve encountered this sort of query all over the place at a customer site:                     DECLARE v_var NUMBER(10); BEGIN SELECT COUNT(*) INTO v_var FROM table1 JOIN table2 ON table1.t1_id = table2.t1_id JOIN table3 ON table2.t2_id = table3.t2_id ... WHERE some_predicate; IF (v_var = 1) THEN do_something ELSE do_something_else END IF; ...

Read More »

Are You Using SQL PIVOT Yet? You Should!

software-development-2-logo

Every once in a while, we run into these rare SQL issues where we’d like to do something that seems out of the ordinary. One of these things is pivoting rows to columns. A recent question on Stack Overflow by Valiante asked for precisely this. Going from this table:             +------+------------+----------------+-------------------+ | dnId | propNameId ...

Read More »

PostgreSQL’s Table-Valued Functions

postgresql-logo

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; … ...

Read More »

Flyway and jOOQ for Unbeatable SQL Development Productivity

software-development-2-logo

When performing database migrations, we at Data Geekery recommend using jOOQ with Flyway – Database Migrations Made Easy. In this post, we’re going to look into a simple way to get started with the two frameworks.     Philosophy There are a variety of ways how jOOQ and Flyway could interact with each other in various development setups. In this ...

Read More »

Database primary key flavors

software-development-2-logo

Types of primary keys All database tables must have one primary key column. The primary key uniquely identifies a row within a table therefore it’s bound by the following constraints: UNIQUE NOT NULL IMMUTABLE When choosing a primary key we must take into consideration the following aspects:   the primary key may be used for joining other tables through a ...

Read More »

MySQL error: Can’t connect to MySQL server

mysql-logo

You may have stumbled on this MySQL error. If you have already seen this before, then it’s really easy to recognize this and fix it. But if you haven’t seen this error before, then it takes a little longer to troubleshoot the issue. I am listing down the steps to troubleshoot this issue.               ...

Read More »
Want to take your Java Skills to the next level?
Grab our programming books for FREE!
  • Save time by leveraging our field-tested solutions to common problems.
  • The books cover a wide range of topics, from JPA and JUnit, to JMeter and Android.
  • Each book comes as a standalone guide (with source code provided), so that you use it as reference.
Last Step ...

Where should we send the free eBooks?

Good Work!
To download the books, please verify your email address by following the instructions found on the email we just sent you.