How to execute sql or script file in PostgreSQL on Windows and Linux

In this article, you will learn how to execute sql or script file in PostgreSQL on Windows and Linux step by step.

Create and Execute script in POstgreSQL on Linux Environment:

On Linux, We can execute scripts files in two ways:

1. From Linux shell
2. From psql shell

Execute PostgreSQL sql script from Linux shell:

1. Create a script file.

 cat > createdatabase.sql
create database test;
\c test
create table test123(name varchar(40), age int);

2. Switch to postgres user.

sudo su - postgres

3. Then, execute the sql script using following syntax:

psql -U postgres -f /home/r2schools/createdatabase.sql

Execute PostgreSQL sql script from psql shell:

1. Create a script file.

cat > createtable.sql
\c test
CREATE TABLE students(SNO int PRIMARY KEY, SNAME varchar(50),DOB date, class int, gender varchar(5));

2. Connect to postgresql using psql shell and run the following command.

\i /home/r2schools/createtable.sql

3. Lets verify the table is created or not.


test=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | students | table | postgres
 public | test123  | table | postgres

Create and Execute script in PostgreSQL on Windows Environment:

Execute PostgreSQL sql script from psql shell On Windows:

1. Create a script file and paste the following commands and save file.


create database test;
\c test
create table test123(name varchar(40), age int);

2. Open psql from start menu

Here, path should be in single quotes and forward slashes must be given.

\i 'C:/Test/createdatabase.sql'

Execute PostgreSQL sql script from Windows command prompt:

1. Create a script file.


create database test;
\c test
create table test123(name varchar(40), age int);

2. Now open command prompt as administrator and run the following command to execute a script as shown below.

C:\WINDOWS\system32>psql -U postgres -f C:\Test\createdatabase.sql

Password for user postgres:
Output:

CREATE DATABASE
You are now connected to database "test" as user "postgres".
CREATE TABLE

If we want to stop script when an error occurred while executing script, then use ON_ERROR_STOP=on

Examples:

psql -U postgres -f /home/r2schools/createdatabase.sql ON_ERROR_STOP=on

Create and Execute script in POstgreSQL on Linux Environment:

Create and Execute script in PostgreSQL on Windows Environment:

Related Posts: