Import PostgreSQL Script Files

Importing PostgreSQL script files is a common operation when working with PostgreSQL databases. These script files may contain SQL commands to create databases, tables, views, and indexes, as well as insert data and define relationships.

This article explains how to move dump file contents to PostgreSQL server using the most popular tools.

Understanding the Structure of PostgreSQL Script Files

PostgreSQL script files typically have a .sql extension. These scripts can include:

• Database Creation Statements: CREATE DATABASE ...
• Table Creation Statements: CREATE TABLE ...
• Data Insertion Statements: INSERT INTO ...
• Function, Trigger, and View Definitions: CREATE FUNCTION ..., CREATE VIEW ..., etc.

A basic example of a script file might look like this:

-- Creating a database
CREATE DATABASE my_database;

-- Using the database
\c my_database;

-- Creating a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- Inserting data into the table
INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com');
INSERT INTO users (name, email) VALUES ('Jane Doe', 'jane.doe@example.com');

PostgreSQL console client (psql)

Syntax of the command line depends on the structure of the dump file. If the dump file contains CREATE DATABASE statement you should call the tool as follows:

psql -h hostname -U username -f {SQL script file name}

Otherwise:

psql -h hostname -d databasename -U username -f {SQL script file name}

Where:

• -U {username}: Specifies the PostgreSQL username (use your actual PostgreSQL username)
• -d {databasename}: Specifies the target database where the script will be imported
• -f {SQL script file name}: Path to the SQL script file to be executed

Of course you need to replace parameters in figure brackets {...} by actual values.

phpPgAdmin

To import SQL script file into PostgreSQL database using this tool, take the following steps:

1. Select the database
   
2. Navigate to the "SQL" button
   
3. Click the "Choose File" (or "Browse") button and select the SQL file from your computer
   
   
4. Click "Execute" button

Using a Script in PostgreSQL with Docker

If you're using PostgreSQL in a Docker container, you can import a script file from your host machine into the Dockerized PostgreSQL container.

1. Start Your PostgreSQL Container. If you don�t already have a running PostgreSQL container, start one with the following command:

  docker run --name my_postgres_container -e POSTGRES_PASSWORD=mysecretpassword -d postgres

2. Copy the Script File into the Container. Use docker cp to copy the SQL script file into the container:

  docker cp /path/to/script.sql my_postgres_container:/script.sql

3. Run the SQL Script Inside the Container. Execute the script inside the container by running:

  docker exec -i my_postgres_container psql -U postgres -d my_database -f /script.sql

This command will run the SQL script inside the Docker container.

Have questions? Contact us