CODEWITHSUNDEEP
sqlpostgresqlpowershell
Zenwalker
Zenwalker

Reputation: 1919

psql select query does not work in powershell

Below is the SQL query i am trying to execute over PostgreSQL 11.

psql -U postgres -d Database -c "\COPY (select * from public.\"UserInfo\") TO 'C:\Users\z00\Desktop\b.sql' WITH DELIMITER ','"

Above command absolutely works on Command Line. It asks for password and then reports copied X rows.

However when i translate above to powershell as shown:

Start-Process "psql.exe" "-U postgres  -d Database -c `"\COPY (select * from userinfo) TO 'c\a.txt' WITH DELIMITER ','`""  -Wait

It wont work.

Below is the combinations what i have tried in Powershell; and none of them works.

Start-Process "psql.exe" "-U postgres  -d Database -c `"\COPY (select * from public.userinfo) TO 'c\a.txt' WITH DELIMITER ','`""  -Wait  

Start-Process "psql.exe" "-U postgres  -d Database -c `"\COPY (select * from public.`"userinfo`") TO 'c\a.txt' WITH DELIMITER ','`""  -Wait

Start-Process "psql.exe" "-U postgres  -d Database -c `"\COPY (select * from `"public`".`"userinfo`") TO 'c\a.txt' WITH DELIMITER ','`""  -Wait

Start-Process "psql.exe" "-U postgres  -d Database -c `"\COPY (select * from `"public`".`"UserInfo`") TO 'c\a.txt' WITH DELIMITER ','`""  -Wait

For all above cobinations; it reports

stderr: ERROR: relation "userinfo" does not exist LINE 1: COPY ( select * from userinfo ) TO STDOUT WITH DELIMITER ',...

However; when i change the select query to a different schema in powershell:

Start-Process "psql.exe" "-U postgres   -d Database -c `"\COPY (select * from information_schema.tables) TO 'c\a.txt' WITH DELIMITER ','`""  -Wait

It absolutely works.

I just can not understand why in powershell for same username, in same Database; it can not look/access public schema? I tried setting default search path to "public" schema. No luck

Please help?

Thanks

Upvotes: 0

Views: 1628

Answers (2)

Zenwalker
Zenwalker

Reputation: 1919

All it took for me to was comparing the way CMD and Powershell do things and logging extensively in my script.

I just did a small change to the line as shown (bold text):

Start-Process "psql.exe" "-U postgres  -d Database -c `"\COPY (select * from \`"UserInfo\`") TO 'c\a.txt' WITH DELIMITER ','`""  -Wait

I just had to add \ before the table name.

Upvotes: 2

JGH
JGH

Reputation: 17846

In the first (working) example, you use "UserInfo", which is case sensitive and contains two capital letters. In the other example, you use userinfo (with or without double quotes), which is lowercase. They are 2 different tables and the 2nd does not exist

Upvotes: 0

Related Questions