PostgreSQL mini cookbook: Basic Query Tricks
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:
yearmonthdayhourminuteseconddowfor 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
Feedback
Was this page helpful?
Glad to hear it!
Sorry to hear that.