Step 4: Create a table - Amazon Redshift

Step 4: Create a table

After you create your new database, create tables to hold your data. Specify the column information when you create the table.

For example, to create a table named DEMO, run the following command.

CREATE TABLE Demo ( PersonID int, City varchar (255) );

By default, new database objects, such as tables, are created in the default schema named public created during data warehouse creation. You can use another schema to create database objects. For more information about schemas, see Managing database security in the Amazon Redshift Database Developer Guide.

You can also create a table using the schema_name.object_name notation to create the table in the SALES schema.

CREATE TABLE SALES.DEMO ( PersonID int, City varchar (255) );

To view and inspect schemas and their tables, you can use the Amazon Redshift query editor v2 . Or you can see the list of tables in schemas using system views. For more information, see Step 6: Query the system tables.

The encoding, distkey, and sortkey columns are used by Amazon Redshift for parallel processing. For more information about designing tables that incorporate these elements, see Amazon Redshift best practices for designing tables.

Insert data rows into a table

After you create a table, insert rows of data into that table.

Note

The INSERT command inserts rows into a table. For standard bulk loads, use the COPY command. For more information, see Use a COPY command to load data.

For example, to insert values into the DEMO table, run the following command.

INSERT INTO DEMO VALUES (781, 'San Jose'), (990, 'Palo Alto');

To insert data into a table that's in a specific schema, run the following command.

INSERT INTO SALES.DEMO VALUES (781, 'San Jose'), (990, 'Palo Alto');

Select data from a table

After you create a table and populate it with data, use a SELECT statement to display the data contained in the table. The SELECT * statement returns all the column names and row values for all of the data in a table. Using SELECT is a good way to verify that recently added data was correctly inserted into the table.

To view the data that you entered in the DEMO table, run the following command.

SELECT * from DEMO;

The result should look like the following.

personid | city ----------+----------- 781 | San Jose 990 | Palo Alto (2 rows)

For more information about using the SELECT statement to query tables, see SELECT.