CODEWITHSUNDEEP
javasqlpostgresqlmacospsql
java_spammingBot
java_spammingBot

Reputation: 13

How connect to database psql?

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 \

Upvotes: 0

Views: 646

Answers (1)

rzwitserloot
rzwitserloot

Reputation: 102872

\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.

Upvotes: 2

Related Questions