Here are some of the top SQL posts from Today I Learned.
My goal with this series is to pause and highlight some of the top posts from the first year of Today I Learned. Today we'll look at SQL, our second-most active channel.
Databases matter to Hashrocket. 'Your database will be multi-tenant' is a common axiom. We feel that a robust database, with precise constraints, capable of standing alone from any framework, is crucial to the long-term success of a project. SQL mastery is a badge of honor here.
Here are the top five most liked SQL posts, in order, from the first year of Today I Learned.
If you are trying to debug multi-threaded environments or a really fast job queue you might need to "watch" a table for changes.
psql use the
\watch command like so:
\watch 1 "select * from job_queue";
This will run the query every 1 second (you can change the second argument if you need it slower) and display the result as well as past results.
Next time you want to see the contents of a table in Postgres' CLI don't type the whole:
select * from name_of_table;
h/t Josh Branchaud
Yesterday, my pair and I created a test that calculated a value and compared that to the value of a calculation in the code we were testing. This worked out great except for one hitch, we were asserting about the derived value after it had been inserted into the database. What we didn't count on is that Postgres rounds. Check this out:
create table money (amount numeric(4, 2)); insert into money (amount) values (10.342) returning amount; amount -------- 10.34 insert into money (amount) values (10.347) returning amount; amount -------- 10.35
Though we usually see column names by themselves when defining an index, it is also possible to create an index with an expression.
Let's say I have a
users table with an
create index email_idx on users (email);
If I always perform queries on the
function, like this
select * from users where lower(email) = lower('firstname.lastname@example.org');
then I will want to also create an index with that full expression --
I can do this with a statement like the following
create index lower_email_idx on users (lower(email));
Without an index that uses the full
statements like the one above will be forced to do full sequential scans
instead of indexed scans.
To see the type of column or any entity in PostgreSQL use
pg_typeof. Here's an example:
select pg_typeof(array['thing']); -- OUTPUT: -- pg_typeof -- --------- -- text
To cast to another type use the
select pg_typeof(array['thing']::varchar); -- OUTPUT: -- pg_typeof -- ------------------- -- character varying
h/t Josh Branchaud
Thanks to Dorian, Chris, and Josh for those posts.
Today I Learned had a spike in traffic near the beginning of the year, and these posts are mostly from that time. But there's a lot of great SQL tips from earlier. See them all here:
Want more SQL tips from Hashrocket? Check out our newest production, PG Casts.
Keep committing that data, and learning every day.
This blog post is part two of a series; here's part one. Next, we will look at the top Ruby posts from year one.