Join us on
Star us on
Get Started
Slack
GitHub
Get Started
v2.0 (latest version) v1.3 (earlier version) v1.2 (earlier version) v1.1 (earlier version) v1.0 (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
      • Linear scalability
      • Fault tolerance
      • Global distribution
      • Auto-sharding
      • Tunable reads
      • Observability
      • Two data center deployment
  • 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
      • Work with GraphQL
        • Hasura
        • Prisma
      • Explore sample applications
    • Deploy
      • Deployment 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
        • Rook operator
        • YugabyteDB operator
      • Docker
      • Public clouds
        • Amazon Web Services
        • Google Cloud Platform
        • Microsoft Azure
      • Change data capture (CDC)
        • Using the Yugabyte CDC connector
        • CDC to Kafka
        • CDC to stdout
      • Two data center (2DC)
      • 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
        • Authentication
        • Fine-grained authentication
      • Authorization
        • RBAC model
        • Create roles
        • Grant privileges
      • Encryption in transit
        • 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
        • Backing Up Data using Snapshots
      • Data migration
        • Bulk import
        • Bulk export
      • Change cluster configuration
      • Diagnostics reporting
      • Upgrade deployment
      • Yugabyte Platform
        • Create universe - Multi-zone
        • Create universe - Multi-region
        • Edit universe
        • Edit configuration options
        • 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
      • Cluster level issues
        • YCQL connection issues
        • YEDIS connection Issues
        • Recover tserver/master
      • Node level issues
        • Check processes
        • Inspect logs
        • System statistics
      • Yugabyte Platform
        • Troubleshoot universes
  • REFERENCE
    • APIs
      • YSQL
        • Statements
          • ABORT
          • ALTER DATABASE
          • ALTER DEFAULT PRIVILEGES
          • ALTER DOMAIN
          • ALTER GROUP
          • ALTER POLICY
          • ALTER ROLE
          • ALTER SEQUENCE
          • ALTER TABLE
          • ALTER USER
          • BEGIN
          • COMMIT
          • COPY
          • CREATE AGGREGATE
          • CREATE CAST
          • CREATE DATABASE
          • CREATE DOMAIN
          • CREATE FUNCTION
          • CREATE GROUP
          • CREATE INDEX
          • CREATE OPERATOR
          • CREATE OPERATOR CLASS
          • CREATE POLICY
          • CREATE PROCEDURE
          • CREATE ROLE
          • CREATE RULE
          • CREATE SCHEMA
          • CREATE SEQUENCE
          • CREATE TABLE
          • CREATE TABLE AS
          • CREATE TRIGGER
          • CREATE TYPE
          • CREATE USER
          • CREATE VIEW
          • DEALLOCATE
          • DELETE
          • DO
          • DROP AGGREGATE
          • DROP CAST
          • DROP DATABASE
          • DROP DOMAIN
          • DROP FUNCTION
          • DROP GROUP
          • DROP OPERATOR
          • DROP OPERATOR CLASS
          • DROP OWNED
          • DROP POLICY
          • DROP PROCEDURE
          • DROP ROLE
          • DROP RULE
          • DROP SEQUENCE
          • DROP TABLE
          • DROP TRIGGER
          • DROP TYPE
          • DROP USER
          • END
          • EXECUTE
          • EXPLAIN
          • GRANT
          • INSERT
          • LOCK
          • PREPARE
          • REASSIGN OWNED
          • RESET
          • REVOKE
          • ROLLBACK
          • SELECT
          • SET
          • SET CONSTRAINTS
          • SET ROLE
          • SET SESSION AUTHORIZATION
          • SET TRANSACTION
          • SHOW
          • SHOW TRANSACTION
          • TRUNCATE
          • UPDATE
        • Data types
          • Binary
          • Boolean
          • Character
          • Date-time
          • Json
          • Money
          • Numeric
          • Serial
          • UUID
        • Expressions
          • currval()
          • lastval()
          • nextval()
        • Extensions
        • 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
      • ysqlsh
      • cqlsh
      • yb-admin
    • Configuration
      • YB-TServer
      • YB-Master
      • Default ports
    • Tools
      • DBeaver
      • pgAdmin
      • SQL Workbench/J
      • TablePlus
      • Visual Studio Code
    • Sample data
      • Chinook
      • Northwind
      • PgExercises
      • SportsDB
    • Connectors and drivers
      • PostgreSQL JDBC Driver
      • YugabyteDB JDBC Driver
      • Kafka Connect YugabyteDB
      • Spring Data YugabyteDB
  • RELEASES
    • Release history
      • v2.0.7
      • v2.0.6
      • v2.0.5
      • v2.0.3
      • v2.0.1
      • v2.0.0
      • 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
      • Key concepts
        • Universe
        • YB-TServer
        • YB-Master
        • Acknowledgements
      • Core functions
        • Universe creation
        • Table creation
        • Write IO path
        • Read IO path
        • High availability
      • Layered architecture
      • Query layer
        • Overview
      • DocDB store
        • Sharding
        • Replication
        • Persistence
        • Performance
      • DocDB transactions
        • Isolation Levels
        • Single row transactions
        • Distributed transactions
        • Transactional IO path
      • Change data capture (CDC)
      • Two data center (2DC) deployments
  • 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
  • CONTRIBUTE
    • Get involved
    • Core database
      • Checklist
      • Building the source
      • Running the Tests
  • 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 > YCQL >

INSERT


    • Synopsis
    • Syntax
      • Diagram
      • using_expression
      • ttl_or_timestamp_expression
      • Grammar
    • Semantics
      • VALUES clause
      • IF clause
      • USING clause
    • Examples
      • Insert a row into a table
      • Conditional insert using the IF clause
      • Insert a row with expiration time using the USING TTL clause
      • Insert a row with USING TIMESTAMP clause.
        • Insert a row with a low timestamp.
        • Overwrite the row with a higher timestamp.
        • Try to overwrite the row with a lower timestamp.
    • See also

Synopsis

The INSERT statement adds a row to a specified table.

Syntax

Diagram

INSERTINTOtable_name(,column_name)VALUES(,expression)IFNOTEXISTSif_expressionUSINGusing_expression

using_expression

using_expression = ttl_or_timestamp_expression { 'AND' ttl_or_timestamp_expression };

ANDttl_or_timestamp_expression

ttl_or_timestamp_expression

ttl_or_timestamp_expression = 'TTL' ttl_expression | 'TIMESTAMP' timestamp_expression;

TTLttl_expressionTIMESTAMPtimestamp_expression

Grammar

insert ::= INSERT INTO table_name '(' column [ ',' column ... ] ')'
               VALUES '(' value [ ',' value ... ] ')'
               [ IF { [ NOT ] EXISTS | if_expression } ]
               [ USING using_expression ];

Where

  • table_name and column are identifiers (table_name may be qualified with a keyspace name).
  • value can be any expression although Apache Cassandra requires that values must be literals.
  • Restrictions for if_expression and ttl_expression are covered in the Semantics section below.
  • See Expressions for more information on syntax rules.

Semantics

  • An error is raised if the specified table_name does not exist.
  • The columns list must include all primary key columns.
  • The USING TIMESTAMP clause indicates we would like to perform the INSERT as if it was done at the timestamp provided by the user. The timestamp is the number of microseconds since epoch.
  • NOTE: You should either use the USING TIMESTAMP clause in all of your statements or none of them. Using a mix of statements where some have USING TIMESTAMP and others do not will lead to very confusing results.

VALUES clause

  • 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 that evaluates to a simple value.

IF clause

  • The if_expression can only apply to non-key columns (regular columns).
  • The if_expression can contain any logical and boolean operators.

USING clause

  • ttl_expression must be an integer value (or a bind variable marker for prepared statements).
  • timestamp_expression must be an integer value (or a bind variable marker for prepared statements).

Examples

Insert a row into a table

cqlsh:example> CREATE TABLE employees(department_id INT, 
                                      employee_id INT, 
                                      name TEXT, 
                                      PRIMARY KEY(department_id, employee_id));
cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 1, 'John');
cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 2, 'Jane');
cqlsh:example> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             1 |           1 | John
             1 |           2 | Jane

Conditional insert using the IF clause

Example 1

cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 1, 'Joe') IF name = null;
 [applied]
-----------
      True

Example 2

cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 1, 'Jack') IF NOT EXISTS;
 [applied]
-----------
     False

Example 3

cqlsh:example> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe
             1 |           1 | John
             1 |           2 | Jane

Insert a row with expiration time using the USING TTL clause

You can do this as shown below.

cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 2, 'Jack') USING TTL 10;

Now query the employees table.

cqlsh:example> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe
             2 |           2 | Jack
             1 |           1 | John
             1 |           2 | Jane

Again query the employees table after 11 seconds or more.

cqlsh:example> SELECT * FROM employees; -- 11 seconds after the insert. 
 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe
             1 |           1 | John
             1 |           2 | Jane

Insert a row with USING TIMESTAMP clause.

Insert a row with a low timestamp.

cqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 3, 'Jeff') USING TIMESTAMP 1000;

Now query the employees table.

cqlsh:foo> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             1 |           1 | John
             1 |           2 | Jane
             1 |           3 | Jeff
             2 |           1 |  Joe

(4 rows)

Overwrite the row with a higher timestamp.

cqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 3, 'Jerry') USING TIMESTAMP 2000;
cqlsh:foo> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+-------
             1 |           1 |  John
             1 |           2 |  Jane
             1 |           3 | Jerry
             2 |           1 |   Joe

(4 rows)

Try to overwrite the row with a lower timestamp.

cqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 3, 'James') USING TIMESTAMP 1500;
cqlsh:foo> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+-------
             1 |           1 |  John
             1 |           2 |  Jane
             1 |           3 | Jerry
             2 |           1 |   Joe

(4 rows)

See also

CREATE TABLE DELETE SELECT UPDATE Expression Other CQL Statements

    • Synopsis
    • Syntax
      • Diagram
      • using_expression
      • ttl_or_timestamp_expression
      • Grammar
    • Semantics
      • VALUES clause
      • IF clause
      • USING clause
    • Examples
      • Insert a row into a table
      • Conditional insert using the IF clause
      • Insert a row with expiration time using the USING TTL clause
      • Insert a row with USING TIMESTAMP clause.
        • Insert a row with a low timestamp.
        • Overwrite the row with a higher timestamp.
        • Try to overwrite the row with a lower timestamp.
    • See also
APIs
USE
APIs
SELECT
Talk to Community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2019 Yugabyte, Inc. All rights reserved.