by Jake Worth on June 21, 2016

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.

Enjoy!

Watch for database changes on #PostgreSQL (Dorian Karter)

If you are trying to debug multi-threaded environments or a really fast job queue you might need to "watch" a table for changes.

In 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.

Quickly see the contents of a table in #PostgreSQL (Dorian Karter)

Next time you want to see the contents of a table in Postgres' CLI don't type the whole:

select * from name_of_table;

Just use:

table name_of_table;

h/t Josh Branchaud

Be aware! Postgres rounds. (Chris Erin)

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

Postgres rounds!

Using Expressions In Indexes With PostgreSQL (Josh Branchaud)

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 email column. Then I may end up creating an index like this

create index email_idx on users (email);

If I always perform queries on the email column with the lower() function, like this

select * from users where lower(email) = lower('some@email.com');

then I will want to also create an index with that full expression -- lower(email)

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 lower(email) expression, select statements like the one above will be forced to do full sequential scans instead of indexed scans.

Types and type casting in #PostgreSQL (Dorian Karter)

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 :: operator:

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:

https://til.hashrocket.com/sql

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.