PostgreSQLInsert Data

PostgreSQL INSERT INTO Statement

The INSERT INTO statement is utilized to insert new, distinct records into a table. Without it, your newly created database tables would remain completely empty forever.

It is one of the most critical Data Manipulation Language (DML) commands. You can seamlessly insert a single row, or forcefully insert multiple rows simultaneously.

Basic Syntax

The syntax rigorously requires the table name and the specific columns you are targeting. It is followed by the VALUES keyword and the actual data you want to insert.

The order of the values must identically match the order of the columns you specified. Text and date values must always be enclosed securely in single quotes.

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

Inserting Specific Columns

You do not have to explicitly insert data into every single column in the table. You can strategically target only the columns that require specific data points.

If a column is omitted, PostgreSQL automatically inserts a NULL value. If the column has a predefined default value, it will use that default instead.

-- Insert a user, leaving the 'Age' column empty (NULL)
INSERT INTO Users (Username, Email)
VALUES ('akash_dev', 'akash@example.com');

Inserting Multiple Rows

Executing a new INSERT query for every single row is incredibly inefficient. PostgreSQL allows you to insert multiple rows flawlessly in one single query.

You simply provide a comma-separated list of value groups after the VALUES keyword. This significantly dramatically reduces network traffic and speeds up database population.

-- Insert three users simultaneously securely
INSERT INTO Users (Username, Email)
VALUES 
  ('jane_smith', 'jane@example.com'),
  ('bob_jones', 'bob@example.com'),
  ('alice_w', 'alice@example.com');

Returning Inserted Data

PostgreSQL has an incredibly powerful, native feature called the RETURNING clause. It allows you to retrieve the data that was just successfully inserted instantly.

This is profoundly useful for retrieving newly generated SERIAL Primary Keys. It prevents you from having to run a second SELECT query to find the new ID.

-- Insert a user and immediately return their auto-generated ID
INSERT INTO Users (Username, Email)
VALUES ('new_user_123', 'new@example.com')
RETURNING UserID;

Summary

The INSERT INTO command is responsible for successfully populating your tables. Always ensure your values strictly match your column data types perfectly.

Utilize the RETURNING clause to cleanly grab auto-incrementing IDs instantly.