PostgreSQL mini cookbook: Advanced Query Tricks
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.
CASE...WHEN...ELSE...END structure or write a function.
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.
Make sure you handle the case with
IS NOT NULL in the sub query.
This is correct - remember that the only test the
NULL passes is
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
To solve it either make sure that dy_company is defined as
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.
Create a new table bar with the additional field, copy the data over then delete foo and rename bar
ALTER TABLE ADD COLUMNcommand
The first has the advantage that you can make more radical changes than just adding a column. The second is much quicker.
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.
- PostgreSQL Conditional Expressions
- PostgreSQL Queries
- PostgreSQL SQL Select
- PostgreSQL SQL Alter Table
Block distractive apps from appearing on the Home Screen and App Library, enhance your focus and reduce screen time.
Encrypted your DNS to protect your privacy and firewall to block phishing, malicious domains, block ads in all browsers and apps