postgreSQL - psql \i : how to execute script in a given path

I'm new to postgreSQL and I have a simple question:

I'm trying to create a simple script that creates a DB so I can later call it like this:

psql -f createDB.sql

I want the script to call other scripts (separate ones for creating tables, adding constraints, functions etc), like this:

\i script1.sql
\i script2.sql

It works fine provided that createDB.sql is in the same dir.

But if I move script2 to a directory under the one with createDB, and modify the createDB so it looks like this:

\i script1.sql
\i somedir\script2.sql

I get an error:

psql:createDB.sql:2: somedir: Permission denied

I'm using Postgres Plus 8.3 for windows, default postgres user.

EDIT:

Silly me, unix slashes solved the problem.

postgresql

180883 次浏览

小开

最佳答案

Postgres started on Linux/Unix. I suspect that reversing the slash with fix it.

\i somedir/script2.sql

If you need to fully qualify something

\i c:/somedir/script2.sql

If that doesn't fix it, my next guess would be you need to escape the backslash.

\i somedir\\script2.sql

小开

Have you tried using Unix style slashes (/ instead of \)?

\ is often an escape or command character, and may be the source of confusion. I have never had issues with this, but I also do not have Windows, so I cannot test it.

Additionally, the permissions may be based on the user running psql, or maybe the user executing the postmaster service, check that both have read to that file in that directory.

小开

Try this, I work myself to do so

\i 'somedir\\script2.sql'

小开

i did try this and its working in windows machine to run a sql file on a specific schema.

psql -h localhost -p 5432 -U username -d databasename -v schema=schemaname < e:\Table.sql