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

Question

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, 'test@test.com', '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~1\PostgreSQL\15.2\bin\psql.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~1\PostgreSQL\15.2\bin\psql.exe "postgres://mydbadmin:mydbadminpasswd@localhost:5432/mydb"

Answer 1

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)

Answer 2

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~1\PostgreSQL\15.2\bin\psql.exe -U myuser -f myscript.sql mydb

Using pgpass

On Linux, the file should be $HOME/.pgpass. On Windows, it's %APPDATA%\postgresql\pgpass.conf.

This is a plain text file with this format:

hostname:port:database:username:password

For example:

localhost:5432:mydb:myuser:mypassword