-
-
Save pvh/4634543 to your computer and use it in GitHub Desktop.
| A Beastiary of Badassery | |
| - | |
| SQLisms | |
| -- | |
| WITH | |
| arrays and unnest | |
| window functions | |
| JSON | |
| row types | |
| selects in the target list | |
| full-text search | |
| range types | |
| reading EXPLAIN | |
| exclusion constraints | |
| LISTEN/NOTIFY | |
| intervals and time | |
| generate_series | |
| CASE WHEN pivots | |
| coalesce | |
| Extensia | |
| -- | |
| hstore | |
| dblink | |
| uuid-ossp | |
| pgcrypto | |
| tablefunc? | |
| pl/v8 | |
| postgis | |
| pppppsql! | |
| -- | |
| \d*+ | |
| \i | |
| \e | |
| \x | |
| URL support | |
| Performance/Operations | |
| -- | |
| CONCURRENTLY | |
| GIN/GIST | |
| knn | |
| application_name | |
| pg_stat_activity | |
| pg_stat_statements | |
| pg_locks | |
| pg_statio | |
| pg_cancel_backend | |
| sync commit | |
| pg_stat_replication | |
| Index only scans | |
| BONUS ROUND | |
| - | |
| 9.3's a comin' | |
| lateral joins | |
| SUPERJSON | |
| foreign data wrappers (more than french hip-hop) | |
| background (database!) workers | |
| (other?) |
!
.psqlrc
and for those more adventurous, you can do things like:
echo '*postgres`namespace_search_path /s' | mdb -p 18883
\set [alias] '[query I use often]';
in psql, use :alias to call the query.
- CASE in aggregates for a quickie alternative to a full pivot
- Also, fancy JOIN conditions: inequality, LIKE, function calls, etc.
Also, fancy JOIN conditions: inequality, LIKE, function calls, etc.
Also, unfancy ones:
SELECT * FROM users JOIN address ON users.id = 1 AND users.id = address.user_id;
@pvh - awesome list! Arrays and unnest came very handy and timely for us here!
I was wondering if there is postgres feature that I am overlooking that could help with this problem: https://gist.github.com/4669408
POSIX Regular expressions are not common in every db, expecially with an easy comparison operator like "~" (not to be confused with LIKE):
select * from users where first_name ~ '.*thomas.*'http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-TABLE
depesz's article on variables, especially custom variable classes, is interesting.
When talking about interval and time handling, don't forget to emphasize how Postgres magically does the right thing with interval arithmetic in the face of timezones, DST, etc. Apparently that is a real differentiator, and I think it nicely illustrates the general Postgres ethos of putting a lot of effort into getting these details right, so the user doesn't have to.
inet types that have many operators (including bitwise operators), so you can do arithmetic with them, and calculate appropriate bitmasks. You could also have declarative constraints enforced using them.
fuzzystrmatch is kind of interesting, though I'm not sure it should make the cut.
Partially indexes. This is something that Instagram use extensively. Maybe mention create index concurrently in passing.
From CK: \pset null, already found it handy - craigkerstiens.com/2013/02/21/mor… and +1 to \set [alias] [query]
The \set commands can be used not just for full queries. I use to save some that ended with something like 'where order_id = ' and would then run it with something like :order 2342523; Worked great.
from dim:
6:38 AM CASE
6:38 AM in the select list AND in the where clauses :)
6:38 AM \s+ and \ef in psql
6:39 AM oh, and the all time favorite, \set PROMPT1 '[PROD] %n@%/%x %# '
6:39 AM and ON_ERROR_STOP \set ON_ERROR_ROLLBACK
6:41 AM pgfincore
6:42 AM if you want some example queries for the WITH/window function, see http://tapoueh.org/blog/2012/10/05-reset-counter.html and http://tapoueh.org/blog/2012/11/02-Conference-AFUP-Lyon.html