13

I need to execute SQL from batch file. I am executing following to connect to Postgres and select data from table 

C:/pgsql/bin/psql -h %DB_HOST% -p 5432 -U %DB_USER% -d %DB_NAME% 
select * from test;

I am able to connect to database, however I'm getting the error

'select' is not recognized as an internal or external command, operable program or batch file.

Has anyone faced such issue?

This is one of the query i am trying, something similar works in shell script, (please ignore syntax error in the query if there are any)

copy testdata (col1,col2,col3) from '%filepath%/%csv_file%' with csv;
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
Ketu
  • 1,608
  • 2
  • 14
  • 30

7 Answers7

13

You could pipe it into psql

(
echo select * from test;
) | C:/pgsql/bin/psql -h %DB_HOST% -p 5432 -U %DB_USER% -d %DB_NAME%

When closing parenthesis are part of the SQL query they have to be escaped with three carets.

( 
echo insert into testconfig(testid,scenarioid,testname ^^^) values( 1,1,'asdf'^^^);
) | psql -h %DB_HOST% -p 5432 -U %DB_USER% -d %DB_NAME%
Ketu
  • 1,608
  • 2
  • 14
  • 30
jeb
  • 78,592
  • 17
  • 171
  • 225
  • 1
    this is the current query I'm trying, `( echo insert into testconfig(testid,scenarioid,testname) values( 1,1,'asdf'); ) | psql -h %DB_HOST% -p 5432 -U %DB_USER% -d %DB_NAME%` and i'm getting `value( was unexpected at this time.` any suggestions? – Ketu Jan 20 '16 at 08:57
  • 1
    Yes, see my second sample. you need three carets per closing parenthesis. `...(name1 ^^^) values(value1 ^^^)` – jeb Jan 20 '16 at 09:05
  • how can i save the output in csv file in windows ?? – RU Ahmed Jan 24 '21 at 07:54
  • `( echo SELECT * FROM docteur; ) | C:\"Program Files"\PostgreSQL\9.5\bin\psql -h localhost -p 5433 -U postgres -d docarriv_db` Here is an example ( windows 10, PostgreSQL 9.6) according to this answer. – Hicham O-Sfh Aug 13 '21 at 16:18
4

Use the -f parameter to pass the batch file name

C:/pgsql/bin/psql -h %DB_HOST% -p 5432 -U %DB_USER% -d %DB_NAME% -f 'sql_batch_file.sql'

http://www.postgresql.org/docs/current/static/app-psql.html

-f filename

--file=filename

Use the file filename as the source of commands instead of reading commands interactively. After the file is processed, psql terminates. This is in many ways equivalent to the meta-command \i.

If filename is - (hyphen), then standard input is read until an EOF indication or \q meta-command. Note however that Readline is not used in this case (much as if -n had been specified).

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • queries that that needs to be executed have batch variables in them as data. i am not very sure if external sql file can be used with this. – Ketu Jan 19 '16 at 15:46
  • @ketub: Post a sample of the queries you want to execute in the question. – Clodoaldo Neto Jan 19 '16 at 15:49
  • `copy testdata (col1,col2,col3) from '%filepath%/%csv_file%' with csv;` This is one of the query i am trying, something similar works in shell script, (please ignore syntax error in the query if there are any) – Ketu Jan 19 '16 at 15:53
2

if running on Linux, this is what worked for me (need to update values below with your user, db name etc)

psql "host=YOUR_HOST port=YOUR_PORT dbname=YOUR_DB_NAME user=YOUR_USER_NAME password=YOUR_PASSWORD" -f "fully_qualified_path_to_your_script.sql"
grepit
  • 21,260
  • 6
  • 105
  • 81
1

You cannot put the query on separate line, batch interpreter will assume it's another command instead of a query for psql. I believe you will need to quote it as well.

LeleDumbo
  • 9,192
  • 4
  • 24
  • 38
  • in Shell Script i used `<< EOF` `sql queries` `sql queries` `sql queries` `EOF` does batch has something on similar grounds? – Ketu Jan 19 '16 at 15:40
  • @ketub the "HereDoc" `<< EOF` does not work on Windows –  Jan 19 '16 at 15:53
1

I agree with Spidey:

1] if you are passing the file with the sql use -f or --file parameter

When you want to execute several commands the best way to do that is to add parameter -f, and after that just type path to your file without any " or ' marks (relative paths works also):

psql -h %host% -p 5432 -U %user% -d %dbname% -f ..\..\folder\Data.txt

It also works in .NET Core. I need it to add basic data to my database after migrations.

greygreg87
  • 152
  • 2
  • 11
0

Kindly refer to the documentation

1] if you are passing the file with the sql use -f or --file parameter
2] if you are passing individual command use -c or --command parameter

Techie
  • 1,491
  • 3
  • 18
  • 24
0

If you are trying the shell script

psql postgresql://$username:$password@$host/$database < /app/sql_script/script.sql

Akhilraj N S
  • 9,049
  • 5
  • 36
  • 42