Redshift tips

I am using Redshift since two years ago, and as every database it has its SQL dialect and its secrets. I will write here everything I discover and it is worth to be annotated.

AWS
PostgreSQL
Redshift

Temporary tables

Prepend an hash (#) to table name to create a temporary table.

CREATE TABLE #mytable AS SELECT 1;

Table #mytable will be clean up when session will be closed. Two simultanei sessions can create a temporary table with the same name with no conflicts.

yyyymmdd

I use an integer field to store dates in the YYYYMMDD format. It is really handy cause it can be sorted naturally, even in other contexts like folder or file naming conventions. It is true that first two digits 20xxxxxx are redundant but they make it really easy to humans to recognize it is a date, like 20170928 the day this article was published. So I use this format in many other databases, it crosses contexts easily and in this section I will write the snippets I use to convert it or manipulate it in Redshift, however…

Consider also to use the YYYY-MM-DD string format that has the same benefits and has also the advantage to be easier to cast in Redshift, for instance SELECT SYSDATE::DATE returns current timestamp in this format.

Convert timestamp to yyyymmdd

SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'), 99999999);
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD')::INTEGER;

First day of last week

 SELECT TO_CHAR(DATE_ADD('day', -7, DATE_TRUNC('week', SYSDATE)), 'YYYYMMDD');