SQL Quick Hits

SQL tidbits

LIKE '_'

You've probably user LIKE and % to search partial strings, but did you know underscore _ can match exactly one character.

id	name
1	first
2	second
3	third

>>> Select * from employee where name like 'fi%';

Returns the same result as:

>>> Select * from employee where name like 'firs_';

UNION vs UNION ALL

While you may not have thought about it, UNION is essentially a select distinct on the second table you are appending. UNION ALL won't remove duplicate rows, it just pulls all rows from all tables fitting your query specifics and combines them into a table.

Creating table from csv file in PostgreSQL

Sometime you want to quickly populate a table from a csv file. This can be done from the command line. After you create your table in the database, use the following command to upload your data.

$ psql -h <serverhostname>  -d <database> -U <user> -c "COPY table_name(column_name1,  column_name2,  column_name3) FROM STDIN with delimiter as ','" < my_csv.csv

Avoid division by zero

If expression1 and expression2 are equal, the NULLIF() function returns NULL. Otherwise, it returns expression1.
>>> SELECT NULLIF(column_name,0);