Error while running PostgreSQL command from terrafrom local-exec

Question

I am trying to run some PostgreSQL command after creating the PostgreSQL.

First I tried to run the psql command line on my command prompt and it works. See below screenshot

The same I would like to do from terraform so added below code and it worked. Which means that psql command is able to connect to the server.

Now the next step is to execute some command. Every time I try to run command getting "The system cannot find the file specified." error. See below screen

Updated(11/27): After doing hours of research below worked for me

Answer 1

Now the next step is to execute some command. Every time I try to run command getting "The system cannot find the file specified." error.

The error "The system cannot find the file specified" typically indicates that the psql command is unable to locate the specified file.

Here is a PL/pgSQL command syntax to check the table in the database. For more details, refer to the PLSQL doc

'SELECT * FROM <table-name>; 

Here is the updated Terraform code for executing psql commands using Terraform.

    provider "azurerm" {
      features {}
    }

    resource "null_resource" "db_setup" {
      provisioner "local-exec" {
        command = "psql -h databasename.postgres.database.azure.com -p 5432 -U user -d postgres -c 'SELECT * FROM student;'"
        environment = {
          PGPASSWORD = "xxxxx"
        }
      }
    }

Terraform result:

Plsql command result