How to run in-line SQL script in the command line?

Question

I have trouble to execute a SQL script via command line, this is what I have

"C:\..\psql.exe" -h my_server_host -U username -c 'CREATE DATABASE test;'

I got this error:

psql: warning: extra command-line argument "test;'" ignored
psql: FATAL:  database "DATABASE" does not exist

I am on Windows 7 with Postgresql 9.3.

Any idea how to do that?

You should mention your OS and your version of Postgres here. — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 2, 2014 at 22:55

Erwin Brandstetter · Accepted Answer · 2024-01-29 10:11:10Z

15

You must connect to a database to run a command, even if you want to run CREATE DATABASE, so:

"C:\..\psql.exe" -h my_server_host -U usr -c 'CREATE DATABASE test;' postgres

Double quotes for Windows, as Craig provided: ... "CREATE DATABASE test;" ...
Connecting to the default maintenance DB postgres here.

There is a better option for the purpose at hand, though: createdb from the command-line directly.

edited Jan 29, 2024 at 10:11

answered Apr 2, 2014 at 22:53

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zs2020 Over a year ago

Thanks. Your point is correct. And I need to use double quotes instead as mentioned by Craig Ringer.

Craig Ringer · Accepted Answer · 2014-04-03 01:36:33Z

1

Windows's cmd.exe expects double quotes on arguments, not single quotes.

"CREATE DATABASE test;"

http://blogs.msdn.com/b/oldnewthing/archive/2010/09/17/10063629.aspx

answered Apr 3, 2014 at 1:36

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

2 Comments

zs2020 Over a year ago

I got this error "psql: FATAL: database "<username>" does not exist", <username> is the username I passed in and it seems the command picked the username as the database name

Craig Ringer Over a year ago

Yes, it does by default. If you want a different DB name, specify it on the command line with the -d flag. See the manual for the psql command.

Vinish George · Accepted Answer · 2016-02-24 11:59:19Z

1

Use the following script to execute the query:

@echo off
SET server=my_server_host
SET database=my_db_name
SET port=my_db_port
SET username=my_user_name
SET query="CREATE DATABASE test;"
for /f "delims=" %%a in ('chcp ^|find /c "932"') do @ SET CLIENTENCODING_JP=%%a
if "%CLIENTENCODING_JP%"=="1" SET PGCLIENTENCODING=SJIS
if "%CLIENTENCODING_JP%"=="1" SET /P PGCLIENTENCODING="Client Encoding [%PGCLIENTENCODING%]: "
REM Run psql
"C:\Program Files\PostgreSQL\9.3\bin\psql.exe" -h %server% -U %username% -d %database% -p %port%  -c %query%
pause

answered Feb 24, 2016 at 11:59

Vinish George

872 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to run in-line SQL script in the command line?

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related