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

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

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.

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:

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.

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