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