Postgre SQL: Sorting text searches by relevance

Something I've been meaning to do for a while now is sort text searches by relevancy. There are alot of examples in the Postgre documentation but I found them a bit vague to understand.

The way I was using it was a little different to the way they were trying to provide examples for, because my table already had a tsvector field which stored the information ready for searching.

This example below is based on the things I've learnt from Haitham Mohammad's blog.

SELECT table.*, ts_rank_cd(tsvector_field, plainto_tsquery('microsoft software')) AS rank
FROM table
WHERE tsvector_field @@ plainto_tsquery('microsoft software')
ORDER BY rank

Assuming that your table has a field called "tsvector_field" which is pre-populated with text search information, the function ts_rank_cd() will create a ranking of the results which allows you to sort by relevancy. The trick is that you have to give it both arguments in your "WHERE" comparison clause in order to make it work.

[ Source ]

PostgreSQL: Display results vertically

Sometimes you just need to quickly skim sample data from the database, but reading long data horizontally is a bit of a mess.

To read results vertically, toggle the expanded display mode using "\x".

content=# \x

Expanded display is on.
content=# select * from table_name;
-[ RECORD 1 ]-----------+-----------------------------
id                      | 8
when                    | 2010-03-02 13:37:33.64327+11
salutation              | Mr
first_name              | twig
address_street_number   | 123
address_street_name     | French Girl
address_suburb          | Has A Reason
address_state           | For Hurting Boyfriend
address_postcode        | 1234
address_country         | France
no_promotional_material | t

content=# \x
Expanded display is off.

In case you're wondering, the french girl reference is from a song called "The Presidents of USA - French Girl".

SQL: Zero padding your numbers

Most SQL servers should support LPAD() and RPAD().

The syntax for these functions are "LPAD(string, length, pad)".

The padding is space by default, but just set it to "0" to use zeros.