Welcome to my PinkLetter. A short, weekly, technology-agnostic, and pink newsletter where we cultivate timeless skills about web development.
My Ramblings This Week
I've been obsessed with SQL // Postgres recently. Here are a few interesting things I've discovered:
- COPY data between a file and a table.
- Data Type Formatting Functions:
to_char,to_date,to_number,to_timestamp. - Conditional Expressions:
case,coalesce,nullif,greatestandleast. join b on a.c = b.c==join b using (c).- LATERAL is like a for loop.
psql --html --dbname DBNAME --command QUERY postgres > out.html.\editin psql to open the editor and write the query.- "The pg_stat_statements module provides a means for tracking planning and execution statistics of all SQL statements executed by a server."
EXPLAIN (analyze, verbose, buffers)and throw the output in https://explain.depesz.com or PostgreSQL Explain Visualizer.- Date/Time Functions and Operators.
- Set Returning Functions.
- "The offset clause is going to cause your SQL query plan to read all the result anyway and then discard most of it until reaching the offset count. When paging through lots of results, it’s less and less ecient with each additional page you fetch that way" (docs).
- Combining Queries:
union,expect,intersect. - "CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same result as you get from listing the two tables at the top level of FROM, but restricted by the join condition (if any). CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. These join types are just a notational convenience, since they do nothing you couldn't do with plain FROM and WHERE" (docs).
from A cross join B==from A, B.- Window Functions.
- Pattern Matching:
~~,~~*,!~~,!~~*and~,!~,~*and!~*. - Exclusion Constraints.
- GROUPING SETS, CUBE, and ROLLUP.
- WITH Queries (Common Table Expressions).
COUNT(*) FILTER (WHERE ...)(docs).- String Functions and Operators.
Have you dealt with a difficult SQL query recently? Please reply to this email and tell the story. I'm researching the topic and would be grateful for life.
Elsewhere on the Web
Not enough links this week, huh? Here's one more.
I swear it is not about Postgres!
JWT should not be your default for sessions by Evert Pot
Using JWTs for tokens add some neat properties and make it possible in some cases for your services to be stateless, which can be desirable property in some architectures.
Adopting them comes with drawbacks. You either forego revocation, or you need to have infrastructure in place that be way more complex than simply adopting a session store and opaque tokens.
My point in all this is not to discourage the use of JWT in general, but be deliberate and careful when you do. Be aware of both the security and functionality trade-offs and pitfalls. Keep it out of your ‘boilerplates’ and templates, and don’t make it the default choice.