Inserting and Reading Data

Inserting Rows

INSERT INTO customers (name, email, city) VALUES
  ('Priya Sharma', 'priya@example.com', 'Mumbai'),
  ('Rahul Patel', 'rahul@example.com', NULL);

Returning Values

A PostgreSQL specialty: INSERT, UPDATE and DELETE can hand back the rows they touched.

INSERT INTO customers (name, email) VALUES ('Anjali', 'anjali@example.com') RETURNING id, name;
UPDATE customers SET city = 'Pune' WHERE id = 1 RETURNING *;

Selecting Data

SELECT name, email FROM customers;
SELECT * FROM customers WHERE city = 'Mumbai';
SELECT * FROM customers ORDER BY name DESC LIMIT 5;
SELECT DISTINCT city FROM customers WHERE city IS NOT NULL;

Upsert With ON CONFLICT

INSERT INTO customers (name, email, city)
VALUES ('Priya', 'priya@example.com', 'Delhi')
ON CONFLICT (email)
DO UPDATE SET city = EXCLUDED.city;

Deleting

DELETE FROM customers WHERE id = 5 RETURNING *;

Key Points

• INSERT adds rows; multiple rows at once are fine.
• RETURNING gives back affected rows immediately.
• ON CONFLICT handles duplicate keys gracefully.
• WHERE, ORDER BY, LIMIT and DISTINCT shape SELECT results.