Kenhuang Academy
  • English

      • PostgreSQL INSERT INTO

      Overview

      The INSERT statement adds new rows to a PostgreSQL table. This chapter covers various ways to insert data, including single rows, multiple rows, and inserting from other tables.

      INSERT Syntax

      INSERT INTO table_name (columns) VALUES (values);
INSERT INTO table_name DEFAULT VALUES;
INSERT INTO table_name SELECT ...;

      Basic INSERT Operations

      Single Row

      -- Specify columns
INSERT INTO users (name, email, age) 
VALUES ('John Doe', 'john@example.com', 30);

-- All columns (order matters)
INSERT INTO users VALUES (1, 'John Doe', 'john@example.com', 30, NOW());

-- With DEFAULT
INSERT INTO users (name, email) VALUES ('Jane', 'jane@example.com');

      Multiple Rows

      -- Single INSERT statement
INSERT INTO products (name, price, category) VALUES 
    ('Product A', 19.99, 'Electronics'),
    ('Product B', 29.99, 'Electronics'),
    ('Product C', 39.99, 'Clothing'),
    ('Product D', 49.99, 'Books'),
    ('Product E', 59.99, 'Sports');

-- Can insert hundreds of rows efficiently

      INSERT with SELECT

      -- Copy from another table
INSERT INTO archive_users 
SELECT * FROM users WHERE deleted_at IS NOT NULL;

-- Specific columns
INSERT INTO user_summary (user_id, name, email)
SELECT id, name, email FROM users WHERE status = 'active';

-- With transformation
INSERT INTO daily_stats (stat_date, user_count)
SELECT CURRENT_DATE, COUNT(*) FROM users;

      Conflict Handling

      Basic ON CONFLICT

      -- Do nothing on conflict
INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com')
ON CONFLICT (id) DO NOTHING;

-- Update on conflict
INSERT INTO users (id, name, email) VALUES (1, 'John Updated', 'john.new@example.com')
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    updated_at = NOW();

      ON CONFLICT with WHERE

      -- Conditional update
INSERT INTO products (id, name, price) VALUES (1, 'Product', 29.99)
ON CONFLICT (id) DO UPDATE SET 
    price = EXCLUDED.price
    WHERE products.stock > 0;

      INSERT with RETURNING

      -- Return inserted values
INSERT INTO users (name, email) VALUES ('New User', 'new@example.com')
RETURNING id, name, created_at;

-- Return all columns
INSERT INTO orders (user_id, total) VALUES (1, 99.99)
RETURNING *;

-- Return specific expression
INSERT INTO products (name, price) VALUES ('Test', 9.99)
RETURNING id, price * 1.1 AS price_with_tax;

      Summary

      INSERT in PostgreSQL includes:

      • Basic INSERT, Multiple rows
      • INSERT with SELECT
      • ON CONFLICT for upsert operations
      • RETURNING clause for results