How to execute postgres sql queries from a batch file?

Question

I need to execute SQL from a batch file. I follow these steps to connect to Postgres and select data from a table

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

I can connect to the database, however I get an error

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

Has anyone encountered such a problem?

This is one of the requests I'm trying to make, something like this works in a shell script (please ignore the syntax error in the request, if any)

 copy testdata (col1,col2,col3) from '%filepath%/%csv_file%' with csv; 

Answer 1

You can pass it to psql

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

When closing parentheses in an SQL query, they must be escaped with three carriages.

 ( echo insert into testconfig(testid,scenarioid,testname ^^^) values( 1,1,'asdf'^^^); ) | psql -h %DB_HOST% -p 5432 -U %DB_USER% -d %DB_NAME% 

Answer 2

You cannot put the query on a separate line, the batch interpreter will consider it a different command instead of the query for psql. I believe that you will need to quote it too.

Answer 3

Use the -f option to pass a 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 = file name

Use the file name as the source of commands instead of reading commands interactively. After processing the psql file is completed. This is largely equivalent to the meta command \ i.

If the file name is (hyphen), then standard input is read before EOF or metadata \ q is displayed. Note, however, that Readline is not used in this case (as if -n were specified).

Answer 4

Please refer to the documentation.

1], if you transfer the file using the sql -f or --file option 2], if you use an individual command, use the -c or --command