Bash scripts for PostgreSQL
Introduction
It is often convenient to run database queries from small bash scripts you can study some examples of such queries in this article.
PostgreSQL script from Bash
To run a Bash script with a PostgreSQL command, it is enough to create a file script.sh with content like this
#!/bin/bash
PGPASSWORD=PASSWORD psql -h HOST -U USERNAME -d DB_NAME -c "YOUR COMMAND"
For example
#!/bin/bash
PGPASSWORD=QWERTY psql -h 127.0.0.1 -U andrei -d urnsu -c "SELECT * FROM news"
And call it
. script.sh
If the SQL script is large and contains complex syntax, it is not so easy to make it work with bash syntax.
In such a situation it can help to create a separate file
sql_script.sql
and a file with a bash script
bash_script.sh
From
bash_script.sh
you can call
sql_script.sql
as follows
#!/bin/bash
cat sql_script.sql | PGPASSWORD=QWERTY psql -h 127.0.0.1 -U andrei -d urnsu
. bash_script.sh
Running a script from a file
On Windows
Several examples for
Windows Subsystem for Linux
First let us check that the environment variables are in order.
To do this, type psql.exe into the console, press Enter and check that
WSL
does not complain about an unknown command.
If it complains, read my advice in the article
The PATH environment variable
We write the script
script.sql
CREATE TABLE person (
id int,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(5),
date_of_birth DATE
)
Let us apply this script to the database
HeiHei_ru_DB
On my machine postgres runs locally on port 5433. Yours may be
on 5432, check it.
cat script.sql | psql.exe -h localhost -p5433 -U postgres HeiHei_ru_DB
Password for user postgres:
CREATE TABLE
It is time to do something closer to a real script
Some constraints have to be put on the table fields and properties added to them.
CREATE TABLE booking_sites (
id BIGSERIAL NOT NULL PRIMARY KEY,
company_name VARCHAR(50) NOT NULL,
origin_country VARCHAR(50) NOT NULL,
age VARCHAR(3) NOT NULL,
date_of_birth DATE NOT NULL,
website_url VARCHAR(50)
);
Now let us run this script, no longer against the test but against the production database heihei (which matches the site name HeiHei.ru, but if you write .ru there will be a syntax error ERROR: syntax error at or near ".")
cat booking_sites.sql | psql.exe -h localhost -p5433 -U postgres heihei
Password for user postgres:
CREATE TABLE
On Linux
Let us run the following script from the file my_script.sql
SELECT data FROM certificates WHERE (id='8c585c96-a593-456d-4a87-4d12322c6063');
cat my_script.sql | psql -h localhost -p5432 -d db_name -U andrei
Password for user andrei: data ------------------------------------------------------------------ -----BEGIN CERTIFICATE----- + MIIDDDCCAfSgAwIBAgIIBkh3ZXTEqB8wDQYJKoZIhvcNAQELBQAwGzEZMBcGA1UE+ AxMQUHJpdlggd2ViIHNlcnZlcjAeFw0yMjA3MTkxMzQ0MDJaFw0yMjA3MjAxMzQ0+ MDJaMBsxGTAXBgNVBAMTEFByaXZYIHdlYiBzZXJ2ZXIwggEiMA0GCSqGSIb3DQEB+ AQUAA4IBDwAwggEKAoIBAQCpOJuP+kmgcNlxnfZ9nBwpvSO8LANeW6DuAgc9D4P0+ RI5qGvF5duVNV7DrY3V3+Lwu4udWUUUnm7NvKbR9vaUcB7tx+2O1SXFwAO0Z0UA5+ nswTlYz0Hblw0Z1SIlhv2VValv6jP66KL37mAYc1QObxNwH4wqEPePix0DUlm0+w+ r8qHKPhpAwL5Haf00zJWwc5t1ENNLhGSG90eYhas+cVkdnWgxn0fbcc23R7QU1Bg+ KxLyT23ptqzlRq/6Vdlt1DDt6/sd92ndY/72txUUA1aT9Ycw+4lK2Z7uIxUXGy65+ iz3gu2THWRll82HBFPrsFKthK+hGvwF/IhBiR9ly8FgJAgMBAAGjVDBSMA4GA1Ud+ DwEB/wQEAwIFoDATBgNVHSUEDDAKBggrBgEFBQcDATAMBgNVHRMBAf8EAjAAMB0G+ A1UcDgQWBBSVXlTlVVB5a4yYYlbJ/SwWAjPo/zANBgkqhkiG9w0BAQsFAAOCAQEA+ PmpmWqcBZo4b8JuBrhcI1hGR5lNkq7/eWZYkCvPLjzkDAnWdf4SZZQNPE6O24xAx+ aQNcn2PeBaO5eJVuZwEQMqoRr2j9CO637XRPv0zAOKCuKfcnGuUrerKLiuSQDT1Y+ hwXzp0lCWO8BT/chIJvjSPHYVKgJimXi9oLtf4QvPvliBt0dY/J6LAnewsEYD9ug+ iUA8iCpAITBVDybF462d+m4L5YN0gmLx+sGhPqNHjIWP8HN73HwvIvuCL+lTAk2M+ FGmYVaqthWDts4UNwdaVA1ZnmJVOjklhn2Jm6yKRNs7ZJ2kESX0k+gj33x0AAdcz+ Q5hgzQSUF3wc4rTnFW90/g== + -----END CERTIFICATE----- + (1 row)
Importing from a .csv file
For the file to open, it is desirable to put it in the folder
Or configure the permissions properly, otherwise there will be an error
An example script for importing
set datestyle to "US";
COPY public.people (id, date, name, address, comment)
FROM '/tmp/file.csv'
DELIMITER ','
CSV HEADER ENCODING 'UTF8'
QUOTE '"'
ESCAPE '''';
Article author: Andrei Olegovich