INSERT: Adding New Data

PostgreSQL INSERT Statement: Adding Data to Your Tables

Welcome to our PostgreSQL tutorial series! In this lesson, we'll focus on the INSERT statement in PostgreSQL, which is essential for adding new data to your database tables. Understanding how to use the INSERT statement effectively allows you to populate your tables with meaningful data.

Introduction to the INSERT Statement

The INSERT statement in PostgreSQL is used to add new rows to a table. You can insert data into all columns or specific columns, insert multiple rows at once, and even insert data from other tables.

Basic Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
  • INSERT INTO: Specifies the table where the data will be inserted.
  • VALUES: Lists the values to be inserted into the columns.

Inserting Data into PostgreSQL Tables

Let's explore various ways to use the INSERT statement with practical examples using our familiar library and movies database.

1. Inserting a Single Row

Example: Add a new book to the books table

INSERT INTO books (title, author, price, pages)
VALUES ('Brave New World', 'Aldous Huxley', 9.99, 268);

View Schema Initialization

Explanation:

  • A new book titled 'Brave New World' is added to the books table with the specified author, price, and page count.

2. Inserting Multiple Rows

You can insert multiple rows in a single statement by separating the value sets with commas.

Example: Add multiple authors to the authors table

INSERT INTO authors (name)
VALUES ('Aldous Huxley'),
       ('Ray Bradbury'),
       ('Kurt Vonnegut');

Explanation:

  • Three new authors are added to the authors table.

3. Inserting Data into Specific Columns

If you don't have values for all columns, you can insert data into specific columns. Unspecified columns will receive their default values or NULL if no default is set.

Example: Insert a movie without specifying additional details

Since our movies table only has a title column, we can only insert into that column.

INSERT INTO movies (title)
VALUES ('Blade Runner');

Explanation:

  • A new movie titled 'Blade Runner' is added to the movies table.

4. Using Default Values

You can use the DEFAULT keyword to insert a column's default value explicitly. If your table has default values defined.

Example: Insert a book with default price

First, modify the books table to have a default price (if it doesn't already):

-- Modify the books table to have a default price
ALTER TABLE books ALTER COLUMN price SET DEFAULT 9.99;
 
-- Insert a book using the default price
INSERT INTO books (title, author, pages)
VALUES ('Fahrenheit 451', 'Ray Bradbury', 194);

Explanation:

  • The book 'Fahrenheit 451' is added with the default price of $9.99.

5. Inserting Data from Another Table

You can insert data into a table by selecting from another table using the INSERT INTO ... SELECT statement.

Example: Copy authors into a new table

-- Create a new table for classic authors
CREATE TABLE classic_authors (name VARCHAR(255));
 
-- Insert authors from the authors table into classic_authors
INSERT INTO classic_authors (name)
SELECT name
FROM authors
WHERE name IN ('George Orwell', 'Jane Austen', 'Leo Tolstoy');

Explanation:

  • A new table classic_authors is created.
  • Authors matching the specified names are copied into classic_authors.

6. Returning Inserted Data

The RETURNING clause allows you to return data from the inserted rows.

Example: Insert a book and return its title and price

INSERT INTO books (title, author, price, pages)
VALUES ('Slaughterhouse-Five', 'Kurt Vonnegut', 8.99, 275)
RETURNING title, price;

Explanation:

  • The book is inserted, and the title and price of the inserted row are returned.

7. Handling Conflicts with ON CONFLICT (Upsert)

The ON CONFLICT clause allows you to specify an alternative action if an insert would violate a uniqueness constraint.

First, we need to set a unique constraint on the relevant column.

Example: Insert an author or do nothing if they already exist

First, ensure there's a uniqueness constraint on the name column:

ALTER TABLE authors ADD CONSTRAINT unique_name UNIQUE (name);

Now, use ON CONFLICT:

INSERT INTO authors (name)
VALUES ('George Orwell')
ON CONFLICT (name) DO NOTHING;

Explanation:

  • Attempts to insert 'George Orwell' into authors.
  • If 'George Orwell' already exists, the insert is skipped.

8. Using INSERT with Subqueries

You can use subqueries to generate values for insertion.

Example: Insert authors from books not already in authors table

Suppose we have some books whose authors are not yet in the authors table.

First, let's find authors in books who are not in authors, and insert them into authors.

INSERT INTO authors (name)
SELECT DISTINCT author
FROM books
WHERE author NOT IN (SELECT name FROM authors);

Explanation:

  • Authors from the books table who are not already in authors are inserted into authors.
  • In our schema, all authors are already present, so no new authors will be added unless new books with new authors are added to the books table.

9. Inserting JSON Data

Our schema does not include any tables with JSON columns, but we can create one.

Example: Insert a JSON object into a table

First, create a table with a JSON column:

CREATE TABLE book_info (
    id SERIAL PRIMARY KEY,
    info JSONB
);

Insert JSON data:

INSERT INTO book_info (info)
VALUES ('{"title": "Dune", "author": "Frank Herbert", "price": 10.99, "pages": 412}');

Explanation:

  • A JSON object containing book details is inserted into the book_info table.

10. Copying Data Between Tables

You can copy data from one table to another, including transformations.

Example: Copy books to a discounted_books table with a 10% discount

Create the discounted_books table:

CREATE TABLE discounted_books (
    title VARCHAR(255),
    author VARCHAR(255),
    discounted_price NUMERIC(10, 2)
);

Insert data with transformed values:

INSERT INTO discounted_books (title, author, discounted_price)
SELECT title, author, price * 0.9
FROM books;

Explanation:

  • All books are copied into discounted_books with a 10% discount applied to the price.

Conclusion

The INSERT statement in PostgreSQL is a versatile tool for adding data to your tables. Whether you're inserting a single row, multiple rows, or data from other tables, mastering the INSERT statement is crucial for effective database management.

Key Takeaways:

  • Use INSERT INTO ... VALUES to add new data.
  • Insert multiple rows by providing multiple sets of values.
  • Specify columns to insert data into specific columns.
  • Utilize RETURNING to get information about inserted rows.
  • Handle conflicts with ON CONFLICT to prevent errors.
  • Insert data from other tables using INSERT INTO ... SELECT.
  • Leverage PostgreSQL's support for JSON data types.

Schema Initialization

To run the examples provided, ensure your PostgreSQL database is set up with the following schema:

-- Create the books table
CREATE TABLE books (
    title VARCHAR(255),
    author VARCHAR(255),
    price NUMERIC(10, 2),
    pages INTEGER
);
 
-- Insert sample data into the books table
INSERT INTO books (title, author, price, pages) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 180),
('To Kill a Mockingbird', 'Harper Lee', 7.99, 281),
('1984', 'George Orwell', 8.99, 328),
('Pride and Prejudice', 'Jane Austen', 6.99, 279),
('The Catcher in the Rye', 'J.D. Salinger', 9.99, 214),
('Moby-Dick', 'Herman Melville', 11.99, 635),
('War and Peace', 'Leo Tolstoy', 12.99, 1225);
 
-- Create the authors table
CREATE TABLE authors (
    name VARCHAR(255)
);
 
-- Insert sample data into the authors table
INSERT INTO authors (name) VALUES
('F. Scott Fitzgerald'),
('Harper Lee'),
('George Orwell'),
('Jane Austen'),
('J.D. Salinger'),
('Herman Melville'),
('Leo Tolstoy');
 
-- Create the movies table
CREATE TABLE movies (
    title VARCHAR(255)
);
 
-- Insert sample data into the movies table
INSERT INTO movies (title) VALUES
('The Shawshank Redemption'),
('The Godfather'),
('The Dark Knight'),
('Pulp Fiction'),
('The Lord of the Rings');

Feel free to practice inserting data using this schema to enhance your understanding of PostgreSQL's INSERT statement. Happy coding!

GROUP BY: Aggregating DataUPDATE: Modifying Data