Join us on
Star us on
Get Started
Slack
GitHub
Get Started
v2.5 (latest) v2.2 (stable) v2.1 (earlier version) v2.0 (earlier version) v1.3 (earlier version)
  • GET STARTED
    • Quick start
      • 1. Install YugabyteDB
      • 2. Create a local cluster
      • 3. Explore YSQL
      • 4. Build an application
        • Java
        • NodeJS
        • Go
        • Python
        • Ruby
        • C#
        • PHP
        • C++
        • C
    • Introduction
    • Explore core
      • 1. Linear scalability
      • 2. Fault tolerance
      • 3. Global distribution
      • 4. Auto sharding
      • 5. Tunable reads
      • 6. Observability
  • USER GUIDES
    • Develop
      • Learn app development
        • 1. SQL vs NoSQL
        • 2. Data modeling
        • 3. Data types
        • 4. ACID transactions
        • 5. Aggregations
        • 6. Batch operations
        • 7. Date and time
        • 8. Strings and text
      • Ecosystem integrations
        • Apache Kafka
        • Apache Spark
        • JanusGraph
        • KairosDB
        • Presto
        • Metabase
      • Real-world examples
        • E-Commerce App
        • IoT Fleet Management
        • Retail Analytics
      • Explore sample applications
    • Deploy
      • Checklist
      • Manual deployment
        • 1. System configuration
        • 2. Install software
        • 3. Start YB-Masters
        • 4. Start YB-TServers
        • 5. Verify deployment
      • Kubernetes
        • Helm Chart
        • Helm configuration
        • Local SSD
      • Docker
      • Public clouds
        • Amazon Web Services
        • Google Cloud Platform
        • Microsoft Azure
      • Pivotal Cloud Foundry
      • Yugabyte Platform
        • 1. Prepare cloud environment
        • 2. Install Admin Console
        • 3. Configure Admin Console
        • 4. Configure Cloud Providers
    • Benchmark
      • Performance
      • YCSB
      • Large datasets
    • Secure
      • Security checklist
      • Authentication
      • Authorization
        • 1. RBAC Model
        • 2. Create Roles
        • 3. Grant permissions
      • TLS encryption
        • 1. Prepare nodes
        • 2. Server-server encryption
        • 3. Client-server encryption
        • 4. Connect to cluster
      • Encryption at Rest
    • Manage
      • Backup and restore
        • Backing up data
        • Restoring data
      • Data migration
        • Bulk import
        • Bulk export
      • Change cluster config
      • Upgrade deployment
      • Diagnostics reporting
      • Yugabyte Platform
        • Create universe - Multi-zone
        • Create universe - Multi-region
        • Edit universe
        • Edit config flags
        • Health checking and alerts
        • Create and edit instance tags
        • Node status and actions
        • Read replicas
        • Back up and restore
        • Upgrade universe
        • Delete universe
    • Troubleshoot
      • Troubleshooting overview
      • Cluster level issues
        • YCQL connection issues
        • YEDIS connection Issues
      • Node level issues
        • Check processes
        • Inspect logs
        • System statistics
      • Yugabyte Platform
        • Troubleshoot universes
  • REFERENCE
    • APIs
      • YSQL
        • Statements
          • ABORT
          • ALTER DATABASE
          • ALTER DOMAIN
          • ALTER TABLE
          • BEGIN
          • COMMENT
          • COMMIT
          • COPY
          • CREATE DATABASE
          • CREATE DOMAIN
          • CREATE INDEX
          • CREATE SCHEMA
          • CREATE SEQUENCE
          • CREATE TABLE
          • CREATE TABLE AS
          • CREATE TYPE
          • CREATE USER
          • CREATE VIEW
          • DEALLOCATE
          • DELETE
          • DROP DATABASE
          • DROP DOMAIN
          • DROP SEQUENCE
          • DROP TABLE
          • DROP TYPE
          • END
          • EXECUTE
          • EXPLAIN
          • GRANT
          • INSERT
          • LOCK
          • PREPARE
          • RESET
          • REVOKE
          • ROLLBACK
          • SELECT
          • SET
          • SET CONSTRAINTS
          • SET TRANSACTION
          • SHOW
          • SHOW TRANSACTION
          • TRUNCATE
          • UPDATE
        • Data types
          • Binary
          • Boolean
          • Character
          • Date-time
          • Json
          • Money
          • Numeric
          • Serial
          • UUID
        • Expressions
          • currval()
          • lastval()
          • nextval()
        • Keywords
        • Reserved Names
      • YCQL
        • Quick Start YCQL
        • ALTER KEYSPACE
        • ALTER ROLE
        • ALTER TABLE
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE ROLE
        • CREATE TABLE
        • CREATE TYPE
        • DROP INDEX
        • DROP KEYSPACE
        • DROP ROLE
        • DROP TABLE
        • DROP TYPE
        • GRANT PERMISSION
        • GRANT ROLE
        • REVOKE PERMISSION
        • REVOKE ROLE
        • USE
        • INSERT
        • SELECT
        • UPDATE
        • DELETE
        • TRANSACTION
        • TRUNCATE
        • Simple Value
        • Subscript
        • Function Call
        • Operator Call
        • BLOB
        • BOOLEAN
        • MAP, SET, LIST
        • FROZEN
        • INET
        • Integer & Counter
        • Non-Integer
        • TEXT
        • Date & Time Types
        • UUID & TIMEUUID
        • JSONB
        • Date and time functions
    • CLIs
      • yb-ctl
      • yb-docker-ctl
      • yb-master
      • yb-tserver
      • ysqlsh
      • cqlsh
    • Sample data
      • Chinook
      • Northwind
      • PgExercises
      • SportsDB
    • Tools
      • TablePlus
  • RELEASES
    • Release history
      • v1.3.1
      • v1.3.0
      • v1.2.12
      • v1.2.11
      • v1.2.10
      • v1.2.9
      • v1.2.8
      • v1.2.6
      • v1.2.5
      • v1.2.4
  • CONCEPTS
    • Architecture
      • Design goals
      • Layered architecture
      • Basic concepts
        • Universe
        • YB-TServer
        • YB-Master
        • Acknowledgements
      • Query layer
        • Overview
      • DocDB store
        • Sharding
        • Replication
        • Persistence
        • Performance
      • DocDB transactions
        • Isolation Levels
        • Single row transactions
        • Distributed transactions
        • Transactional IO path
  • FAQ
    • Comparisons
      • CockroachDB
      • Google Cloud Spanner
      • MongoDB
      • FoundationDB
      • Amazon DynamoDB
      • Azure Cosmos DB
      • Apache Cassandra
      • Redis in-memory store
      • Apache HBase
    • Other FAQs
      • Product
      • Architecture
      • Yugabyte Platform
      • API compatibility
  • CONTRIBUTOR GUIDES
    • Get involved
  • Misc
    • YEDIS
      • Quick start
      • Develop
        • Client drivers
          • C
          • C++
          • C#
          • Go
          • Java
          • NodeJS
          • Python
      • API reference
        • APPEND
        • AUTH
        • CONFIG
        • CREATEDB
        • DELETEDB
        • LISTDB
        • SELECT
        • DEL
        • ECHO
        • EXISTS
        • EXPIRE
        • EXPIREAT
        • FLUSHALL
        • FLUSHDB
        • GET
        • GETRANGE
        • GETSET
        • HDEL
        • HEXISTS
        • HGET
        • HGETALL
        • HINCRBY
        • HKEYS
        • HLEN
        • HMGET
        • HMSET
        • HSET
        • HSTRLEN
        • HVALS
        • INCR
        • INCRBY
        • KEYS
        • MONITOR
        • PEXPIRE
        • PEXPIREAT
        • PTTL
        • ROLE
        • SADD
        • SCARD
        • RENAME
        • SET
        • SETEX
        • PSETEX
        • SETRANGE
        • SISMEMBER
        • SMEMBERS
        • SREM
        • STRLEN
        • ZRANGE
        • TSADD
        • TSCARD
        • TSGET
        • TSLASTN
        • TSRANGEBYTIME
        • TSREM
        • TSREVRANGEBYTIME
        • TTL
        • ZADD
        • ZCARD
        • ZRANGEBYSCORE
        • ZREM
        • ZREVRANGE
        • ZSCORE
        • PUBSUB
        • PUBLISH
        • SUBSCRIBE
        • UNSUBSCRIBE
        • PSUBSCRIBE
        • PUNSUBSCRIBE
> APIs > YSQL > Statements >

INSERT

Attention

This page documents an earlier version. Go to the latest (v2.3) version.
  • Synopsis
  • Syntax
  • Semantics
    • insert
    • table_name
    • column_names
    • VALUES clause
    • ON CONFLICT clause
    • returning_clause
    • column_values
    • conflict_target
    • conflict_action
  • Examples
  • See also

Synopsis

Use the INSERT statement to add one or more rows to the specified table.

Syntax

  • Grammar
  • Diagram
insert ::= [ WITH [ RECURSIVE ] with_query [ , ... ] ]  INSERT INTO 
           table_name [ AS alias ] [ ( column_names ) ]  
           { DEFAULT VALUES
             | VALUES ( column_values ) [ ,(column_values ... ]
             | subquery }  
           [ ON CONFLICT [ conflict_target ] conflict_action ]  
           [ returning_clause ]

returning_clause ::= RETURNING { * | { output_expression 
                                     [ [ AS ] output_name ] } 
                                     [ , ... ] }

column_values ::= { expression | DEFAULT } [ , ... ]

conflict_target ::= ( { column_name | expression } [ , ... ] ) 
                    [ WHERE condition ]
                    | ON CONSTRAINT constraint_name

conflict_action ::= DO NOTHING
                    | DO UPDATE SET update_item [ , ... ] 
                      [ WHERE condition ]

insert

WITHRECURSIVE,with_queryINSERTINTOtable_nameASalias(column_names)DEFAULTVALUESVALUES(column_values),(column_values)subqueryONCONFLICTconflict_targetconflict_actionreturning_clause

returning_clause

RETURNING*,output_expressionASoutput_name

column_values

,expressionDEFAULT

conflict_target

(,column_nameexpression)WHEREconditionONCONSTRAINTconstraint_name

conflict_action

DONOTHINGDOUPDATESET,update_itemWHEREcondition

Semantics

Constraints must be satisfied.

insert

table_name

Specify the name of the table. If the specified table does not exist, an error is raised.

column_names

Specify a comma-separated list of columns names. If a specified column does not exist, an error is raised. Each of the primary key columns must have a non-null value.

VALUES clause

  • Each of the values list must have the same length as the columns list.
  • Each value must be convertible to its corresponding (by position) column type.
  • Each value literal can be an expression.

ON CONFLICT clause

  • The target table must have at least one column (list) with either a unique index or a unique constraint. We shall refer to this as a unique key. The argument of VALUES is a relation that must include at least one of the target table's unique keys. Some of the values of this unique key might be new, and others might already exist in the target table.

  • The basic aim of INSERT ON CONFLICT is simply to insert the rows with new values of the unique key and to update the rows with existing values of the unique key to set the values of the remaining specified columns to those in the VALUES relation. In this way, the net effect is either to insert or to update; and for this reason the INSERT ON CONFLICT variant is often colloquially referred to as "upsert".

returning_clause

column_values

conflict_target

conflict_action

DO NOTHING | DO UPDATE SET *update_item* [ , ... ] [ WHERE *condition* ]

update_item

condition

Examples

First, the bare insert. Create a sample table.

postgres=# CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));

Insert some rows.

postgres=# INSERT INTO sample VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4, 'b'), (3, 4.0, 5, 'c');

Check the inserted rows.

postgres=# SELECT * FROM sample ORDER BY k1;
 k1 | k2 | v1 | v2
----+----+----+----
  1 |  2 |  3 | a
  2 |  3 |  4 | b
  3 |  4 |  5 | c

Next, a basic "upsert" example. Re-create and re-populate the sample table.

postgres=# DROP TABLE IF EXISTS sample CASCADE;
postgres=# CREATE TABLE sample(
  id int  CONSTRAINT sample_id_pk PRIMARY KEY,
  c1 text CONSTRAINT sample_c1_NN NOT NULL,
  c2 text CONSTRAINT sample_c2_NN NOT NULL);
postgres=# INSERT INTO sample(id, c1, c2)
  VALUES (1, 'cat'    , 'sparrow'),
         (2, 'dog'    , 'blackbird'),
         (3, 'monkey' , 'thrush');

Check the inserted rows.

postgres=# SELECT id, c1, c2 FROM sample ORDER BY id;
 id |   c1   |    c2å
----+--------+-----------
  1 | cat    | sparrow
  2 | dog    | blackbird
  3 | monkey | thrush

Demonstrate "on conflict do nothing". In this case, we don't need to specify the conflict target.

postgres=# INSERT INTO sample(id, c1, c2)
  VALUES (3, 'horse' , 'pigeon'),
         (4, 'cow'   , 'robin')
  ON CONFLICT
  DO NOTHING;

Check the result. The non-conflicting row with id = 4 is inserted, but the conflicting row with id = 3 is NOT updated.

postgres=# SELECT id, c1, c2 FROM sample ORDER BY id;
 id |   c1   |    c2
----+--------+-----------
  1 | cat    | sparrow
  2 | dog    | blackbird
  3 | monkey | thrush
  4 | cow    | robin

Demonstrate the real "upsert". In this case, we DO need to specify the conflict target. Notice the use of the EXCLUDED keyword to specify the conflicting rows in the to-be-upserted relation.

postgres=# INSERT INTO sample(id, c1, c2)
  VALUES (3, 'horse' , 'pigeon'),
         (5, 'tiger' , 'starling')
  ON CONFLICT (id)
  DO UPDATE SET (c1, c2) = (EXCLUDED.c1, EXCLUDED.c2);

Check the result. The non-conflicting row with id = 5 is inserted, and the conflicting row with id = 3 is updated.

postgres=# SELECT id, c1, c2 FROM sample ORDER BY id;
 id |  c1   |    c2
----+-------+-----------
  1 | cat   | sparrow
  2 | dog   | blackbird
  3 | horse | pigeon
  4 | cow   | robin
  5 | tiger | starling

We can make the "update" happen only for a specified subset of the excluded rows. We illustrate this by attempting to insert two conflicting rows (with id = 4 and id = 5) and one non-conflicting row (with id = 6). And we specify that the existing row with c1 = 'tiger' should not be updated with "WHERE sample.c1 <> 'tiger'".

INSERT INTO sample(id, c1, c2)
  VALUES (4, 'deer'   , 'vulture'),
         (5, 'lion'   , 'hawk'),
         (6, 'cheeta' , 'chaffinch')
  ON CONFLICT (id)
  DO UPDATE SET (c1, c2) = (EXCLUDED.c1, EXCLUDED.c2)
  WHERE sample.c1 <> 'tiger';

Check the result. The non-conflicting row with id = 6 is inserted; the conflicting row with id = 4 is updated; but the conflicting row with id = 5 (and c1 = 'tiger') is NOT updated;

postgres=# SELECT id, c1, c2 FROM sample ORDER BY id;
 id |   c1   |    c2     
----+--------+-----------
  1 | cat    | sparrow
  2 | dog    | blackbird
  3 | horse  | pigeon
  4 | deer   | vulture
  5 | tiger  | starling
  6 | cheeta | chaffinch

Notice that this restriction is legal too:

WHERE EXCLUDED.c1 <> 'lion'

Finally, a slightly more elaborate "upsert" example. Re-create and re-populate the sample table. Notice that id is a self-populating surrogate primary key and that c1 is a business unique key.

postgres=# DROP TABLE IF EXISTS sample CASCADE;
CREATE TABLE sample(
  id INTEGER GENERATED ALWAYS AS IDENTITY CONSTRAINT sample_id_pk PRIMARY KEY,
  c1 TEXT CONSTRAINT sample_c1_NN NOT NULL CONSTRAINT sample_c1_unq unique,
  c2 TEXT CONSTRAINT sample_c2_NN NOT NULL);
INSERT INTO sample(c1, c2)
  VALUES ('cat'   , 'sparrow'),
         ('deer'  , 'thrush'),
         ('dog'   , 'blackbird'),
         ('horse' , 'vulture');

Check the inserted rows.

postgres=# SELECT id, c1, c2 FROM sample ORDER BY c1;
 id |  c1   |    c2
----+-------+-----------
  1 | cat   | sparrow
  2 | deer  | thrush
  3 | dog   | blackbird
  4 | horse | vulture

Now do the upsert. Notice that we illustrate the usefulness of the WITH clause to define the to-be-upserted relation before the INSERT clause and use a subselect instead of a VALUES clause. We also specify the conflict column(s) indirectly by mentioniing the name of the unique constrained that covers them.

postgres=# WITH to_be_upserted AS (
  SELECT c1, c2 FROM (VALUES
    ('cat'   , 'chaffinch'),
    ('deer'  , 'robin'),
    ('lion'  , 'duck'),
    ('tiger' , 'pigeon')
   )
  AS t(c1, c2)
  )
  INSERT INTO sample(c1, c2) SELECT c1, c2 FROM to_be_upserted
  ON CONFLICT ON CONSTRAINT sample_c1_unq
  DO UPDATE SET c2 = EXCLUDED.c2;

Check the inserted rows.

postgres=# SELECT id, c1, c2 FROM sample ORDER BY c1;
 id |  c1   |    c2
----+-------+-----------
  1 | cat   | chaffinch
  2 | deer  | robin
  3 | dog   | blackbird
  4 | horse | vulture
  7 | lion  | duck
  8 | tiger | pigeon

See also

  • COPY
  • CREATE TABLE
  • SELECT
  • Synopsis
  • Syntax
  • Semantics
    • insert
    • table_name
    • column_names
    • VALUES clause
    • ON CONFLICT clause
    • returning_clause
    • column_values
    • conflict_target
    • conflict_action
  • Examples
  • See also
Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2020 Yugabyte, Inc. All rights reserved.