Data Load and Import

If you are dealing with small datasets during the development phase, you have the opportunity to easily test ideas by utilizing the CSV import feature.

Loading

Multiple rows insert

The INSERT statement allows you to insert multiple rows into a table using a single statement as the following:

INSERT INTO table_name(column1,column2…)
VALUES (value1,value2,…),
       (value1,value2,…),

INSERT INTO shippers(companyName,phone)
VALUES ('UPS','1-800-782-7892'),
       ('DHL','1-800-225-5345')

Load CSV

Utilizing PostgreSQL's COPY command is recommended when working with extensive data collections.

• The data is imported directly from a file into a table.

• Various file formats can be utilised for transferring data, such as text, CSV, binary, and JSON.

psql -h DATABASE_URL -p 5432 -d postgres -U postgres \
 -c "\COPY projects FROM './projects.csv';"

Additionally use the DELIMITER, HEADER and FORMAT options as defined in the PostgreSQL COPY docs.

psql -h DATABASE_URL -p 5432 -d postgres -U postgres \
  -c "\COPY projects FROM './projects.csv' WITH DELIMITER ',' CSV HEADER"

If you encounter the error message "FATAL: password authentication failed for user 'postgres'", it is recommended to reset your database password in the Database Settings and attempt to log in again.

Importing

The Appizap dashboard offers a convenient interface for importing data, though it may not be the optimal choice for very large datasets due to its 100MB size limitation. This tool is better suited for smaller datasets and expedited data imports. Those dealing with substantial data sets may want to explore alternative approaches such as pgloader for more efficient data importing.

• Locate the table within the Table Editor interface.

• Click on “Insert” then choose "Import Data from CSV" and follow the on-screen instructions to upload your CSV file.