How to do bulk insert in PostgreSQL?

by filiberto , in category: PHP Databases , 2 years ago

How to do bulk insert in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

2 answers

by darrion.kuhn , a year ago

@filiberto 

To perform a bulk insert in PostgreSQL, you can use the COPY command. This command allows you to copy data from a file or STDIN (standard input) to a table, or to copy the results of a SELECT query to a file or STDOUT (standard output).


Here's the general syntax for the COPY command:

1
2
3
COPY table_name (column1, column2, ...)
FROM '/path/to/file'
WITH (FORMAT CSV, DELIMITER ',', HEADER);


This will copy data from the specified file into the specified table, using the CSV format and a comma as the delimiter. The HEADER option specifies that the first line of the file contains the column names.


You can also specify the STDIN or STDOUT instead of a file path, and use the SELECT statement to copy the results of a query to a file or table:

1
COPY (SELECT * FROM source_table) TO '/path/to/file' WITH (FORMAT CSV, DELIMITER ',', HEADER);


This will copy all rows and columns from the source_table to the specified file.


There are many other options available for the COPY command, including options for specifying the encoding, null values, and more. You can find more information about the COPY command in the PostgreSQL documentation.

Member

by domenico , 7 months ago

@filiberto 

To perform a bulk insert in PostgreSQL, you can use the COPY command. This command allows you to copy data from a file or STDIN (standard input) to a table, or to copy the results of a SELECT query to a file or STDOUT (standard output).


Here's the general syntax for the COPY command:

1
2
3
COPY table_name (column1, column2, ...)
FROM '/path/to/file'
WITH (FORMAT CSV, DELIMITER ',', HEADER);


This will copy data from the specified file into the specified table, using the CSV format and a comma as the delimiter. The HEADER option specifies that the first line of the file contains the column names.


You can also specify the STDIN or STDOUT instead of a file path, and use the SELECT statement to copy the results of a query to a file or table:

1
COPY (SELECT * FROM source_table) TO '/path/to/file' WITH (FORMAT CSV, DELIMITER ',', HEADER);


This will copy all rows and columns from the source_table to the specified file.


There are many other options available for the COPY command, including options for specifying the encoding, null values, and more. You can find more information about the COPY command in the PostgreSQL documentation.