postgres 'psql' command is not recognized in windows environment

Question

It seems like I have it set up, I have my database.yml using postgres, and I can connect the database with pgadmin III, but when I tried using the psql command to test what version, it says that is is not recognized? Any idea what I should be doing?

I'm using windows and using command prompt with Ruby on Rails. Using PostgreSQL 9.2 installed with the EnterpriseDB one-click installer.

Solution 1

Assuming you installed PostgreSQL on Windows with the PostgreSQL "One-click" installer packaged by EnterpriseDB, psql is not added to the PATH automatically. That's partly because adding it to the path could otherwise cause confusion when people have multiple versions of PostgreSQL installed.

You need to specify the full explicit path to psql, eg:

"%PROGRAMFILES%\Postgresql\9.2\bin\psql.exe"

or add psql to the user PATH environment variable, see eg this guide.

Solution 2

Open Command Line

psql --version

if on above line psql version is not displayed, then follow below steps

(For Windows 7 OS)

Mycomputer->Properties->Advanced System Settings->EnvironmentVariables->Path->Edit->
(Donot remove any existing Path)

Add this

;C:\Program Files\PostgreSQL\9.5\bin;C:\Program Files\PostgreSQL\9.5\lib

save it then

Reopen Command Prompt and repeat

psql --version

C:\Users\rumi>psql --version

psql (PostgreSQL) 9.5.0

Solution 3

it is because there should not be space between the ; and your psql path so it should be like *****;C:\Program Files\PostgreSQL\9.2\bin\.

If there is a space after ******; then it will not work. You can actually type %PATH%in your cmd prompt and see how your environment variable and space looks like. I tried both cases for you, the first with space had the same error you mentioned and the second without space worked.

Solution 4

You need to set both the bin AND the lib path in your PATH environment variable separated by semicolons:

%PROGRAMFILES%\Postgresql\9.2\bin;%PROGRAMFILES%\Postgresql\9.2\lib

View more solutions

Comments

[Craig Ringer]

Pg version? What installer did you use?Windows version?

[hellomello]

@CraigRinger It was the enterprise version, with 9.2 per your answer below, but it seems like it still doesn't work when I added psql.exe in my path

[Craig Ringer]

"Enterprise"? Do you mean the EnterpriseDB version?

[hellomello]

@CraigRinger yes. I did the windows one-click installer package

[hellomello]

Do I need to restart my command prompt? I have these in my path: C:\PostgresSQL\9.2\;C:\PostgresSQL\9.2\bin;C:\PostgresSQL\9.‌​2\bin\psql.exe. I closed the prompt and opened it again but when I tried typing psql its still not recognized?

[hellomello]

I put it on my systems environment

[Craig Ringer]

@andrewliu Only the middle one actually makes sense; you don't set the path directly to the executable, nor to some arbitrary parent directory, the path must contain the immediate parent directory. You should generally modify the user path not the system path; if no PATH variable exists for the user then add one. To verify that it's correct close and reopen cmd.exe and then type echo "%PATH%". Edit your question to add the result (copy and paste it).

[Craig Ringer]

@andrewliu Did you install PostgreSQL to C:\PostgreSQL ? That's an unusual and non-default install location. Are you sure it isn't in Program Files like usual.

[hellomello]

I installed it into C:\PostgresSQL because there was an issue before and I read somewhere that it needs to be installed outside of Program Files for my application to work or something... But as long as my PATH is to the corrected .exe file, it shouldn't be a problem where I install it right?

[Craig Ringer]

@andrewliu No, your PATH must be to the folder containing psql.exe, not to psql.exe its self. What if you run the command with absolute path, eg (assuming your install has PostgreSQL mis-spelled as PostgresSQL like your comments) "C:\PostgresSQL\9.2\bin\psql.exe". The quotes are required. Have you actually checked the path? Is there a 9.2 intermediate path in your install?

[hellomello]

I have checked the path, and it is 9.2, and the path is correct, I copied straight from the folder path itself, and I added quotes, but it still doesn't work. This is a head scratcher...

[hellomello]

Okay, I think adding it to the user instead of system path works... now its saying that my role name doesn't exists... its better than saying its not referenced

[Craig Ringer]

@andrewliu Sounds like you might've missed a semicolon when appending to the system path or something. Glad it's working. The new error is completely normal; you need to specify a username to log in. Read the user manual on client authentication. If you're still stuck after reading the manual post a new question.

[hellomello]

Thanks for your help and time!

[Cristian E.]

Also don't forget to re-open CMD.

[Pascal]

works on Windows 10 also! Though I couldn't install pg into "c:\program files".

[majurageerthan]

Is is not lib, it should be bin