Use PostgreSQL database in SQL script-CodePudding

I'm creating 3 databases from a single file:

CREATE DATABASE "products"
  WITH
  OWNER = postgres
  ENCODING = "UTF8"
  CONNECTION LIMIT = -1
  IS_TEMPLATE = False;

CREATE DATABASE "accounts"
  WITH
  OWNER = postgres
  ENCODING = "UTF8"
  CONNECTION LIMIT = -1
  IS_TEMPLATE = False;

CREATE TYPE role as ENUM ('employee', 'admin', 'customer');

Now I would like the type role to be created in the accounts database. The current script just creates the role in the 'default' postgres DB. I am used to MySQL syntax where I could use the 'use ' command. Any idea how I can use a similar command for a PostgreSQL script?

For additional context: this SQL file is executed in a PostgreSQL docker container upon initialisation.

I tried the following:

use the 'use' command. -> not recognised.
use the 'select' command. -> invalid.

CodePudding user response：

In PostgreSQL you have to specify the database when you open a connection to the server. You can not switch to a different database using SQL.

From the documentation of the low-level API for opening connections:

An application program can have several backend connections open at one time. (One reason to do that is to access more than one database.)

But as @a_horse_with_no_name already pointed out if you are using psql you can use the command \connect to open a new connection to a different database.