CONNECT within Postgres SQL file

Question

Is there a command for connecting to a database within an SQL file?

I want to create a database and then immediately begin populating it with tables and data. All contained within a single .sql file. I'm not aware of anything that replaces the \c shell command?

Example SQL file:

CREATE DATABASE mydb;
CREATE USER myusername WITH PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myusername;
CONNECT TO mydb USER myusername; --<-- something like this?

CREATE TABLE administrator (
    id integer NOT NULL,
    admin_name character varying(150),
    password character varying(255),
    active boolean,
    "timestamp" timestamp without time zone,
    email character varying(255)
);


ALTER TABLE administrator OWNER TO myusername;

CONNECT is the closest thing I've found in the documentation, but it's encountering syntax errors. I couldn't find a wrapper that specifies where the tables should go either?

ERROR: syntax error at or near "CONNECT"

\c should work provided you use psql to run that SQL script — user330315
– user330315, Commented Dec 7, 2018 at 7:04
\c is a psql command, not a shell command. You can use it inside .sql script — Slava Lenskyy
– Slava Lenskyy, Commented Dec 7, 2018 at 7:39
If your client does not have an equivalent to psql's \c, then no, you cannot do that from inside the script. — Laurenz Albe
– Laurenz Albe, Commented Dec 7, 2018 at 7:41
In PostgreSQL you establish a connection to a specific database when setting up a session. CREATE DATABASE creates a new database, so you need to establish a new connection if you want to send SQL commands to this new database. This is different from MS SQL Server, where you always connect to the server, not a specific database, and can use the "use" command to send commands to any database of your server. There you can even use tables from several databases within a single SELECT statement by fully qualifying them. PostgreSQL regards databases as separate and closed entities. — Holger Jakobs
– Holger Jakobs, Commented Dec 9, 2018 at 21:12

Mohanraj Karatadipalayam · Accepted Answer · 2022-12-21 00:28:33Z

3

Add the below statement instead of connect to db

\c mydb;

The reorder statements of execution should be

CREATE DATABASE mydb;
\c mydb;
CREATE USER myusername WITH PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myusername;

CREATE TABLE administrator (
    id integer NOT NULL,
    admin_name character varying(150),
    password character varying(255),
    active boolean,
    "timestamp" timestamp without time zone,
    email character varying(255)
);


ALTER TABLE administrator OWNER TO myusername;

answered Dec 21, 2022 at 0:28

Mohanraj Karatadipalayam

8178 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CONNECT within Postgres SQL file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related