How connect to database psql?

Question

I'm trying to create and connect db using PostgreSQL and this SQLfile:

-- recreate the user --
REVOKE ALL PRIVILEGES ON DATABASE "playground" from student;
REASSIGN OWNED BY student TO postgres;
DROP DATABASE playground WITH (FORCE);
DROP USER student;
CREATE USER student WITH PASSWORD '1';

-- recreate the database --
CREATE DATABASE playground OWNER student;
GRANT ALL PRIVILEGES ON DATABASE "playground" TO student;

-- fulfill the database --
-- \connect playground student
\connect "dbname=playground user=student password=1"

BEGIN;
\i sql/schema.sql
\i sql/data.sql
COMMIT;

But when I try to run it using JDBC / IntelliJ idea I have an error

syntax error at or near \

https://stackoverflow.com/questions/31537204/execute-several-sql-files-in-a-single-transaction-using-postgresql-and-bash — JCompetence, Nov 27 '20 at 15:18
`psql` is not a database. Postgres is the database. The `psql` tool is a native app that connects to the Postgres database server to give you a command-line interface. This avenue has nothing to do with Java. An alternative avenue is JDBC, for connecting to the Postgres database from within a Java app. This avenue has nothing to do with the *psql* tool. — Basil Bourque, Nov 27 '20 at 16:38

score 2 · Accepted Answer · edited Nov 27 '20 at 16:32

\i and co are commands that the psql tool processes. The Postgres JDBC driver doesn't understand such commands.

You have two options.

Do those commands in Java

\connect is done by dropping your JDBC Connection (close() it), then create a new connection by crafting a JDBC URL with the right dbname and user/pass properties.

\i is done by opening the file and reading each line (using e.g. BufferedReader or Files.lines), then sending each line to PSQL by creating a Statement and executing it.

Use process exec

You can also use ProcessBuilder to fire up the actual psql command, passing it this script by getting the standard-in of the psql processes you start, sending these lines to /usr/bin/psql or where-ever it is. You'll need to know where psql lives to do this, which is not neccessarily easy.

How connect to database psql?

1 Answers1

Do those commands in Java

Use process exec