Updated March 8, 2023
Definition of Redshift Insert Into
Redshift insert is used to insert rows into the specified table, we can insert single as well as multiple row in a single statement. We can use a column list at the time of inserting records into the specified column, if suppose we have not used any column list data will be inserted in order which was we have defined in our example. We can also insert a bulk amount of data in a single query, also we can insert data from another table by specifying table name in select query. Redshift insert into command is following PostgreSQL standard querying protocol.
Syntax:
Below is the syntax of insert into in redshift are as follows.
1) Insert single row without using column list –
Insert into name_of_table values (column1_value, column2_value, …, columnN_value);
2) Insert single row using column list –
Insert into name_of_table (column_name1, column_name2, …., column_nameN)
Values (column1_value, column2_value, …, columnN_value);
3) Insert multiple row without using column list –
Insert into name_of_table values (column1_value, column2_value, …, columnN_value);
(column1_value, column2_value, …, columnN_value);
………...,
(column1_value, column2_value, …, columnN_value);
4) Insert multiple row without using column list –
Insert into name_of_table values (column_name1, column_name2, …., column_nameN)
(column1_value, column2_value, …, columnN_value);
(column1_value, column2_value, …, columnN_value);
………...,
(column1_value, column2_value, …, columnN_value);
5) Insert row from another table –
Insert into name_of_table (select * from name_of_table);
6) Insert single row using default value –
Insert into name_of_table values (default, default, …, columnN_value);
Parameters:
1) Insert into – This command is used in redshift to insert rows into a particular table. We can insert single as well as multiple row in single statement by using the insert into command.
2) Table name – This parameter is very important while using insert into a statement in redshift. This is defined as the name of the table in which we have inserted the records. It’s a persistent or temporary table.
3) Values – This keyword is used with insert into command in redshift. The values list of column is align with the column list which was we have used in the query. While inserting multiple values in a single query we need to separate each value with “,” sign.
4) Column name – This is defined as the name of column in which we have inserted the values.
5) Default – Using this keyword we can insert the default value into the column, we have defined the default value at the time of table creation.
How insert into works in Redshift?
- Basically, redshift insert into the command is used to insert the records into the table. We have inserting single as well as multiple records in a single query.
- We can list a number of columns at the time of inserting records into the table. If suppose we have not used column name then it will insert null values.
- If suppose selected column not allowing null values it will return the error that we cannot pass empty values.
- The below example shows that we cannot insert null values into the column which contains the not null constraints.
- In the below example, we have trying to insert records into the redshift_insert table but it will show the error that we cannot insert the null value which contains the not null constraints.
- We have used null value in the second column but it not allowing to insert null values because we have defined not null constraints to the second column.
Code:
Insert into redshift_insert values (1, NULL, 'PQR', default, default);
Figure – we cannot insert null values into the column which contains the not null constraints.
- To insert the records into the table we need to insert or owner of the table privileges to the user.
- The below example shows that we need insert or owner of the table privileges to execute insert into command in redshift.
- In the below example, we have used the test user to insert the records into redshift_insert table but the test user has not privileged like insert or owner so it will show the error like permission denied for relation name as redshift_insert.
Code:
Insert into redshift_insert values (1, NULL, 'PQR', default, default);
Figure – we need insert or owner of the table privileges to execute insert into command.
- Redshift insert command is inserting new row into the specified table. If suppose we want to insert a large amount of data into the table we need to prefer copy command because insert is slower as compare to copy command.
- Insert is mostly preferable to insert a small number of records in redshift.
Examples
The below example shows that insert into redshift is as follows.
1) Insert single records into the table without using column list
The below example shows that insert single records into the table without using column list. We have to insert one row in redshift_insert table.
Code:
Insert into redshift_insert values (2, 'ABC', 'PQR', 'Pune', 'India');
Select * from redshift_insert;
2) Insert single records into the table using column list
The below example shows that insert single records into the table using column list. We have to insert one row in redshift_insert table.
Code:
Insert into redshift_insert (id, name, last_name, city, country) values (2, 'ABC', 'PQR', 'Pune', 'India');
Select * from redshift_insert;
3) Insert multiple records into the table without using column list
The below example shows that insert multiple records into the table without using column list. We have to insert two-row in redshift_insert table in a single statement.
Code:
Insert into redshift_insert values (3, 'ABC', 'PQR', 'Pune', 'India'), (4, 'ABC', 'PQR', 'Pune', 'India');
Select * from redshift_insert where id between 3 and 4;
4) Insert row from another table
In the below example, we have to insert records into redshift_insert1 table from redshift_insert table.
Code:
Insert into redshift_insert1 (select * from redshift_insert);
Select * from redshift_insert1;
5) Insert row using default value
In the below example, we have used the default value to insert rows into the redshift_insert table.
Code:
Insert into redshift_insert values (5, 'ABC', 'PQR', default, default);
Select * from redshift_insert where id = 5;
Conclusion
Redshift insert into is used to insert records into the table. Using insert into we can inset single or multiple records in a single query. To execute insert statement we need table owner or insert privileges. We can also use default value when inserting records into table.
Recommended Articles
This is a guide to Redshift Insert Into. Here we discuss the definition, How insert into works in Redshift? and examples for better understanding. You may also have a look at the following articles to learn more –
