---
name: "insert"
section: "7l"
platform: "linux"
package: "postgresql-client"
version: "7.2.1-2woody8"
release: "Debian 3.0 (Woody)"
platforms: ["linux"]
url: "https://new.carta.tech/insert/7l"
---

# insert(7l)

create new rows in a table

## NAME

INSERT - create new rows in a table

## SYNOPSIS

```
INSERT INTO table [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( expression [, ...] ) | SELECT query }

```

### INPUTS

- *table*

  The name of an existing table.

- *column*

  The name of a column in *table*.

- **DEFAULT VALUES**

  All columns will be filled by NULLs or by values specified when the table was created using DEFAULT clauses.

- *expression*

  A valid expression or value to assign to *column*.

- *query*

  A valid query. Refer to the SELECT statement for a further description of valid arguments.

### OUTPUTS

- **INSERT** *oid* **1**

  Message returned if only one row was inserted. *oid* is the numeric OID of the inserted row.

- **INSERT 0** *#*

  Message returned if more than one rows were inserted. *#* is the number of rows inserted.

## DESCRIPTION

**INSERT** allows one to insert new rows into a table. One can insert a single row at a time or several rows as a result of a query. The columns in the target list may be listed in any order.

Each column not present in the target list will be inserted  using a default value, either a declared DEFAULT value or NULL. PostgreSQL will reject the new column if a NULL is inserted into a column declared NOT NULL.

If the expression for each column is not of the correct data type, automatic type coercion will be attempted.

You must have insert privilege to a table in order to append to it, as well as select privilege on any table specified in a WHERE clause.

## USAGE

Insert a single row into table films:

```
INSERT INTO films VALUES
    ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');

```

In this second example the last column len is omitted and therefore it will have the default value of NULL:

```
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');

```

Insert a single row into table distributors; note that only column name is specified, so the omitted column did will be assigned its default value:

```
INSERT INTO distributors (name) VALUES ('British Lion');

```

Insert several rows into table films from table tmp:

```
INSERT INTO films SELECT * FROM tmp;

```

Insert into arrays (refer to the *PostgreSQL User's Guide* for further information about arrays):

```
-- Create an empty 3x3 gameboard for noughts-and-crosses
-- (all of these queries create the same board attribute)
INSERT INTO tictactoe (game, board[1:3][1:3])
    VALUES (1,'{{"","",""},{},{"",""}}');
INSERT INTO tictactoe (game, board[3][3])
    VALUES (2,'{}');
INSERT INTO tictactoe (game, board)
    VALUES (3,'{{,,},{,,},{,,}}');

```

## COMPATIBILITY

### SQL92

**INSERT** is fully compatible with SQL92. Possible limitations in features of the  *query* clause are documented for SELECT \[**select**(l)\].