How to write bash script to insert the records from file my_records.txt into PostgreSQL DB table?

One | 1 | 2021-01-01 01:01:01 
Two | 2 | 2022-02-02 02:02:02
Three | 3 | 2023-03-03 03:03:03
Four | 4 | 2024-04-04 04:04:04
Five | 5 | 2025-05-05 05:05:05

First step, create the table of the corresponding structure in the PostgreSQL DB.

postgres=# create table your_table_name("name" varchar(100), "number" smallint, "date" timestamp);
CREATE TABLE

Here's a bash script to insert the records from a file called "my_records.txt" into a PostgreSQL database table:

#!/bin/bash

# Set the database host, port, name, username and password
export DB_HOST="your_db_host"
export DB_PORT="your_db_port"
export DB_NAME="your_db_name"
export DB_USER="your_db_user"
export PGPASSWORD='your_db_password'

# Loop through the lines in the file
while read -r line
do
  # Split the line by '|' delimiter
  IFS='|' read -ra values <<< "$line"

  # Extract the values
  name="${values[0]}"
  number="${values[1]}"
  date="${values[2]}"

  # Insert the values into the database
  psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "INSERT INTO your_table_name (name, number, date) VALUES ('$name', '$number', '$date')"
done < "my_records.txt"

Note that you need to replace "your_db_host", "your_db_port", "your_db_name", "your_db_user", "your_db_password", "your_table_name" with your own database details and table name.

dmi@dmi-laptop:~$ cat populate_file_recs_into_pg_table.sh

#!/bin/bash

# Set the database host, port, name, username and password
export DB_HOST="localhost"
export DB_PORT="5442"
export DB_NAME="postgres"
export DB_USER="myuser"
export PGPASSWORD='mypwd'

# Loop through the lines in the file
while read -r line
do
  # Split the line by '|' delimiter
  IFS='|' read -ra values <<< "$line"

  # Extract the values
  name="${values[0]}"
  number="${values[1]}"
  date="${values[2]}"

  # Insert the values into the database
  psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "INSERT INTO your_table_name (name, number, date) VALUES ('$name', '$number', '$date')"
done < "my_records.txt"

dmi@dmi-laptop:~$ ./populate_file_recs_into_pg_table.sh
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
dmi@dmi-laptop:~$ 

Here's an updated script that adds a check to ensure that the number of inserted records matches the number of records in the table:

#!/bin/bash

export DB_HOST="localhost"
export DB_PORT="5442"
export DB_NAME="postgres"
export DB_USER="myuser"
export PGPASSWORD='mypwd'
export DB_NAME="postgres"
export TABLE_NAME="your_table_name"
export RECORDS_FILE="my_records.txt"

# Count the number of records in the file
count_num_of_recs_in_the_file=$(wc -l < $RECORDS_FILE)
echo "count_num_of_recs_in_the_file: $count_num_of_recs_in_the_file"

# Count the number of records in the table before the insert
count_before=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT COUNT(*) FROM $TABLE_NAME" -t)

# Insert the records from the file
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "\copy $TABLE_NAME FROM $RECORDS_FILE WITH DELIMITER '|'"

# Count the number of records in the table after the insert
count_after=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT COUNT(*) FROM $TABLE_NAME" -t)

# Compare the counts to ensure that the correct number of records were inserted
if [ "$count_after" -eq $(( "$count_before" + "$count_num_of_recs_in_the_file" )) ]; then
    echo "All records were inserted successfully."
else
    echo "Error: Incorrect number of records inserted."
fi

This script adds two SELECT COUNT(*) queries to count the number of records in the table before and after the insert. It then compares the counts to ensure that the correct number of records were inserted. The expected number of records in the table is calculated by adding the number of records in the file to the count before the insert.

In this code, I've used a copy approach to populate the table with the records.

[e-mail]