RSS

PostgreSQL mini cookbook: Basic Query Tricks

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

Case insensitive searches

You want to check equality or do a regular expression search but ignoring case

Solution

Either use lower() on the field and the value being compared or use the ILIKE or ˜˜* regular expression operators.

Discussion

If you want to do a simple comparison of a text field use the lower() or upper() functions:

SELECT * FROM companies WHERE lower('Archonet')=lower(co_name);

If you want to do a case-insensitive wildcard match use the ILIKE keyword or ˜˜* operator:

SELECT * FROM companies WHERE co_name ILIKE 'Arc%';

Note that both ILIKE and ˜˜* are locale-dependent and are Postgres-specific.

Escaping LIKE wildcards

You want a wildcard match with LIKE but need to match a literal underscore or percentage sign.

Solution

Escape the wildcard character with a backslash - remember you will need two backslashes to get one through the string processor.

Discussion

If you want to match “20% increase…” but not “200 increase…” you will need something like:

SELECT * FROM foo WHERE a LIKE '20\\% increase%';

The above is when typed from psql, and you need two backslashes because Postgres' string processor boils two backslashes down into one. If you were building a query string in (say) PHP you would need four - PHP will reduce this to two in the variable and Postgres to one by the time it reaches the LIKE

$sql = "SELECT * FROM foo WHERE a LIKE '20\\\\% increase%'";

Custom escape character

If you are worried about a world backslash shortage you can use the ESCAPE qualifier to use a different character instead of backslash:

SELECT * FROM foo WHERE a LIKE '20X% increase%' ESCAPE 'X';

You’ll need to make sure you double X characters where they occur if you use this. Oh - and other people’s string quoting functions won’t be expecting this, so be careful.

Query records without a corresponding key in another table

You want to find all the records in table A which don’t have a corresponding record in table B.

Solution

Use NOT IN or a LEFT | RIGHT JOIN

Discussion

If you’ve imported data and want to make sure the data matches between tables, you could use something like the following:

SELECT dy_id, dy_company from diary LEFT JOIN companies
ON dy_company=co_id WHERE co_id IS NULL;

This will select all diary entries whether or not there is a matching company entry and then filter out those that have matches.

Alternatively, you can use NOT IN:

SELECT dy_id, dy_company from diary WHERE dy_company NOT IN
(SELECT co_id FROM companies);

Which one to use is a matter of taste and which you find most efficient (but bear in mind [not-in-nulls] with NULLs and NOT IN).

Returning the first N results

You don’t want all million rows returned at once, you want a page at a time.

Solution

Use the LIMIT keyword to modify your SELECT.

Discussion

If you want to limit your results to a manageable size, use the LIMIT keyword in your query. So to get the first five results:

SELECT * FROM staff ORDER BY username LIMIT 5;

Note you should always have an ORDER BY clause, otherwise you won’t know which five results you are getting.

Returning the next N results

You want the second (or fifteenth) page of your results.

Solution

Use the OFFSET keyword to modify your LIMIT.

Discussion

To get a second or subsequent “page” of results, add the OFFSET keyword to your query:

SELECT * FROM companies ORDER BY co_id LIMIT 5 OFFSET 5;

Returning the last N results

You want to skip directly to the last page of your results.

Solution

Calculate how many rows there are in the full results-set and use an appropriate OFFSET or do a reverse-sort.

Discussion

There is no way to directly return the last page of a results set, but you can try:

SELECT count(*) FROM companies;

And take the result from that (N say) to calculate an offset of N-P in your OFFSET (see [return-next-n]). Remember that the last page might only be half full though, so you want N mod P not N-P if you are being strict about these things.

Alternatively, you can do:

SELECT * FROM companies ORDER BY co_id DESC LIMIT 5;

This gives you the first five results but has sorted them in a reverse order. If you can’t be bothered to reverse them in your application you can use a view (or a temporary table).

CREATE VIEW tmp_co_view AS SELECT * FROM companies
  ORDER BY co_id DESC LIMIT 5;
SELECT * FROM tmp_co_view ORDER BY co_id;

This grabs the page we want in reverse order then re-sorts it. Don’t forget to drop the view when you finish with it.

Selecting part of a date

You want to compare/calculate/display part of a date/time (an hour, day, month etc).

Solution

Use the date_part(<part>,<date | time | interval>) function.

Discussion

Sometimes you want to summarise results per month or select entries for the first day of the month. The simplest way to do this is to use the date_part() function. So, to see all orders placed in January, use:

=# select username, date_part('year', last_update), date_part('month', last_update) from staff where date_part('month', last_update) = 5;

 username | date_part | date_part
----------+-----------+-----------
 Mike     |      2006 |         5
 Jon      |      2006 |         5

You can use date_part to return:

  • year
  • month
  • day
  • hour
  • minute
  • second
  • dow for the day of the week (Sunday is 0, Saturday is 6).

Converting a date to unix timestamp (epoch seconds)

You want to compare a Postgresql date or timestamp to a unix time (seconds since the epoch).

Solution

Use date_part() or extract()

Discussion

If you are planning to do a lot of temporal manipulation, always use Postgresql’s date/time types - they offer better features.

On the other hand, most client languages provide unix-style timestamps - these two functions can bridge the gap for you.

=# select date_part('epoch',now()) as unixtime;
     unixtime
------------------
 1582594120.25834

=# select extract('epoch' from now()) as unixtime;
     unixtime
------------------
 1582594120.25834

Combining two sets of results

You have two (or more) queries returning the same fields and want to merge their output.

Solution

Use the UNION keyword to join your two SELECT statements.

To see a real-world example of this run psql -E and type \dt to see tables.

Discussion

Let’s assume we add a diary_archive table and move entries from diary into it once they are older than 6 months. Sooner or later you are going to want to see all the diary entries for a company both current and archived, so:

SELECT * FROM diary WHERE dy_company=1
  UNION
  SELECT * FROM diary_archive WHERE dy_company=1 ORDER BY dy_id;

Note that we only need one ORDER BY to sort the output of the combined query. Also, the fields in this case were the same, but this doesn’t need to be the case. The important thing is that the types match - if the field1 from the first select is an int8 field1 from the second select should be too.

References