RSS

PostgreSQL mini cookbook: Advanced Query Tricks

PostgreSQL mini cookbook: advanced query tricks, those tricks back to 2001 and still works today. 😉

Having a dynamic field value without a lookup table

You want a “calculated” column returned from a select but joining to a lookup table is either not practical or not desirable.

Solution

Use the CASE...WHEN...ELSE...END structure or write a function.

Discussion

Let’s say we want to select orders and indicate whether or not they are pending, awaiting payment or complete. Obviously this depends on the dates ordered, delivered and paid but we can’t use these to index a lookup table since we don’t know what values they will have.

SELECT ord_id, ord_qty,
  CASE
    WHEN ord_paid IS NOT NULL THEN 'Complete'
    WHEN ord_delivered IS NOT NULL THEN 'Awaiting payment'
  ELSE 'Pending'
  END AS status
  FROM orders;

Note that the CASE conditions shortcut - that is the first match is the one returned, others are never tested.

Alternatively, we could write a function taking the two dates and returning the status-text we desire.

SELECT ord_id,ord_qty, status(ord_paid, ord_delivered) FROM orders;

See the reference manuals for how to build such a function.

You should write a function if it is going to be used in several places, otherwise the CASE is good for simple situations.

Handling sub queries with NULL

You have a sub select in a SELECT query but null values seem to short-circuit your results.

Solution

Make sure you handle the case with IS NOT NULL in the sub query.

Discussion

This is correct - remember that the only test the NULL passes is IS NULL. So - if you have

SELECT co_name
  FROM companies
  WHERE co_id NOT IN
    (SELECT dy_company FROM diary);

You’d be expecting a list of companies without diary entries, and so long as dy_company is never null that’s what you’ll get. But if there’s a null value you’ll get no results at all. Why?

We can expand the query as follows:

WHERE co_id NOT IN (SELECT dy_company FROM diary)
WHERE co_id NOT IN (1, 2, null, 3...)
WHERE NOT (co_id=1 OR co_id=2 OR co_id=null OR co_id=3...)
WHERE NOT (null)
WHERE null

Remember that Anything Null returns Null and it starts to make sense.

To solve it either make sure that dy_company is defined as NOT NULL in the table definition (avoid the whole issue) or explicitly check for this case.

SELECT co_name FROM companies
  WHERE co_id NOT IN
    (SELECT dy_company
      FROM diary
      WHERE dy_company IS NOT NULL);

Altering table definitions

You want to add a column to table foo.

Solution

Either:

  1. Create a new table bar with the additional field, copy the data over then delete foo and rename bar

  2. Use the ALTER TABLE ADD COLUMN command

The first has the advantage that you can make more radical changes than just adding a column. The second is much quicker.

Discussion

Even the best design needs occasional tweaking. To add a city column to the companies table you would use

ALTER TABLE companies ADD COLUMN city(text);

You can also add indices and constraints to a table, rename columns or change the owner of a table. If you find something you can’t do just follow solution 1.

References