PostgreSQL INSERT

PostgreSQL INSERT

How to use PostgreSQL INSERT Command with Examples

Hello! 🎉 Today, we’re diving into another foundational PostgreSQL command—INSERT. By the end of this tutorial, you’ll know how to insert data into tables, understand the common flags, and troubleshoot some frequent errors. Let’s jump right in!

What is INSERT?

The INSERT INTO command in PostgreSQL is used to add new rows to a table. This is how you populate your tables with actual data after creating them using CREATE TABLE.

Basic Syntax

The basic syntax for inserting a single row into a table is:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

Example: Inserting Data into the tv_series Table

Remember the tv_series table we created in the last tutorial? It had columns for id, name, genre, seasons, and is_running. Let’s go ahead and populate it with some data about TV series.

Inserting a Single Row

You can insert a single row like this:

INSERT INTO tv_series (name, genre, seasons, is_running) VALUES ('Breaking Bad', 'Crime', 5, false);

Inserting Multiple Rows

What if you want to insert several rows at once? You can do that too! Just add multiple sets of values, separated by commas.

INSERT INTO tv_series (name, genre, seasons, is_running) VALUES
('Breaking Bad', 'Crime', 5, false),
('Stranger Things', 'Science Fiction', 4, true),
('The Crown', 'Historical Drama', 4, true);

Common Flags and Usage Patterns

INSERT INTO … DEFAULT VALUES

When all columns have default values or can accept NULL, you can insert a row without specifying any values:

INSERT INTO tv_series DEFAULT VALUES;

INSERT INTO … RETURNING

Sometimes, you want to know what data was inserted—particularly when it comes to auto-incremented fields like id. In this case, you can use the RETURNING clause:

INSERT INTO tv_series (name, genre) VALUES ('The Mandalorian', 'Science Fiction') RETURNING id;

Common Errors and Solutions

Error Message:

ERROR: null value in column "name" violates not-null constraint

Cause:

You’re trying to insert a NULL value into a column that has been defined as NOT NULL.

Solution:

  • Make sure you provide a value for every column that can’t be NULL.

Error Message:

ERROR: duplicate key value violates unique constraint "tv_series_pkey"

Cause:

You’ve tried to insert a row with an id that already exists in the table.

Solution:

  • Let PostgreSQL handle auto-incrementing by not specifying a value for the id.
  • If you’re deliberately setting id values, make sure the one you’re inserting doesn’t already exist in the table.

Error Message:

ERROR: column "xyz" of relation "tv_series" does not exist

Cause:

The column you’re trying to insert data into doesn’t exist.

Solution:

  • Check for typos in your column names.
  • Make sure you’re inserting into the correct table.

And that’s it for our tutorial on the INSERT command in PostgreSQL! Now you know how to add data to your tables, whether it’s one row at a time or in big, beautiful batches. 🚀

Leave a Reply