Inserting Multiple Rows:

Inserting multiple rows: You can insert multiple rows into a table in a single INSERT statement:

INSERT INTO person (NAME, age)
VALUES
('john doe', 25),
('jane doe', 20);

INSERT data and RETURNING values: If you are inserting data into a table with an auto-increment column and want to retrieve the value of the auto-incremented column, you can use the RETURNING clause:

INSERT INTO my_table (NAME, contact_number)
VALUES ('USER', 8542621)
RETURNING id;

This query returns the id of the inserted row.

Basic INSERT: In a simple table like person:

CREATE TABLE person (
  person_id BIGINT,
  NAME VARCHAR(255),
  age INT,
  city VARCHAR(255)
);

You can perform basic inserts:

-- Inserting all values into the table
INSERT INTO person VALUES (1, 'john doe', 25, 'new york');

-- Inserting specific columns
INSERT INTO person (NAME, age) VALUES ('john doe', 25);

Note: If any constraints, such as NOT NULL, exist on the table, you need to include those columns accordingly.

Insert from SELECT: You can insert data into a table based on the result of a SELECT statement:

INSERT INTO person SELECT * FROM tmp_person WHERE age < 30;

Note that the projection of the SELECT must match the columns required for the INSERT. In this case, the tmp_person table has the same columns as the person table.

UPSERT - INSERT ... ON CONFLICT DO UPDATE: Since version 9.5, PostgreSQL offers UPSERT functionality with the INSERT statement. If you have a table called my_table:

-- Insert a row, returning PK value of inserted row
INSERT INTO my_table (name, contact_number) VALUES ('one', 333) RETURNING id;

If you try to insert a row with an existing unique key, it will raise an exception. However, with UPSERT functionality, you can insert it anyway, solving the conflict:

INSERT INTO my_table VALUES (2, 'one', 333) ON CONFLICT (id) DO UPDATE SET name = my_table.name || ' changed to: "two" at ' || NOW() RETURNING *;

SELECT data into a file: You can use the COPY command to copy the table and paste it into a file. For example:

-- Copy table and paste it into a file
COPY my_table TO '/home/postgres/my_table.txt' USING DELIMITERS '|' WITH NULL AS 'null_string' CSV HEADER;

-- Display the contents of the file
\\\\! cat my_table.txt