Question 182874 in PostgreSQL Question posted in
The official documentation can be found here.

How can I put an sql file into a postgresql database with a command line using URI?

I’ve wrote an sql file with some inserts I want to do in my database.

Here’s an example of what it’s made of :

-- User
INSERT INTO users (user_id, credit, email, passwd, alias_login, user_id) 
VALUES (1, 250, '[email protected]', '123456', '1111', 'admin');

And I’ve also wrote a batch script which install my database before and I want after the installation to put some datas inside it with this sql script inside the batch script.

I’ve already explore all the options and to be fully automatised I need to connect to my db with a string to avoid password typing like that :

C:PROGRA~1PostgreSQL15.2binpsql.exe "postgres://mydbadmin:mydbadminpasswd@localhost:5432/mydb"

But the thing that I want is to input to this my sql script considering that with this string method I can’t use the -f option or any -c "i …" thing. It just ignores the command.

Also, I’ve tried this which didn’t work at all :

type %cd%/myscript.sql | C:PROGRA~1PostgreSQL15.2binpsql.exe "postgres://mydbadmin:mydbadminpasswd@localhost:5432/mydb"
Tags:

2

Answers


  1. 0

    You could either use the PGPASSWORD environment variable or use a .pgpass file (pgpass.conf on Windows).

    Using PGPASSWORD

    • On Linux, you could just use:
    PGPASSWORD=mypassword psql -U myuser -f myscript.sql mydb

    Note: you may want to take measures so that it doesn’t stay in your Bash history.

    • On Windows (assuming cmd.exe):
    SET PGPASSWORD=mypassword
C:PROGRA~1PostgreSQL15.2binpsql.exe -U myuser -f myscript.sql mydb

    Using pgpass

    On Linux, the file should be $HOME/.pgpass. On Windows, it’s %APPDATA%postgresqlpgpass.conf.

    This is a plain text file with this format:

    hostname:port:database:username:password

    For example:

    localhost:5432:mydb:myuser:mypassword
    Login or Signup to reply.
  2. 0

    psql -c <command> works just fine with a connection string URL:

    psql -d "postgresql://user:password@host/dbname" -c "SELECT 42"

 ?column? 
══════════
       42
(1 row)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search