# How to Insert in Redshift

## Basic

The simplest way to insert a row in Redshift is to to use the `INSERT INTO` command and specify values for all columns. If you have 10 columns, you have to specify 10 values and they have to be in order how the table was defined:.

```sql
-- Assuming the users table has only three columns: first_name, last_name, and email, and in that order
INSERT INTO users VALUES ('John', 'Doe', 'john@doe.com');
```

## Specifying a Column List

It's optional, but specifying a column list before the `VALUES` keyword is highly recommended:

```sql
INSERT INTO users (first_name, last_name, email, birth_date, city, state)
VALUES ('John', 'Doe', 'john@doe.com','2000-01-01','Los Angeles','CA');
```

Having a column list has the following advantages:

*   You don't have to remember the column order as defined in the table.
*   You don't have to specify a value for all columns, just the required ones.
*   In case there are many columns, it is easier to match a value to the column it's intended for when you see it in the statement, rather than having to look at the table definition.
*   `INSERT` statements without a column lists are invalidated once a column is added or removed from the table. You need to modify your query to reflect the new or deleted column in order for them to work again.

## Inserting Multiple Rows

You can insert multiple rows in one `INSERT` statement by having multiple sets of values enclosed in parentheses. It's faster to do one bulk insert rather than multiple individual inserts.

```sql
INSERT INTO users (first_name, last_name)
VALUES
  ('John','Lennon'),
  ('Paul','McCartney'),
  ('George','Harrison'),
  ('Ringo','Starr');
```

You can also use `INSERT` with a `SELECT` command to copy data from an existing table. Note that the `VALUES` keyword is omitted:

```sql
INSERT INTO beta_users (first_name, last_name)
SELECT first_name, last_name
FROM users
where beta = 1;
```

## Inserting JSON Strings

While Redshift does not support the JSON datatype, you can still store properly formatted JSON strings in a `CHAR` or `VARCHAR` column. `VARCHAR` is needed if the strings include multi-byte characters.

```sql
INSERT INTO test_json
VALUES ('{"beta": true, "status": "for review", "test_count": 1, "test_array":[1,2,3]}');
```

Note that Amazon Web Services [recommends using JSON sparingly](https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html), because it does not leverage Redshift's design.

## Handling Conflicts/Duplicates

Note that primary keys and unique constraints are not enforced in Redshift. Ensure your data is clean and duplicates have been eliminated before inserting into Redshift.

How to Insert

# How to Update in Redshift

To update all rows in a Redshift table, just use the `UPDATE` statement without a `WHERE` clause:

```sql
UPDATE products SET brand='Acme';
```

You can also update multiple columns at a time:

```sql
UPDATE products SET brand='Acme', category='Home Appliances';
```

Usually you only want to update rows that match a certain condition. You do this by specifying a `WHERE` clause:

```sql
--This will update only one row that matches product_id=1
UPDATE products SET stocks=100, available=true
WHERE product_id=1;

--This will update multiple rows that match Category='Electronics'
UPDATE products SET stocks=50, available=true
WHERE category='Electronics';
```

How to Update

# How to Delete in Redshift

To delete rows in a Redshift table, use the `DELETE FROM` statement:

```sql
DELETE FROM products WHERE product_id=1;
```

The `WHERE` clause is optional, but you'll usually want it, unless you really want to delete every row from the table.

How to Delete

# How to Create a Table in Redshift

Here's an example of creating a `users` table in Redshift:

```sql
CREATE TABLE users (
  id INTEGER primary key, -- Auto incrementing IDs
  name character varying, -- String column without specifying a length
  created_at timestamp without time zone -- Always store time in UTC
);
```

This is also a chance to specify [not null constraints and default values in Redshift](https://popsql.com/learn-sql/redshift/how-to-add-or-remove-default-values-or-null-constraints-to-a-column-in-redshift/):

```sql
create table users (
  id BIGINT primary key,
  name character varying not null,
  active boolean default true
);
```

Redshift supports the following data types:

*   SMALLINT (INT2)
*   INTEGER (INT, INT4)
*   BIGINT (INT8)
*   DECIMAL (NUMERIC)
*   REAL (FLOAT4)
*   DOUBLE PRECISION (FLOAT8)
*   BOOLEAN (BOOL)
*   CHAR (CHARACTER)
*   VARCHAR (CHARACTER VARYING)
*   DATE
*   TIMESTAMP
*   TIMESTAMPTZ

You can also create temporary tables that will stick around for the duration of your session. This is helpful to break down your analysis into smaller pieces.

```sql
-- Create a temporary table called `scratch_users` with just an `id` column
create temporary table scratch_users (id integer);

-- Or create a temporary table based on the output of a select
create temp table active_users
as
select * from users where active is true;
```

This concludes the basics of creating tables in Redshift. In [How to Use DISTKEY, SORTKEY and Define Column Compression Encoding in Redshift](https://popsql.com/learn-sql/redshift/how-to-use-distkey-sortkey-and-define-column-compression-encoding-in-redshift/) we will cover more advanced, Redshift-specific table creation options.

How to Create a Table

# How to Use DISTKEY, SORTKEY and Define Column Compression Encoding in Redshift

Designing tables properly is critical to successful use of any database, and is emphasized a lot more in specialized databases such as Redshift. This articles talks about the options to use when creating tables to ensure performance, and continues from [Redshift table creation basics](https://popsql.com/learn-sql/redshift/how-to-create-a-table-in-redshift/).

## Selecting Sort Keys

When you create a table on Redshift, you can (and should) specify one or more columns as the sort key. You can think of a sort key as a specialized type of index, since Redshift does not have the regular indexes found in other relational databases. Redshift stores data on disk in sorted order according to the sort key, which has an important effect on query performance.

You choose sort keys based on the following criteria:

*   If recent data is queried most frequently, specify the timestamp column as the leading column.
*   If you frequently filter by a range of values or a single value on one column, that column should be the sort key.
*   Columns frequently used in joins should be used as the sort key

Here are some examples of defining the sort key:

```sql
-- sale_date is the timestamp column
CREATE TABLE sales (
  sale_id BIGINT NOT NULL PRIMARY KEY,
  sale_date timestamp NOT NULL SORTKEY,
  ... <other colums>
);

-- use the SORTKEY table attribute keyword to create a multi-column sort key
-- In this case searches are done frequently by the location columns,
-- so state and city are part of sort key
CREATE TABLE dim_customers (
  ... <some columns>...
  state VARCHAR,
  city VARCHAR
)
SORTKEY (state, city);
```

## Selecting Distribution Styles

When you create a Redshift cluster, you define the number of nodes you want to use. The nodes work in parallel to speed up query execution. This also means that when you load data into a table, Redshift distributes the rows of the table to each of the node slices according to the table's distribution style.

There are three distribution styles:

*   **EVEN Distribution**: This is the default and just uses a simple round-robin method to distribute data, regardless of values. This is appropriate when a table is not used in queries with joins or when there is no clear choice of distribution method between the next two.
*   **KEY Distribution**: The values in one column are used to determine the row distribution. Redshift will attempt to place matching values on the same node slice. Use this for tables that are frequently joined together so that Redshift will collocate the rows of the tables with the same values of the joining columns on the same node slices. This makes execution of the joins much faster since the matching values of the common columns are physically stored together.
*   **ALL Distribution**: Using this will cause a copy of the entire table to be stored on each node. This is normally used for small but frequently joined tables such as lookup tables.

Distribution of a table is defined using the `DISTSTYLE` and/or `DISTKEY`.

```sql
-- Specifying a column as DISTKEY automatically sets distribution style to KEY
CREATE TABLE sales (
  sale_id BIGINT NOT NULL PRIMARY KEY,
  sale_date timestamp NOT NULL SORTKEY,
  customer_id int DISTKEY,
  amount float
);

-- Use DISTSTYLE table attribute to set it to ALL
CREATE TABLE atrribute_lookup (
  attribute_id INT NOT NULL PRIMARY KEY,
  attribute_name VARCHAR
)
DISTSTYLE ALL;
```

## Specifying Column Compression Encoding

Compression is defined per column allows reduction of size of stored data, which reduces disk I/O and improves query performance.

You define compression per column, if you do not specify any, Redshift uses the following compression:

*   All columns in temporary tables are assigned RAW compression by default
*   Columns defined as sort keys are assigned RAW compression
*   BOOLEAN, REAL, and DOUBLE PRECISION columns are assigned RAW compression
*   All others are assigned LZO compression.

For example, if you want to force a VARCHAR column to use RAW compression:

```sql
CREATE TABLE atrribute_lookup (
  attribute_id INT NOT NULL PRIMARY KEY,
  attribute_name VARCHAR ENCODE RAW
);
```

See the [Redshift Documentation](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html#compression-encoding-list) for details on the different compression encodings.

How to Use DISTKEY, SORTKEY and Define Column Compression Encoding

# How to Drop a Table in Redshift

Dropping a table in Redshift is simple:

```sql
DROP TABLE users;
```

Care must be done before running this command. There is no "undo" function for it.

How to Drop a Table

# How to Rename a Table in Redshift

Redshift allows renaming a table using the `ALTER TABLE` syntax:

```sql
ALTER TABLE old_table_name RENAME TO new_table_name;
```

How to Rename a Table

# How to Truncate a Table in Redshift

Be very careful with this command. It will empty the contents of your Redshift table and there is no undo. This is useful in development, but you'll rarely want to do this in production.

```sql
TRUNCATE TABLE table_name;

--the TABLE keyword is actually optional:
TRUNCATE table_name;
```

How to Truncate a Table

# How to Duplicate a Table in Redshift

You can duplicate or "clone" a Redshift table's contents by executing a `CREATE TABLE ... AS SELECT` statement:

```sql
CREATE TABLE new_table AS SELECT * FROM original_table;
```

Please be careful when using this to clone big tables. This can take a lot of time and server resources.

Note also that `new_table` inherits ONLY the basic column definitions, null settings and default values of the `original_table`. It does not inherit table attributes.

To inherit all table definitions, use the `CREATE TABLE... LIKE` syntax:

```sql
CREATE TABLE new_table (LIKE original_table);
```

This makes the structure of `new_table` exactly like that of `original_table`, but _does not_ copy the data. To copy the data, you'll need `INSERT ... SELECT`:

```sql
INSERT INTO new_table SELECT * FROM original_table;
```

Again, be careful when doing this to big tables.

How to Duplicate a Table

# How to Add a Column in Redshift

Adding a column in Redshift involves using the `ALTER TABLE` command. The typical syntax is as follows:

```sql
ALTER TABLE promotions ADD expiry_date DATE;
```

Redshift supports the following data types:

*   SMALLINT (INT2)
*   INTEGER (INT, INT4)
*   BIGINT (INT8)
*   DECIMAL (NUMERIC)
*   REAL (FLOAT4)
*   DOUBLE PRECISION (FLOAT8)
*   BOOLEAN (BOOL)
*   CHAR (CHARACTER)
*   VARCHAR (CHARACTER VARYING)
*   DATE
*   TIMESTAMP

Here's an example of adding a string (varchar) column with a not null constraint:

```sql
ALTER TABLE users ADD nickname VARCHAR NOT NULL;
```

Adding a boolean column with a default value:

```sql
ALTER TABLE users ADD active BOOLEAN DEFAULT TRUE;
```

How to Add a Column

# How to Drop a Column in Redshift

Dropping a column in Redshift involves using the `ALTER TABLE` command. The typical syntax is as follows:

```sql
ALTER TABLE table_name DROP COLUMN column_name;

--The COLUMN keyword is actually optional
ALTER TABLE table_name DROP column_name;
```

How to Drop a Column

# How to Rename a Column in Redshift

Changing a column name in Redshift involves using the `ALTER TABLE` command:

```sql
ALTER TABLE products RENAME COLUMN product_name TO product_full_name;
```

How to Rename a Column

# How to Add or Remove Default Values or Null Constraints to a Column in Redshift

Other than renaming, Redshift does not allow changing a column's attributes. To add a default value or a null constraint to a column in Redshift, you need to choose one of the following methods:

## Method 1: Add a New Column, Copy, then Drop the Old

1.  [Add a new column](https://popsql.com/learn-sql/redshift/how-to-add-a-column-in-redshift/) to the table with the same data type as the original column, plus the default value
2.  Use [`update`](https://popsql.com/learn-sql/redshift/how-to-update-in-redshift/) to backfill the data
3.  [Drop the old column](https://popsql.com/learn-sql/redshift/how-to-drop-a-column-in-redshift/)
4.  [Rename the new column](https://popsql.com/learn-sql/redshift/how-to-rename-a-column-in-redshift/) to use the original column name

Here's an example of all the steps:

```sql
-- Add a new column with a default value
ALTER TABLE products ADD COLUMN stocks_new integer DEFAULT 0;

-- Or, if you want a not null constraint, add a new column with a not null constraint
ALTER TABLE products ADD COLUMN created_at NOT NULL;

-- Backfill the data
UPDATE products SET stocks_new=stocks;

-- Drop the old column
ALTER TABLE products DROP COLUMN stocks;

-- Rename the new column
ALTER TABLE products RENAME stocks_new TO stocks;
```

## Method 2: Recreate the Table

Some people don't like the previous method because it changes the order of the columns in the table with the recreated column ending up last. To avoid this, you can do the following instead:

1.  Create a new table using the basic [`CREATE TABLE`](https://popsql.com/learn-sql/redshift/how-to-create-a-table-in-redshift/) statement
2.  Use [`INSERT` with a `SELECT`](https://popsql.com/learn-sql/redshift/how-to-insert-in-redshift/) command to copy over the contents from old to new table.
3.  [Drop the old table](https://popsql.com/learn-sql/redshift/how-to-drop-a-table-in-redshift/)
4.  [Rename the new table](https://popsql.com/learn-sql/redshift/how-to-rename-a-table-in-redshift/) to use the original table name

```sql
-- Create the new table with the default value
CREATE TABLE products_new (stocks_new integer DEFAULT 0);

-- Or, create the new table with with a not null constraint
CREATE TABLE products_new (stocks_new integer NOT NULL);

-- Backfill the data
INSERT INTO products_new SELECT * FROM products;

-- Drop the old table
DROP TABLE products;

-- Rename the new table
ALTER TABLE products_new RENAME TO products;
```

Besides the overhead of copying over the contents of the whole table, another downside of this method is if you have other objects referring to the original table, such as [views](https://popsql.com/learn-sql/redshift/how-to-create-a-view-in-redshift/), you will also need to update/recreate them as they will not recognize the new table, even if it inherited the name of the original one.

How to Add or Remove Default Values or Null Constraints to a Column

# How to Create an Index in Redshift

Being a columnar database specifically made for data warehousing, Redshift has a different treatment when it comes to indexes. It does not support regular indexes usually used in other databases to make queries perform better. Instead, you choose distribution styles and sort keys when you follow recommended practices in [How to Use DISTKEY, SORTKEY and Define Column Compression Encoding in Redshift](https://popsql.com/learn-sql/redshift/how-to-use-distkey-sortkey-and-define-column-compression-encoding-in-redshift/).

Redshift does support creation of unique, primary key and foreign key indexes (referred to in the [Redshift Documentation](https://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html) as constraints). However, the same documentation states that these are informational only and are not enforced. It is recommended to use them if your data loading process ensures their integrity, as they are used as planning hints to optimize query execution. However, you should not define them if you doubt their validity. For example, if you are not sure if the product name is really unique for a table, do not create the unique key constraint for it. But if you are certain, then do create it so the database engine can use it when executing your queries.

To add a constraint to a table, use the `ALTER TABLE` command:

```sql
-- Add primary key to a table
ALTER TABLE table_1 ADD PRIMARY KEY (col1);

-- It is recommended to specify a constraint name using the CONSTRAINT keyword
-- Here we also show you can have more than one column in the constraint definition
ALTER TABLE table_2 ADD CONSTRAINT table_2_pk PRIMARY KEY (col1, col2);

-- Add a unique key
ALTER TABLE table_3 ADD CONSTRAINT table_3_uq UNIQUE (col1);

-- Add a foreign key
ALTER TABLE products ADD CONSTRAINT products_categories_fk
FOREIGN KEY (category_id) REFERENCES categories (category_id);
```

How to Create an Index

# How to Drop an Index in Redshift

This article deals with removing primary key, unique keys and foreign key constraints from a table. Please refer to [Creating Indexes](https://popsql.com/learn-sql/redshift/how-to-create-an-index-in-redshift/) to understand the different treatment of indexes/constraints in Redshift.

To remove a constraint from a table, use the `ALTER TABLE.. DROP CONSTRAINT` command:

```sql
-- If you don't know the name of the constraint, you can look it up using:
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name='your_table_name';

-- Then drop the constraint using the ALTER TABLE command:
ALTER TABLE DROP CONSTRAINT constraint_name;
```

How to Drop an Index

# How to Create a View in Redshift

Views allow to encapsulate or "hide" complexities.

To create a Redshift view, use the `CREATE VIEW` command:

```sql
CREATE OR REPLACE VIEW view_name AS <select statement>;
```

While optional, the `OR REPLACE` part is frequently used so the the view is updated if already exists.

Some examples:

```sql
-- A view to show only beta users
CREATE VIEW beta_users_vw AS
SELECT * FROM users WHERE beta = 1;

-- A view to limit read access to only certain columns
CREATE VIEW users_basic_vw AS
SELECT first_name, last_name, telephone_number
FROM users;
```

You may notice all view names above end in `_vw`. This is an example convention some developers adopt to easily distinguish views from tables.

Redshift view creation may include the `WITH NO SCHEMA BINDING` clause. This specifies that the view is not bound to the underlying database objects, such as tables and user-defined functions. This means you can create a view even if the referenced objects don't exist and you can drop or alter a referenced object without affecting the view. One restriction with using this clause is that the tables and views referenced in the `SELECT` statement must be qualified with a schema name.

```sql
-- this won't work because the referenced object does not have the schema name
CREATE OR REPLACE VIEW this_view
AS SELECT col1, col2
FROM events
WITH NO SCHEMA BINDING;

-- the corrected statement has the schema name of the referenced table
CREATE OR REPLACE VIEW this_view
AS SELECT col1, col2
FROM public.events
WITH NO SCHEMA BINDING;
```

How to Create a View

# How to Drop a View in Redshift

To drop a Redshift view, use the `DROP VIEW` command:

```sql
DROP VIEW view_name;
```

You can also add the `IF EXISTS` option to prevent errors when trying to drop a non-existent view.

```sql
DROP VIEW IF EXISTS view_name;
```

How to Drop a View

# Redshift Date Functions: timestamp, datediff, dateadd, sysdate, date\_part

Redshift has the following functions/methods to get the current date and time:

```sql
select now();  -- date and time in string datatype
select sysdate; -- date and time in timestamp datatype
select current_date; -- date in date format
select current_time; -- time in timestamp format
```

Note the difference between `now()` and the other methods with regard to the datatype of the returned value. This means you must use `sysdate()` or `current_date` when doing date and time manipulation.

To find rows between two dates or timestamps:

```sql
SELECT *
FROM events
where event_date between '2018-01-01' and '2018-01-31';

-- Can include time by specifying in YYYY-MM-DD hh:mm:ss format
-- Can also use the methods above except for now()
SELECT *
FROM events
WHERE event_date BETWEEN '2018-01-01 12:00:00' AND current_date;
```

To find rows created within the last week:

```sql
SELECT *
FROM events
WHERE event_date > dateadd(week, -1, sysdate);
```

The `dateadd()` function accepts `second`, `minute`, `hour`, `day`, `week`, `month`, and `year`. To get the complete list of possible values, please refer to the [Redshift Documentation](https://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html).

You can extract part of a timestamp by applying the `EXTRACT()` or `DATE_PART()` functions:

```sql
select extract(minute from sysdate);  -- hour, day, month, year, century
select date_part(minute, sysdate);  -- hour, day, month, year, century

-- returns 0-6 (integer), where 0 is Sunday and 6 is Saturday
SELECT extract(dow from sysdate);
SELECT extract(dow, sysdate);

-- returns a string like monday, tuesday, etc
select to_char(sysdate, 'day');
```

For all possible values to use with `EXTRACT()` and `DATE_PART()` please refer to the [Redshift Documentation](https://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html).

To convert a timestamp to a unix timestamp (integer seconds):

```sql
-- This assumes midnight
SELECT date_part(epoch, '2018-09-20');

-- You can specify an exact timestamp to be converted down to the second
SELECT date_part(epoch, '2018-12-09 14:53:21');

-- calling unix_timestamp without a parameter will be like calling it for current timestamp
SELECT date_part(epoch, sysdate);
```

To calculate the difference between two timestamps, you can use the `DATEDIFF()` command:

```sql
-- show weeks between two dates
select datediff(week, '2018-01-01', '2018-12-31') as numweeks;

-- show hours between since a specific date
select datediff(hour, '2018-12-01', sysdate) as numhours;
```

`DATEDIFF()` accepts the same values as `EXTRACT()` and `DATE_PART()` for its first parameter (eg `week`, `day`, `hour`, etc).

How to Query Date and Time

# How to Group by Time in Redshift

When you want to group by minute, hour, day, week, etc, it's tempting to just group by your timestamp column, however, then you'll get one group per second, which is likely not what you want. Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc. The Redshift function you need here is `TO_CHAR()`:

```sql
SELECT
   to_char(created_at, 'YYYY-MM-DD HH24:MI'), -- leave out :MI if you want to group by hour
   count(1)
FROM users
GROUP BY 1;
```

Grouping by date is easier as you can just use the `TRUNC()` function:

```sql
SELECT
   trunc(created_at),
   count(1)
FROM users
GROUP BY 1;
```

If you don't have new users for every minute/hour/day, you're going to have gaps in your data. To have one row per interval, even when there's no data, you'll want to [generate data](https://popsql.com/learn-sql/redshift/how-to-avoid-gaps-in-data-in-redshift/) for it.

How to Group by Time

# How to Round Timestamps in Redshift

Rounding/truncating timestamps are especially useful when you're [grouping by time](https://popsql.com/learn-sql/redshift/how-to-group-by-time-in-redshift/). The Redshift function you need here is `TO_CHAR()`:

```sql
select to_char(sysdate, 'YYYY-MM-DD HH24:MI');
```

Truncating to date or year is easier because you can just use the `TRUNC()` and `EXTRACT()` functions, respectively:

```sql
select trunc(sysdate);
select extract(year from sysdate);
```

How to Round Timestamps

# Convert Timezone in Redshift from UTC to Local Time Zone

To convert a Redshift timestamp from one time zone to another, use the `convert_timezone()` function:

```sql
-- convert_timezone(source, target, timestamp);
SELECT convert_timezone('PST', 'EST', '2018-12-21 13:42:11');
```

The source time zone is optional. If omitted, Redshift will assume it is in UTC.

```sql
SELECT convert_timezone('EST', sysdate);
```

To check the supported time zone names and abbreviations, you can execute the following commands:

```sql
SELECT pg_timezone_names();
SELECT pg_timezone_abbrevs();
```

How to Convert UTC to Local Time Zone

# How to Use Coalesce in Redshift

Say you're looking at a Redshift integer column where some rows are null:

```sql
select
  day,
  tickets
from stats;
```

```sql
    day     | tickets
------------+-------
 2018-01-01 |     1
 2018-01-02 |   null
 2018-01-03 |     3
```

Instead of having that null, you might want that row to be `0`. To do that, use the `coalesce` function, which returns the first non-null argument it's passed:

```sql
select
  day,
  coalesce(tickets, 0)
from stats;
```

```sql
    day     | tickets
------------+-------
 2018-01-01 |     1
 2018-01-02 |     0
 2018-01-03 |     3
```

How to Use Coalesce

# How to Get First Row Per Group in Redshift using row\_number Function

Let's say we want to see the first order for every customer for a certain time period. This means we need to order the orders for every customer first. You can use `row_number()` for this:

```sql
SELECT
  *,
  row_number() OVER (PARTITION BY customer_id ORDER BY orderdate ASC) AS row_number
FROM lineorder
WHERE orderdate BETWEEN '2018-11-01' AND '2018-11-30';
```

This gives the orders for the time period, plus the `row_number` that resets for each customer. Since we only want to see the first order for each customer, we only want rows that have `row_number = 1`. So we can include this in either a subquery, or better yet a [common table expression](https://popsql.com/learn-sql/redshift/how-to-write-a-common-table-expression-in-redshift/).:

```sql
WITH orders AS (
  SELECT
   *,
   row_number() OVER (PARTITION BY customer_id ORDER BY orderdate ASC) AS row_number
  FROM lineorder
  WHERE orderdate BETWEEN '2018-11-01' AND '2018-11-30'
)
SELECT * FROM orders WHERE row_number = 1;
```

How to Get First Row Per Group

# How to Avoid Gaps in Data in Redshift

If you're [grouping by time](https://popsql.com/learn-sql/redshift/how-to-group-by-time-in-redshift/) and you don't want gaps in your report data, you need to generate a series of time values and use it to do an outer join with your data. Redshift does not offer any special function for this, because the recommended way for data warehouses is to [have a time dimension table](https://www.kimballgroup.com/1997/07/its-time-for-time/) that has all the possible time values.

Still if you need to generate a series of time values, you can do so by selecting from an arbitrary big table - one that has at least the number of rows as the number of values you want to generate:

```sql
-- This generates 60 days, the latest being the current date
SELECT (getdate()::date - row_number() OVER (ORDER BY true))::date AS day
FROM orders
LIMIT 60;
```

How to Avoid Gaps in Data

# How to Do Type Casting in Redshift

Redshift follows the same syntax as PostgreSQL for type casting. Here are some examples:

```sql
-- Cast text to boolean
select 'true'::boolean;

-- Cast float to integer
select 1.0::integer;

-- Cast integer to float
select '3.33'::float;
select 10/3.0; -- This will return a float too

-- Cast text to integer
select '1'::integer;

-- Cast text to timestamp
select '2018-01-01 09:00:00'::timestamp;

-- Cast text to date
select '2018-01-01'::date;

-- Cast text to interval
select '1 minute'::interval;
select '1 hour'::interval;
select '1 day'::interval;
select '1 week'::interval;
select '1 month'::interval;
```

How to Do Type Casting

# How to Write a Common Table Expression in Redshift

Common table expressions (CTEs) are a great way to break up complex queries. Using CTEs usually result in a more readable and maintainable query versus using subqueries. Here's a simple query to illustrate how to write a CTE:

```sql
with beta_users as (
  select *
  from users
  where beta is true
)
select events.*
from events
inner join beta_users on (beta_users.id = events.user_id);
```

You can find more complex examples of using CTEs in [How to Avoid Gaps in Series Data in Redshift](https://popsql.com/learn-sql/redshift/how-to-avoid-gaps-in-data-in-redshift/) and in [Calculating Cumulative Sums in Redshift](https://popsql.com/learn-sql/redshift/how-to-calculate-cumulative-sum-running-total-in-redshift/).

How to Write a Common Table Expression

# How to Import a CSV in Redshift

Importing a CSV into Redshift requires you to [create a table](https://popsql.com/learn-sql/redshift/how-to-create-a-table-in-redshift/) first. [Duplicating an existing table's structure](https://popsql.com/learn-sql/redshift/how-to-duplicate-a-table-in-redshift/) might be helpful here too.

The easiest way to load a CSV into Redshift is to first upload the file to an Amazon S3 Bucket. You can follow the [Redshift Documentation](https://docs.aws.amazon.com/redshift/latest/dg/tutorial-loading-data-upload-files.html) for how to do this. After that you can use the `COPY` command to tell Redshift to pull the file from S3 and load it to your table.

```sql
COPY table_name
FROM 's3://<your-bucket-name>/load/file_name.csv'
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>'
CSV;
```

Note that you need the `CSV` keyword at the end, else Redshift will assume that the file contents uses the pipe character (|) as delimiter.

If your CSV file has a different column arrangement as the table or does not contain all columns, you need to specify a column list:

```sql
COPY table_name (col1, col2, col3, col4)
FROM 's3://<your-bucket-name>/load/file_name.csv'
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>'
CSV;
```

Finally, if the file has header rows to be ignored, you can specify the number of lines to be skipped:

```sql
-- Ignore the first line
COPY table_name (col1, col2, col3, col4)
FROM 's3://<your-bucket-name>/load/file_name.csv'
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>'
CSV
INGOREHEADER 1;
```

For a complete list of options for `COPY`, please refer to the [Redshift COPY Command Specification](https://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html).

How to Import a CSV

# How to Compare Two Values When One is Null in Redshift

Imagine you're comparing two Redshift columns and you want to know how many rows are different. No problem, you think:

```sql
select count(1)
from items
where width != height;
```

Not so fast. If some of the widths or heights are null, they won't be counted! Surely that wasn't your intention. In other databases, there are null-aware comparison operators you can use, like [PostgreSQL's `is distinct from`](https://popsql.com/learn-sql/postgresql/how-to-group-by-time-in-postgresql/). In Redshift, you can achieve similar functionality by using the `coalesce()` function.

```sql
select count(1)
from items
where coalesce(width, 0) != coalesce(height, 0);
```

Now, your count will be "null aware" and you'll get the result you want 💥

How to Compare Two Values When One is Null

# How to Write a Case Statement in Redshift

Case statements are useful when you're dealing with multiple `IF` statements in your select clause. It comes in two forms:

```sql
-- Redshift documentation calls this form a searched CASE expression.
SELECT
  CASE
    WHEN score < 70 THEN 'failed'
    WHEN score BETWEEN 70 AND 80 THEN 'passed'
    WHEN score BETWEEN 81 AND 90 THEN 'very good'
    ELSE 'outstanding'
  END AS performance
FROM test_scores;

-- Redshift documentation calls this for a simple case expression
SELECT
  CASE grade
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Good'
    WHEN 'C' THEN 'Needs Improvement'
    ELSE 'Failed'
  END AS grade_interpretation
FROM grades;
```

How to Write a Case Statement

# How to Query a JSON Column in Redshift using json\_extract\_path\_text

You can store JSON in Redshift as a `CHAR` or `VARCHAR` column, but [Amazon Web Services recommends using JSON sparingly](https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html), because it does not leverage Redshift's design.

Here are examples of what you can do with JSON values in Redshift:

```sql
-- Check if a field contains a valid JSON-formatted value
select is_valid_json(json_column) from table_name;

-- Check if a field contains a valid JSON array
select is_valid_json_array(json_column) from table_name;

-- Extract the value of `status` from a column named json_values
select json_extract_path_text(json_values, 'status')
from event_attributes;

-- Look for rows that have status: live in json_values column
select *
from event_attributes
where json_extract_path_text(json_values, 'status') = 'live';

-- Get the array length from a JSON array
select json_array_length(json_column) from table_name;

-- Get the 2nd element from a JSON array (first element is at position 0)
-- 3rd optional parameter tells Redshift to return NULL if not a valid JSON array
select json_extract_array_element_text(json_array_column, 1, true)
from event_attributes;

-- Combine JSON_EXTRACT_ARRAY_ELEMENT_TEXT with JSON_EXTRACT_PATH_TEXT
-- to extract an array element nested within a JSON value
select json_extract_array_element_text(json_extract_path_text(json_column, 'user_ids'), 2)
from event_attributes;
```

How to Query a JSON Column

# How to Have Multiple Counts in Redshift

To do multiple counts in one query in Redshift, you can combine `COUNT()` with `CASE`:

```sql
select
  count(1), -- count all users
  count(case when gender = 'male' then 1 else 0 end), -- count male users
  count(case when beta = true then 1 else 0 end) -- count beta users
  count(case when beta = false then 1 else 0 end) -- count active non-beta users
from users;
```

How to Have Multiple Counts

# How to Calculate Cumulative Sum-Running Total in Redshift

Let's say we have a sales table in Redshift and we want to see a hockey stick graph of our sales quantity by day. First, we'll need a table with a day column and a count column:

```sql
select
  date(sales_date) as day,
  sum(qty) as total_sold
from sales
group by day;

day        | total_sold
-----------+------------
2018-12-01 |	10
2018-12-02 |	40
2018-12-03 |	40
2018-12-04 |	10
2018-12-05 |	30
2018-12-06 |	20
2018-12-07 |	35
2018-12-08 |	30
```

Next, we'll write a [Redshift common table expression (CTE)](https://popsql.com/learn-sql/redshift/how-to-write-a-common-table-expression-in-redshift/) and use a windowed `SUM()`function to get the cumulative sum/running total:

```sql
with _data as (
  select
    date(sales_date) as day,
    sum(qty) as total_sold
  from sales
  group by day
)
select
  day,
  total_sold,
  sum(total_sold) over (order by day rows unbounded preceding) as cumulative_sum
from _data;

day        | total_sold | cumulative_sum
-----------+------------|----------------
2018-12-01 |	10      |	10
2018-12-02 |	40      |	50
2018-12-03 |	40      |	90
2018-12-04 |	10      |	100
2018-12-05 |	30      |	130
2018-12-06 |	20      |	150
2018-12-07 |	35      |	185
2018-12-08 |	30      |	215
```

How to Calculate Cumulative Sum-Running Total

# How to Calculate Percentiles in Redshift

Let's say we want to look at the percentiles for query durations. We can use the Redshift `percentile_cont` function to do that:

```sql
select
  percentile_cont(0.25) within group (order by duration asc) as percentile_25,
  percentile_cont(0.50) within group (order by duration asc) as percentile_50,
  percentile_cont(0.75) within group (order by duration asc) as percentile_75,
  percentile_cont(0.95) within group (order by duration asc) as percentile_95
from query_durations
```

If we want to view those percentiles by day:

```sql
select
  day,
  percentile_cont(0.25) within group (order by duration asc) over (partition by day) as percentile_25,
  percentile_cont(0.50) within group (order by duration asc) over (partition by day) as percentile_50,
  percentile_cont(0.75) within group (order by duration asc) over (partition by day) as percentile_75,
  percentile_cont(0.95) within group (order by duration asc) over (partition by day) as percentile_95
from query_durations
group by 1
order by 1 asc
```

Redshift

Ready for a modern SQL editor?