Skip to main content

INSERT

Adds rows to a table.

Syntax​

INSERT INTO <table_name> [( <column_name> [, ...] )]
   {DEFAULT VALUES | VALUES ( {<expression> | DEFAULT} [, ...] ) [, ...] | <query>}
   [RETURNING * | <output_expr> [[AS] <output_name>] [, ...]]

Description​

The INSERT command allows you to insert one or more rows specified by value expressions or resulting from a query.

You can specify columns in the command to insert data only to the specified columns. The columns can be specified in any order. If you specify no columns, the columns of the table in declared order are used by default. The values obtained from the VALUES clause or query are associated with the explicit or implicit column list from left to right.

If a column is not explicitly or implicitly listed, it will be assigned its declared default value or null if it has no default value.

If the data type of the expression for a column is incorrect, the system will automatically convert the data type.

If you include RETURNING in your command, the command will also compute and return values based on each inserted row. If you want to obtain values supplied by defaults, such as sequence numbers, the RETURNING clause is extremely useful.

To execute INSERT on a table, you must have the INSERT privilege on the table. If you specify a list of columns in the INSERT command, you must also have the INSERT privilege on each listed column. If you also want to include RETURNING in your command, you must also have the SELECT privilege on each listed column.

Parameters​

  • <table_name>

    The name of the table. You can prefix the name with a schema name to insert the table in the specified schema.

  • <column_name>

    The name of the column. You can specify the name with a qualifier such as a subfield name or an array subscript.

  • [DEFAULT VALUES]

    The option to fill all columns with their default values.

  • [<expression> | DEFAULT]

    The expression or value to assign to the column. Alternatively, you can specify DEFAULT to fill the corresponding column with the default value.

  • <query>

    The query, that is a SELECT statement, used to supply the rows to insert.

  • <output_expression>

    The expression to be computed or returned after each row is inserted. Set this parameter to * to return all columns of the inserted rows.

  • <output_name>

    The name of the returned output.

Outputs​

After an INSERT command is successfully executed, a command tag in the following format is returned:

INSERT <oid> <count>

<count> indicates the number of inserted rows. <oid> indicates the object ID (OID). If <count> is 0 and the given table has OIDs, <oid> is the OID of the inserted row. Otherwise, <oid> is 0.

Examples​

Insert a row to table sales:

INSERT INTO sales VALUES (1, 2022, 1, 1, 'S', 'CA');

Insert a row to table sales without specifying a value for column qtr:

INSERT INTO sales (id, year, c_rank, code, region) 
VALUES (1, 2023, 1, 'S', 'CA');

Insert a row to table sales by specifying the DEFAULT clause for column year:

INSERT INTO sales (id, year, qtr, c_rank, code, region) 
VALUES (1, DEFAULT, 1, 1, 'S', 'CA');

Insert a row to table sales using default values for all columns:

INSERT INTO sales DEFAULT VALUES;

Insert multiple rows to table sales:

INSERT INTO sales (id, year, qtr, c_rank, code, region) 
VALUES 
(1, 2023, 1, 1, 'S', 'CA'),
(2, 2023, 2, 2, 'R', 'NY'),
(3, 2023, 3, 3, 'S', 'TX');

SQL standard compatibility​

INSERT in Relyt is fully compatible to the SQL standard. The only difference is that the SQL standard requires all columns to be filled from the VALUES clause or query, if no list of column names is specified in the INSERT command.