Tutorial by Examples

Let's say we have a simple table called person: CREATE TABLE person ( person_id BIGINT, name VARCHAR(255). age INT, city VARCHAR(255) ); The most basic insert involves inserting all values in the table: INSERT INTO person VALUES (1, 'john doe', 25, 'new york'); If you wa...

You can insert multiple rows in the database at the same time: INSERT INTO person (name, age) VALUES ('john doe', 25), ('jane doe', 20);

You can insert data in a table as 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 person.

COPY is PostgreSQL's bulk-insert mechanism. It's a convenient way to transfer data between files and tables, but it's also far faster than INSERT when adding more than a few thousand rows at a time. Let's begin by creating sample data file. cat > samplet_data.csv 1,Yogesh 2,Raunak 3,Varun ...

If you are inserting data into a table with an auto increment column and if you want to get the value of the auto increment column. Say you have a table called my_table: CREATE TABLE my_table ( id serial NOT NULL, -- serial data type is auto incrementing four-byte integer name character varying...

You can COPY table and paste it into a file. postgres=# select * from my_table; c1 | c2 | c3 ----+----+---- 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3 4 | 4 | 4 5 | 5 | (5 rows) postgres=# copy my_table to '/home/postgres/my_table.txt' using delimiters '|' with null as 'null_s...

since version 9.5 postgres offers UPSERT functionality with INSERT statement. Say you have a table called my_table, created in several previous examples. We insert a row, returning PK value of inserted row: b=# INSERT INTO my_table (name,contact_number) values ('one',333) RETURNING id; id ---- ...