COPY command for bulk data loading in PostgreSQL - Step-by-Step Execution

Concept Flow - COPY command for bulk data loading

Start COPY command

↓

Read source file

↓

Parse each line

↓

Convert data to table format

↓

Insert rows into table

↓

Finish and report rows loaded

The COPY command reads data from a file, parses it line by line, converts it into table rows, inserts them, and finishes by reporting how many rows were loaded.

Execution Sample

PostgreSQL

COPY employees FROM '/tmp/employees.csv' DELIMITER ',' CSV HEADER;

This command loads data from a CSV file into the employees table, using comma as separator and skipping the header line.

Execution Table

Step	Action	Input/State	Output/Result
1	Start COPY command	Command issued	Ready to read file
2	Open file '/tmp/employees.csv'	File opened	File stream ready
3	Read first line	Line: 'id,name,salary'	Header detected, skipped
4	Read second line	Line: '1,John Doe,50000'	Parsed row: (1, 'John Doe', 50000)
5	Insert row into employees	Row data	Row inserted
6	Read third line	Line: '2,Jane Smith,60000'	Parsed row: (2, 'Jane Smith', 60000)
7	Insert row into employees	Row data	Row inserted
8	End of file reached	No more lines	COPY finished, 2 rows loaded

💡 End of file reached, all rows processed

Variable Tracker

Variable	Start	After Step 4	After Step 6	Final
Current Line	None	'1,John Doe,50000'	'2,Jane Smith,60000'	EOF
Rows Inserted	0	1	2	2

Key Moments - 3 Insights

Why does COPY skip the first line in this example?

Because the command uses CSV HEADER option, the first line is treated as column names and skipped, as shown in execution_table row 3.

What happens if the file has more rows than shown?

COPY will continue reading and inserting rows until it reaches the end of the file, as indicated by the loop in execution_table rows 4-7.

Why is the delimiter important in COPY?

The delimiter tells COPY how to split each line into columns. Here, ',' is used to separate fields, matching the CSV format.

Visual Quiz - 3 Questions

Look at the execution table, what is the output after reading the first data line?

A Header detected, skipped

B Parsed row: (1, 'John Doe', 50000)

C Row inserted

D File stream ready

💡 Hint

Check row 4 in the execution_table where the first data line is parsed.

At which step does the COPY command finish loading data?

A Step 6

B Step 7

C Step 8

D Step 3

💡 Hint

Look at the exit note and the last row in execution_table.

If the CSV file did not have a header, what would change in the execution?

A The first line would be inserted as data

B COPY would fail immediately

C Rows inserted would be zero

D Delimiter would change automatically

💡 Hint

Refer to key_moments about the HEADER option and execution_table row 3.

Concept Snapshot

COPY command syntax:
COPY table_name FROM 'file_path' DELIMITER ',' CSV HEADER;

- Reads data from a file
- Parses lines using delimiter
- Skips header if CSV HEADER used
- Inserts rows into table
- Efficient for bulk loading

Full Transcript

The COPY command in PostgreSQL loads bulk data from a file into a table. It starts by opening the file, then reads each line. If the CSV HEADER option is used, the first line is skipped as it contains column names. Each subsequent line is parsed using the specified delimiter, converting text into table columns. Each parsed row is inserted into the table. This continues until the end of the file is reached, and the command reports how many rows were loaded. This process is efficient for loading large amounts of data quickly.