PostgreSQL 17.5, 16.9, 15.13, 14.18, and 13.21 Released!
PostgreSQL 正體中文使用手冊
PostgreSQL.TW官方使用手冊小島故事加入社團
11
11
  • 簡介
  • 前言
    • 1. 什麼是PostgreSQL?
    • 2. PostgreSQL沿革
    • 3. 慣例
    • 4. 其他參考資訊
    • 5. 問題回報指南
  • I. 新手教學
    • 1. 入門指南
      • 1.1. 安裝
      • 1.2. 基礎架構
      • 1.3. 建立一個資料庫
      • 1.4. 存取一個資料庫
    • 2. SQL查詢語言
      • 2.1. 簡介
      • 2.2. 概念
      • 2.3. 創建一個新的資料表
      • 2.4. 資料列是資料表的組成單位
      • 2.5. 資料表的查詢
      • 2.6. 交叉查詢
      • 2.7. 彙總查詢
      • 2.8. 更新資料
      • 2.9. 刪除資料
    • 3. 先進功能
      • 3.1. 簡介
      • 3.2. 檢視表(View)
      • 3.3. 外部索引鍵
      • 3.4. 交易安全
      • 3.5. 窗函數
      • 3.6. 繼承
      • 3.7. 結論
  • II. SQL查詢語言
    • 4. SQL語法
      • 4.1. 語法結構
      • 4.2. 參數表示式
      • 4.3. 函數呼叫
    • 5. 定義資料結構
      • 5.1. 認識資料表
      • 5.2. 預設值
      • 5.3. 限制條件
      • 5.4. 系統欄位
      • 5.5. 表格變更
      • 5.6. 權限
      • 5.7. 資料列安全原則
      • 5.8. Schemas
      • 5.9. 繼承
      • 5.10. 分割資料表
      • 5.11. 外部資料
      • 5.12. 其他資料庫物件
      • 5.13. 相依性追蹤
    • 6. 資料處理
      • 6.1. 新增資料
      • 6.2. 更新資料
      • 6.3. 刪除資料
      • 6.4. 修改並回傳資料
    • 7. 資料查詢
      • 7.1. 概觀
      • 7.2. 資料表表示式
      • 7.3. 取得資料列表
      • 7.4. 合併查詢結果
      • 7.5. 資料排序
      • 7.6. 指定資料範圍
      • 7.7. 列舉資料
      • 7.8. 遞迴查詢(Common Table Expressions)
    • 8. 資料型別
      • 8.1. 數字型別
      • 8.2. 貨幣型別
      • 8.3. 字串型別
      • 8.4. 位元組型別(bytea)
      • 8.5. 日期時間型別
      • 8.6. 布林型別
      • 8.7. 列舉型別
      • 8.8. 地理資訊型別
      • 8.9. 網路資訊型別
      • 8.10. 位元字串型別
      • 8.11. 全文檢索型別
      • 8.12. UUID型別
      • 8.13. XML型別
      • 8.14. JSON型別
      • 8.15. 陣列
      • 8.16. 複合型別
      • 8.17. 範圍型別
      • 8.18. 指標型別
      • 8.19. pg_lsn型別
      • 8.20. 概念型別
    • 9. 函式及運算子
      • 9.1. 邏輯運算子
      • 9.2. 比較函式及運算子
      • 9.3. 數學函式及運算子
      • 9.4. 字串函式及運算子
      • 9.5. 位元字串函式及運算子
      • 9.6. 二元字串函式及運算子
      • 9.7. 特徵比對
      • 9.8. 型別轉換函式
      • 9.9 日期時間函式及運算子
      • 9.10. 列舉型別函式
      • 9.11. 地理資訊函式及運算子
      • 9.12. 網路位址函式及運算子
      • 9.13. 文字檢索函式及運算子
      • 9.14. XML函式
      • 9.15. JSON函式及運算子
      • 9.16. 序列函式
      • 9.17. 條件表示式
      • 9.18. 陣列函式及運算子
      • 9.19. 範圍函式及運算子
      • 9.20. 彙總函數
      • 9.21. Window函式
      • 9.22. 子查詢
      • 9.23. 資料列與陣列的比較運算
      • 9.24. 集合回傳函式
      • 9.25. 系統資訊函數
      • 9.26. 系統管理函式
      • 9.27. 觸發函式
      • 9.28. 事件觸發函式
    • 10. 型別轉換
      • 10.1. 概觀
      • 10.2. 運算子
      • 10.3. 函式
      • 10.4. 資料儲存轉換規則
      • 10.5. UNION、CASE 等相關結構
      • 10.6. SELECT輸出規則
    • 11. 索引(Index)
      • 11.1. 簡介
      • 11.2. 索引型別
      • 11.3. 多欄位索引
      • 11.4. 索引與ORDER BY
      • 11.5. 善用多個索引
      • 11.6. 唯一值索引
      • 11.7. 表示式索引
      • 11.8. 部份索引(partial index)
      • 11.9. 運算子物件及家族
      • 11.10. 索引與排序規則
      • 11.11. 索引限定查詢(Index-only scan)
      • 11.12. 檢查索引運用
    • 12. 全文檢索
      • 12.1. 簡介
      • 12.2. 查詢與索引
      • 12.3. 細部控制
      • 12.4. 延伸功能
      • 12.5. 斷詞
      • 12.6. 字典
      • 12.7. 組態範例
      • 12.8. 測試與除錯
      • 12.9. GIN及GiST索引型別
      • 12.10. psql支援
      • 12.11. 功能限制
    • 13. 一致性管理(MVCC)
      • 13.1. 簡介
      • 13.2. 交易隔離
      • 13.3. 鎖定模式
      • 13.4. 在應用端檢視資料一致性
      • 13.5. 特別注意
      • 13.6. 鎖定與索引
    • 14. 效能技巧
      • 14.1. 善用EXPLAIN
      • 14.2. 統計資訊
      • 14.3. 使用確切的JOIN方式
      • 14.4. 快速建立資料庫內容
      • 14.5. 彈性設定
    • 15. 平行查詢
      • 15.1. 如何運作?
      • 15.2. 啓用時機?
      • 15.3. 平行查詢計畫
      • 15.4. 平行查詢的安全性
  • III. 系統管理
    • 16. 用原始碼安裝
      • 16.1. Short Version
      • 16.2. Requirements
      • 16.3. Getting The Source
      • 16.4. 安裝流程
      • 16.5. Post-Installation Setup
      • 16.6. Supported Platforms
      • 16.7. 平台相關的注意事項
    • 17. 用原始碼在 Windows 上安裝
      • 17.1. Building with Visual C++ or the Microsoft Windows SDK
    • 18. 服務配置與維運
      • 18.1. PostgreSQL 使用者帳號
      • 18.2. Creating a Database Cluster
      • 18.3. Starting the Database Server
      • 18.4. 核心資源管理
      • 18.5. Shutting Down the Server
      • 18.6. Upgrading a PostgreSQL Cluster
      • 18.7. Preventing Server Spoofing
      • 18.8. Encryption Options
      • 18.9. Secure TCP/IP Connections with SSL
      • 18.10. Secure TCP/IP Connections with SSH Tunnels
      • 18.11. 在 Windows 註冊事件日誌
    • 19. 服務組態設定
      • 19.1. Setting Parameters
      • 19.2. File Locations
      • 19.3. 連線與認證
      • 19.4. 資源配置
      • 19.5. Write Ahead Log
      • 19.6. 複寫(Replication)
      • 19.7. 查詢規畫
      • 19.8. 錯誤回報與日誌記錄
      • 19.9. Run-time Statistics
      • 19.10. 自動資料庫清理
      • 19.11. 用戶端連線預設參數
      • 19.12. 交易鎖定管理
      • 19.13. 版本與平台的相容性
      • 19.14. Error Handling
      • 19.15. 預先配置的參數
      • 19.16. Customized Options
      • 19.17. Developer Options
      • 19.18. Short Options
    • 20. 使用者認證
      • 20.1. 設定檔:pg_hba.conf
      • 20.2. User Name Maps
      • 20.3. Authentication Methods
      • 20.4. Authentication Problems
    • 21. 資料庫角色
      • 21.1. Database Roles
      • 21.2. Role Attributes
      • 21.3. Role Membership
      • 21.4. 移除角色
      • 21.5. Default Roles
      • 21.6. Function Security
    • 22. Managing Databases
      • 22.1. Overview
      • 22.2. Creating a Database
      • 22.3. 樣版資料庫
      • 22.4. Database Configuration
      • 22.5. Destroying a Database
      • 22.6. Tablespaces
    • 23. 語系
      • 23.1. 語系支援
      • 23.2. Collation Support
      • 23.3. 字元集支援
    • 24. 例行性資料庫維護工作
      • 24.1. 例行性資料清理
      • 24.2. 定期重建索引
      • 24.3. Log File Maintenance
    • 25. 備份及還原
      • 25.1. SQL Dump
      • 25.2. File System Level Backup
      • 25.3. Continuous Archiving and Point-in-Time Recovery (PITR)
    • 26. High Availability, Load Balancing, and Replication
      • 26.1. Comparison of Different Solutions
      • 26.2. 日誌轉送備用伺服器 Log-Shipping Standby Servers
      • 26.3. Failover
      • 26.4. Alternative Method for Log Shipping
      • 26.5. Hot Standby
    • 27. Recovery Configuration
      • 27.1. Archive Recovery Settings
      • 27.2. Recovery Target Settings
      • 27.3. Standby Server Settings
    • 28. 監控資料庫活動
      • 28.1. Standard Unix Tools
      • 28.2. 統計資訊收集器
      • 28.3. Viewing Locks
      • 28.4. Progress Reporting
      • 28.5. Dynamic Tracing
    • 29. Monitoring Disk Usage
      • 29.1. Determining Disk Usage
      • 29.2. Disk Full Failure
    • 30. 高可靠度及預寫日誌
      • 30.1. Reliability
      • 30.2. Write-Ahead Logging (WAL)
      • 30.3. Asynchronous Commit
      • 30.4. WAL Configuration
      • 30.5. WAL Internals
    • 31. 邏輯複寫(Logical Replication)
      • 31.1. 發佈(Publication)
      • 31.2. 訂閱(Subscription)
      • 31.3. 衝突處理
      • 31.4. 限制
      • 31.5. 架構
      • 31.6. 監控
      • 31.7. 安全性
      • 31.8. 系統設定
      • 31.9. 快速設定
    • 32. Just-in-Time Compilation (JIT)
      • 32.1. What is JIT compilation?
      • 32.2. When to JIT?
      • 32.3. Configuration
      • 32.4. Extensibility
    • 33. 迴歸測試
      • 33.1. Running the Tests
      • 33.2. Test Evaluation
      • 33.3. Variant Comparison Files
      • 33.4. TAP Tests
      • 33.5. Test Coverage Examination
  • IV. 用戶端介面
    • 34. libpq - C Library
      • 34.1. 資料庫連線控制函數
      • 34.2. 連線狀態函數
      • 34.3. Command Execution Functions
      • 34.4. Asynchronous Command Processing
      • 34.5. Retrieving Query Results Row-By-Row
      • 34.6. Canceling Queries in Progress
      • 34.7. The Fast-Path Interface
      • 34.8. Asynchronous Notification
      • 34.9. Functions Associated with the COPY Command
      • 34.10. Control Functions
      • 34.11. Miscellaneous Functions
      • 34.12. Notice Processing
      • 34.13. Event System
      • 34.14. 環境變數
      • 34.15. 密碼檔
      • 34.16. The Connection Service File
      • 34.17. LDAP Lookup of Connection Parameters
      • 34.18. SSL Support
      • 34.19. Behavior in Threaded Programs
      • 34.20. Building libpq Programs
      • 34.21. Example Programs
    • 35. Large Objects
      • 35.1. Introduction
      • 35.2. Implementation Features
      • 35.3. Client Interfaces
      • 35.4. Server-side Functions
      • 35.5. Example Program
    • 36. ECPG - Embedded SQL in C
      • 36.1. The Concept
      • 36.2. Managing Database Connections
      • 36.3. Running SQL Commands
      • 36.4. Using Host Variables
      • 36.5. Dynamic SQL
      • 36.6. pgtypes Library
      • 36.7. Using Descriptor Areas
      • 36.8. Error Handling
      • 36.9. Preprocessor Directives
      • 36.10. Processing Embedded SQL Programs
      • 36.11. Library Functions
      • 36.12. Large Objects
      • 36.13. C++ Applications
      • 36.14. Embedded SQL Commands
      • 36.15. Informix Compatibility Mode
      • 36.16. Internals
    • 37. The Information Schema
      • 37.1. The Schema
      • 37.2. Data Types
      • 37.3. information_schema_catalog_name
      • 37.4. administrable_role_authorizations
      • 37.5. applicable_roles
      • 37.6. attributes
      • 37.7. character_sets
      • 37.8. check_constraint_routine_usage
      • 37.9. check_constraints
      • 37.10. collations
      • 37.11. collation_character_set_applicability
      • 37.12. column_domain_usage
      • 37.13. column_options
      • 37.14. column_privileges
      • 37.15. column_udt_usage
      • 37.16. columns
      • 37.17. constraint_column_usage
      • 37.18. constraint_table_usage
      • 37.19. data_type_privileges
      • 37.20. domain_constraints
      • 37.21. domain_udt_usage
      • 37.22. domains
      • 37.23. element_types
      • 37.24. enabled_roles
      • 37.25. foreign_data_wrapper_options
      • 37.26. foreign_data_wrappers
      • 37.27. foreign_server_options
      • 37.28. foreign_servers
      • 37.29. foreign_table_options
      • 37.30. foreign_tables
      • 37.31. key_column_usage
      • 37.32. parameters
      • 37.33. referential_constraints
      • 37.34. role_column_grants
      • 37.35. role_routine_grants
      • 37.36. role_table_grants
      • 37.37. role_udt_grants
      • 37.38. role_usage_grants
      • 37.39. routine_privileges
      • 37.40. routines
      • 37.41. schemata
      • 37.42. sequences
      • 37.43. sql_features
      • 37.44. sql_implementation_info
      • 37.45. sql_languages
      • 37.46. sql_packages
      • 37.47. sql_parts
      • 37.48. sql_sizing
      • 37.49. sql_sizing_profiles
      • 37.50. table_constraints
      • 37.51. table_privileges
      • 37.52. tables
      • 37.53. transforms
      • 37.54. triggered_update_columns
      • 37.55. triggers
      • 37.56. udt_privileges
      • 37.57. usage_privileges
      • 37.58. user_defined_types
      • 37.59. user_mapping_options
      • 37.60. user_mappings
      • 37.61. view_column_usage
      • 37.62. view_routine_usage
      • 37.63. view_table_usage
      • 37.64. views
  • V. 資料庫程式設計
    • 38. SQL 延伸功能
      • 38.1. How Extensibility Works
      • 38.2. The PostgreSQL Type System
      • 38.3. 使用者自訂函數
      • 38.4. User-defined Procedures
      • 38.5. Query Language (SQL) Functions
      • 38.6. Function Overloading
      • 38.7. 函數易變性類別
      • 38.8. Procedural Language Functions
      • 38.9. Internal Functions
      • 38.10. C-Language Functions
      • 38.11. User-defined Aggregates
      • 38.12. User-defined Types
      • 38.13. User-defined Operators
      • 38.14. Operator Optimization Information
      • 38.15. Interfacing Extensions To Indexes
      • 38.16. Packaging Related Objects into an Extension
      • 38.17. Extension Building Infrastructure
    • 39. Triggers
    • 40. Event Triggers
    • 41. 規則系統
      • 41.1. The Query Tree
      • 41.2. Views and the Rule System
      • 41.3. Materialized Views
      • 41.4. Rules on INSERT, UPDATE, and DELETE
      • 41.5. 規則及權限
      • 41.6. Rules and Command Status
      • 41.7. Rules Versus Triggers
    • 42. Procedural Languages(程序語言)
      • 42.1. Installing Procedural Languages
    • 43. PL/pgSQL - SQL Procedural Language
      • 43.5. 基本語法
    • 44. PL/Tcl - Tcl Procedural Language
    • 45. PL/Perl - Perl Procedural Language
    • 46. PL/Python - Python Procedural Language
    • 47. Server Programming Interface
    • 48. Background Worker Processes
    • 49. Logical Decoding
    • 50. Replication Progress Tracking
  • VI. 參考資訊
    • I. SQL 指令
      • ALTER DATABASE
      • ALTER DEFAULT PRIVILEGES
      • ALTER EXTENSION
      • ALTER FUNCTION
      • ALTER INDEX
      • ALTER LANGUAGE
      • ALTER MATERIALIZED VIEW
      • ALTER POLICY
      • ALTER PUBLICATION
      • ALTER ROLE
      • ALTER RULE
      • ALTER SCHEMA
      • ALTER SEQUENCE
      • ALTER STATISTICS
      • ALTER SUBSCRIPTION
      • ALTER TABLE
      • ALTER TABLESPACE
      • ALTER TRIGGER
      • ALTER TYPE
      • ALTER VIEW
      • ANALYZE
      • CLUSTER
      • COMMENT
      • COPY
      • CREATE CAST
      • CREATE DATABASE
      • CREATE EXTENSION
      • CREATE FOREIGN TABLE
      • CREATE FOREIGN DATA WRAPPER
      • CREATE FUNCTION
      • CREATE INDEX
      • CREATE LANGUAGE
      • CREATE MATERIALIZED VIEW
      • CREATE DOMAIN
      • CREATE POLICY
      • CREATE PROCEDURE
      • CREATE PUBLICATION
      • CREATE ROLE
      • CREATE RULE
      • CREATE SCHEMA
      • CREATE SEQUENCE
      • CREATE SERVER
      • CREATE STATISTICS
      • CREATE SUBSCRIPTION
      • CREATE TABLE
      • CREATE TABLE AS
      • CREATE TABLESPACE
      • CREATE TRANSFORM
      • CREATE TRIGGER
      • CREATE TYPE
      • CREATE USER
      • CREATE USER MAPPING
      • CREATE VIEW
      • DELETE
      • DO
      • DROP DATABASE
      • DROP EXTENSION
      • DROP FUNCTION
      • DROP INDEX
      • DROP LANGUAGE
      • DROP MATERIALIZED VIEW
      • DROP OWNED
      • DROP POLICY
      • DROP ROLE
      • DROP RULE
      • DROP SCHEMA
      • DROP SEQUENCE
      • DROP STATISTICS
      • DROP SUBSCRIPTION
      • DROP TABLE
      • DROP TABLESPACE
      • DROP TRANSFORM
      • DROP TRIGGER
      • DROP TYPE
      • DROP USER
      • DROP VIEW
      • EXECUTE
      • EXPLAIN
      • GRANT
      • IMPORT FOREIGN SCHEMA
      • INSERT
      • LISTEN
      • LOAD
      • NOTIFY
      • PREPARE TRANSACTION
      • REASSIGN OWNED
      • REFRESH MATERIALIZED VIEW
      • REINDEX
      • RESET
      • REVOKE
      • SELECT
      • SELECT INTO
      • SET
      • SET CONSTRAINTS
      • SET ROLE
      • SET SESSION AUTHORIZATION
      • SET TRANSACTION
      • SHOW
      • TRUNCATE
      • UNLISTEN
      • UPDATE
      • VACUUM
      • VALUES
    • II. PostgreSQL 用戶端工具
      • createdb
      • createuser
      • dropdb
      • dropuser
      • pgbench
      • pg_dump
      • psql
      • vacuumdb
    • III. PostgreSQL 伺服器應用程式
      • pg_test_timing
      • postgres
  • VII. 資料庫進階
    • 52. 系統目錄
      • 52.3. pg_am
      • 52.7. pg_attribute
      • 52.8. pg_authid
      • 52.9. pg_auth_members
      • 52.11 pg_class
      • 52.12. pg_collation
      • 52.13. pg_constraint
      • 52.15 pg_database
      • 52.26 pg_index
      • 52.29. pg_language
      • 52.32. pg_namespace
      • 52.33. pg_opclass
      • 52.38. pg_policy
      • 52.39. pg_proc
      • 52.44. pg_rewrite
      • 52.50. pg_statistic
      • 52.51. pg_statistic_ext
      • 52.54. pg_tablespace
      • 52.56. pg_trigger
      • 52.62. pg_type
      • 52.79. pg_replication_origin_status
      • 52.81 pg_roles
      • 52.85. pg_settings
      • 52.87. pg_stats
    • 53. Frontend/Backend Protocol
      • 53.1. Overview
      • 53.2. Message Flow
      • 53.3. SASL Authentication
      • 53.4. Streaming Replication Protocol
      • 53.5. Logical Streaming Replication Protocol
      • 53.6. Message Data Types
      • 53.7. Message Formats
      • 53.8. Error and Notice Message Fields
      • 53.9. Logical Replication Message Formats
      • 53.10. Summary of Changes since Protocol 2.0
    • 54. PostgreSQL 程式撰寫慣例
      • 54.1. Formatting
      • 54.2. Reporting Errors Within the Server
      • 54.3. Error Message Style Guide
      • 54.4. Miscellaneous Coding Conventions
    • 56. Writing A Procedural Language Handler
    • 64. GiST Indexes
      • 64.1. Introduction
      • 64.2. Built-in Operator Classes
      • 64.3. Extensibility
      • 64.4. Implementation
      • 64.5. Examples
    • 65. SP-GiST Indexes
      • 65.1. Introduction
      • 65.2. Built-in Operator Classes
      • 65.3. Extensibility
      • 65.4. Implementation
      • 65.5. Examples
    • 66. GIN 索引
      • 66.1. 簡介
      • 66.2. 內建運算子類
      • 66.3. Extensibility
      • 66.4. Implementation
      • 66.5. GIN Tips and Tricks
      • 66.6. Limitations
      • 66.7. Examples
    • 67. BRIN Indexes
      • 67.1. Introduction
      • 67.2. Built-in Operator Classes
      • 67.3. Extensibility
    • 68. 資料庫實體儲存格式
      • 68.2. TOAST
      • 68.4 可視性映射表(Visibility Map)
    • 70. How the Planner Uses Statistics
      • 70.2. Multivariate Statistics Examples
  • VIII. 附錄
    • A. PostgreSQL錯誤代碼
    • B. 日期時間格式支援
      • B.1. 日期時間解譯流程
      • B.2. 日期時間慣用字
      • B.3. 日期時間設定檔
      • B.4. 日期時間的沿革
    • C. SQL 關鍵字
    • D. SQL 相容性
    • E. 版本資訊
    • F. 延伸支援模組
      • F.4. auto_explain
      • F.11. dblink
        • dblink
      • F.33. pg_visibility
    • G. Additional Supplied Programs
      • G.1. Client Applications
        • oid2name
        • vacuumlo
      • G.2. Server Applications
        • pg_standby
    • H. 外部專案
      • H.1. 用戶端介面
      • H.2. Administration Tools
      • H.3. Procedural Languages
      • H.4. Extensions
    • I. The Source Code Repository
      • I.1. Getting The Source via Git
    • J. 文件取得
    • K. 縮寫字
  • 參考書目
Powered by GitBook

現在才知道 PostgreSQL Taiwan 了吧!

On this page
  • Synopsis
  • Description
  • Parameters
  • Inserting
  • ON CONFLICTClause
  • Tip
  • Outputs
  • Notes
  • Examples
  • Compatibility

Was this helpful?

Edit on Git
Export as PDF
  1. VI. 參考資訊
  2. I. SQL 指令

INSERT

INSERT — create new rows in a table

Synopsis

[ WITH [ RECURSIVE ] 
with_query
 [, ...] ]
INSERT INTO 
table_name
 [ AS 
alias
 ] [ ( 
column_name
 [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER} VALUE ]
    { DEFAULT VALUES | VALUES ( { 
expression
 | DEFAULT } [, ...] ) [, ...] | 
query
 }
    [ ON CONFLICT [ 
conflict_target
 ] 
conflict_action
 ]
    [ RETURNING * | 
output_expression
 [ [ AS ] 
output_name
 ] [, ...] ]


where 
conflict_target
 can be one of:


    ( { 
index_column_name
 | ( 
index_expression
 ) } [ COLLATE 
collation
 ] [ 
opclass
 ] [, ...] ) [ WHERE 
index_predicate
 ]
    ON CONSTRAINT 
constraint_name
and 
conflict_action
 is one of:


    DO NOTHING
    DO UPDATE SET { 
column_name
 = { 
expression
 | DEFAULT } |
                    ( 
column_name
 [, ...] ) = [ ROW ] ( { 
expression
 | DEFAULT } [, ...] ) |
                    ( 
column_name
 [, ...] ) = ( 
sub-SELECT
 )
                  } [, ...]
              [ WHERE 
condition
 ]

Description

INSERTinserts new rows into a table. One can insert one or more rows specified by value expressions, or zero or more rows resulting from a query.

The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order; or the firstN_column names, if there are onlyNcolumns supplied by theVALUESclause orquery. The values supplied by theVALUESclause orquery_are associated with the explicit or implicit column list left-to-right.

Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.

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

The optionalRETURNINGclause causesINSERTto compute and return value(s) based on each row actually inserted (or updated, if anON CONFLICT DO UPDATEclause was used). This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. The syntax of theRETURNINGlist is identical to that of the output list ofSELECT. Only rows that were successfully inserted or updated will be returned. For example, if a row was locked but not updated because anON CONFLICT DO UPDATE ... WHEREclause_condition_was not satisfied, the row will not be returned.

You must haveINSERTprivilege on a table in order to insert into it. IfON CONFLICT DO UPDATEis present,UPDATEprivilege on the table is also required.

If a column list is specified, you only needINSERTprivilege on the listed columns. Similarly, whenON CONFLICT DO UPDATEis specified, you only needUPDATEprivilege on the column(s) that are listed to be updated. However,ON CONFLICT DO UPDATEalso requiresSELECTprivilege on any column whose values are read in theON CONFLICT DO UPDATEexpressions orcondition.

Use of theRETURNINGclause requiresSELECTprivilege on all columns mentioned inRETURNING. If you use the_query_clause to insert rows from a query, you of course need to haveSELECTprivilege on any table or column used in the query.

Parameters

Inserting

This section covers parameters that may be used when only inserting new rows. Parameters_exclusively_used with theON CONFLICTclause are described separately.

with_query

It is possible for thequery(SELECTstatement) to also contain aWITHclause. In such a case both sets ofwith_query_can be referenced within thequery_, but the second one takes precedence since it is more closely nested.

table_name

The name (optionally schema-qualified) of an existing table.

alias

A substitute name fortable_name. When an alias is provided, it completely hides the actual name of the table. This is particularly useful whenON CONFLICT DO UPDATEtargets a table namedexcluded, since that will otherwise be taken as the name of the special table representing rows proposed for insertion.

column_name

The name of a column in the table named bytable_name. The column name can be qualified with a subfield name or array subscript, if needed. (Inserting into only some fields of a composite column leaves the other fields null.) When referencing a column withON CONFLICT DO UPDATE, do not include the table's name in the specification of a target column. For example,INSERT INTO table_name ... ON CONFLICT DO UPDATE SET table_name.col = 1is invalid (this follows the general behavior forUPDATE).

OVERRIDING SYSTEM VALUE

Without this clause, it is an error to specify an explicit value (other thanDEFAULT) for an identity column defined asGENERATED ALWAYS. This clause overrides that restriction.

OVERRIDING USER VALUE

If this clause is specified, then any values supplied for identity columns defined asGENERATED BY DEFAULTare ignored and the default sequence-generated values are applied.

This clause is useful for example when copying values between tables. WritingINSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1will copy fromtbl1all columns that are not identity columns intbl2while values for the identity columns intbl2will be generated by the sequences associated withtbl2.

DEFAULT VALUES

All columns will be filled with their default values. (AnOVERRIDINGclause is not permitted in this form.)

expression

An expression or value to assign to the corresponding column.

DEFAULT

The corresponding column will be filled with its default value.

query

output_expression

An expression to be computed and returned by theINSERTcommand after each row is inserted or updated. The expression can use any column names of the table named bytable_name. Write*to return all columns of the inserted or updated row(s).

output_name

A name to use for a returned column.

ON CONFLICTClause

The optionalON CONFLICTclause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if anarbiter_constraint or index specified byconflict_targetis violated, the alternativeconflict_action_is taken.ON CONFLICT DO NOTHINGsimply avoids inserting a row as its alternative action.ON CONFLICT DO UPDATEupdates the existing row that conflicts with the row proposed for insertion as its alternative action.

conflict_target_can perform_unique index inference. When performing inference, it consists of one or moreindex_column_name_columns and/orindex_expressionexpressions, and an optionalindex_predicate. Alltable_nameunique indexes that, without regard to order, contain exactly theconflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes. If anindex_predicate_is specified, it must, as a further requirement for inference, satisfy arbiter indexes. Note that this means a non-partial unique index (a unique index without a predicate) will be inferred (and thus used byON CONFLICT) if such an index satisfying every other criteria is available. If an attempt at inference is unsuccessful, an error is raised.

ON CONFLICT DO UPDATEguarantees an atomicINSERTorUPDATEoutcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known asUPSERT—“UPDATE or INSERT”.

conflict_target

Specifies which conflictsON CONFLICTtakes the alternative action on by choosingarbiter indexes. Either performsunique index inference, or names a constraint explicitly. ForON CONFLICT DO NOTHING, it is optional to specify aconflict_target; when omitted, conflicts with all usable constraints (and unique indexes) are handled. ForON CONFLICT DO UPDATE, a_conflict_targetmust_be provided.

conflict_action

_conflict_action_specifies an alternativeON CONFLICTaction. It can be eitherDO NOTHING, or aDO UPDATEclause specifying the exact details of theUPDATEaction to be performed in case of a conflict. TheSETandWHEREclauses inON CONFLICT DO UPDATEhave access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the specialexcludedtable.SELECTprivilege is required on any column in the target table where correspondingexcludedcolumns are read.

Note that the effects of all per-rowBEFORE INSERTtriggers are reflected inexcludedvalues, since those effects may have contributed to the row being excluded from insertion.

index_column_name

The name of atable_name_column. Used to infer arbiter indexes. FollowsCREATE INDEXformat.SELECTprivilege onindex_column_name_is required.

index_expression

Similar toindex_column_name, but used to infer expressions ontable_name_columns appearing within index definitions (not simple columns). FollowsCREATE INDEXformat.SELECTprivilege on any column appearing withinindex_expression_is required.

collation

When specified, mandates that correspondingindex_column_name_orindex_expression_use a particular collation in order to be matched during inference. Typically this is omitted, as collations usually do not affect whether or not a constraint violation occurs. FollowsCREATE INDEXformat.

opclass

When specified, mandates that correspondingindex_column_name_orindex_expression_use particular operator class in order to be matched during inference. Typically this is omitted, as the_equality_semantics are often equivalent across a type's operator classes anyway, or because it's sufficient to trust that the defined unique indexes have the pertinent definition of equality. FollowsCREATE INDEXformat.

index_predicate

Used to allow inference of partial unique indexes. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred. FollowsCREATE INDEXformat.SELECTprivilege on any column appearing within_index_predicate_is required.

constraint_name

Explicitly specifies an arbiter_constraint_by name, rather than inferring a constraint or index.

condition

An expression that returns a value of typeboolean. Only rows for which this expression returnstruewill be updated, although all rows will be locked when theON CONFLICT DO UPDATEaction is taken. Note that_condition_is evaluated last, after a conflict has been identified as a candidate to update.

Note that exclusion constraints are not supported as arbiters withON CONFLICT DO UPDATE. In all cases, onlyNOT DEFERRABLEconstraints and unique indexes are supported as arbiters.

INSERTwith anON CONFLICT DO UPDATEclause is a“deterministic”statement. This means that the command will not be allowed to affect any single existing row more than once; a cardinality violation error will be raised when this situation arises. Rows proposed for insertion should not duplicate each other in terms of attributes constrained by an arbiter index or constraint.

Tip

It is often preferable to use unique index inference rather than naming a constraint directly usingON CONFLICT ON CONSTRAINTconstraint_name. Inference will continue to work correctly when the underlying index is replaced by another more or less equivalent index in an overlapping way, for example when usingCREATE UNIQUE INDEX ... CONCURRENTLYbefore dropping the index being replaced.

Outputs

On successful completion, anINSERTcommand returns a command tag of the form

INSERT 
oid
count

Thecount_is the number of rows inserted or updated. Ifcountis exactly one, and the target table has OIDs, thenoidis theOIDassigned to the inserted row. The single row must have been inserted rather than updated. Otherwiseoid_is zero.

If theINSERTcommand contains aRETURNINGclause, the result will be similar to that of aSELECTstatement containing the columns and values defined in theRETURNINGlist, computed over the row(s) inserted or updated by the command.

Notes

If the specified table is a partitioned table, each row is routed to the appropriate partition and inserted into it. If the specified table is a partition, an error will occur if one of the input rows violates the partition constraint.

Examples

Insert a single row into tablefilms:

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

In this example, thelencolumn is omitted and therefore it will have the default value:

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

This example uses theDEFAULTclause for the date columns rather than specifying a value:

INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');

To insert a row consisting entirely of default values:

INSERT INTO films DEFAULT VALUES;

To insert multiple rows using the multirowVALUESsyntax:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

This example inserts some rows into tablefilmsfrom a tabletmp_filmswith the same column layout asfilms:

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod 
<
 '2004-05-07';

This example inserts into array columns:

-- Create an empty 3x3 gameboard for noughts-and-crosses
INSERT INTO tictactoe (game, board[1:3][1:3])
    VALUES (1, '&#123;{" "," "," "},{" "," "," "},{" "," "," "}&#125;');
-- The subscripts in the above example aren't really needed
INSERT INTO tictactoe (game, board)
    VALUES (2, '&#123;{X," "," "},{" ",O," "},{" ",X," "}&#125;');

Insert a single row into tabledistributors, returning the sequence number generated by theDEFAULTclause:

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
   RETURNING did;

Increment the sales count of the salesperson who manages the account for Acme Corporation, and record the whole updated row along with current time in a log table:

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

Insert or update new distributors as appropriate. Assumes a unique index has been defined that constrains values appearing in thedidcolumn. Note that the specialexcludedtable is used to reference values originally proposed for insertion:

INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

Insert a distributor, or do nothing for rows proposed for insertion when an existing, excluded row (a row with a matching constrained column or columns after before row insert triggers fire) exists. Example assumes a unique index has been defined that constrains values appearing in thedidcolumn:

INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')
    ON CONFLICT (did) DO NOTHING;

Insert or update new distributors as appropriate. Example assumes a unique index has been defined that constrains values appearing in thedidcolumn.WHEREclause is used to limit the rows actually updated (any existing row not updated will still be locked, though):

-- Don't update existing distributors based in a certain ZIP code
INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
    ON CONFLICT (did) DO UPDATE
    SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
    WHERE d.zipcode 
<
>
 '21201';

-- Name a constraint directly in the statement (uses associated
-- index to arbitrate taking the DO NOTHING action)
INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

Insert new distributor if possible; otherwiseDO NOTHING. Example assumes a unique index has been defined that constrains values appearing in thedidcolumn on a subset of rows where theis_activeBoolean column evaluates totrue:

-- This statement could infer a partial unique index on "did"
-- with a predicate of "WHERE is_active", but it could also
-- just use a regular unique constraint on "did"
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    ON CONFLICT (did) WHERE is_active DO NOTHING;

Compatibility

INSERTconforms to the SQL standard, except that theRETURNINGclause is aPostgreSQLextension, as is the ability to useWITHwithINSERT, and the ability to specify an alternative action withON CONFLICT. Also, the case in which a column name list is omitted, but not all the columns are filled from theVALUESclause orquery, is disallowed by the standard.

The SQL standard specifies thatOVERRIDING SYSTEM VALUEcan only be specified if an identity column that is generated always exists. PostgreSQL allows the clause in any case and ignores it if it is not applicable.

PreviousIMPORT FOREIGN SCHEMANextLISTEN

Last updated 7 years ago

Was this helpful?

ON CONFLICTcan be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (Seebelow.)

TheWITHclause allows you to specify one or more subqueries that can be referenced by name in theINSERTquery. Seeandfor details.

A query (SELECTstatement) that supplies the rows to be inserted. Refer to thestatement for a description of the syntax.

Possible limitations of the_query_clause are documented under.

ON CONFLICTClause
Section 7.8
SELECT
SELECT
SELECT