Member Avatar for cored0mp

How do I create a table in postgres that's associated with a specific database?

Obviously from the command line one can do 

Create table tablename ();

But how do I make sure that the table is associated with the correct database?

CREATE table dbname.tablename();

is giving me the error:

ERROR:  schema "dbname" does not exist
Edited by Dani because: Moved to Databases forum
  • 2 Contributors
  • 4 Replies
  • 227 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Dani

Dani AI

Generated

PostgreSQL separates databases and schemas: a client connection is tied to a single database, and qualified names use schema.table (not database.table). That is why CREATE TABLE dbname.tablename fails with “schema ... does not exist” — Postgres interprets the left-hand identifier as a schema. was right to point out the need to connect to the intended database first, and ’s install-script fix (create DB as superuser, then reconnect as the app user) is the correct pattern.

A reliable installer workflow:

  • Open an admin connection to a different database (commonly postgres or template1) and create the new database there.
  • Close the admin connection, then open a new connection to the newly created database as the application role and run DDL (create schemas/tables, set owners, grants).
  • Ensure CREATE DATABASE runs outside a transaction (many client libs start a transaction automatically).

Example using psycopg2 (illustrates autocommit and reconnecting to the new DB):

import psycopg2
from psycopg2 import sql

admin = psycopg2.connect(dbname='postgres', user='postgres', password='...')
admin.autocommit = True
with admin.cursor() as cur:
    cur.execute(sql.SQL("CREATE DATABASE {}").format(sql.Identifier('myappdb')))
admin.close()

app = psycopg2.connect(dbname='myappdb', user='appuser', password='...')
with app.cursor() as cur:
    cur.execute("CREATE SCHEMA IF NOT EXISTS appschema AUTHORIZATION appuser")
    cur.execute("SET search_path TO appschema")
    # run table DDL here

Additional practical tips: run schema SQL files against the target DB with psql -d myappdb -U appuser -f schema.sql; use CREATE SCHEMA ... AUTHORIZATION to set ownership; grant database-level and schema-level privileges to the app role. The key takeaway (as shown by ’s solution) is to separate the database-creation phase (admin connection, autocommit) from the schema/table creation phase (connect to the new DB with the app role).

Recommended Answers

All 4 Replies

Member Avatar for Dani

I believe you need to connect to the appropriate database first, before issuing the create table command. From my understanding, it is not possible to specify a database name from within the create table command.

Member Avatar for cored0mp

,

Thanks! That makes sense, I'll give it a shot.

Member Avatar for cored0mp

It worked!

I am building an application and so I decided to create an installation script. It turns out that the installation script must first connect to the database as the postgres user in order to create the databases. What got this working was to fully disconnect and then using both new connection handles and cursor to reconnect as the application account to create the tables inside of the newly minted databases. At this point it's running.

Member Avatar for Dani

Glad you were able to get it working. I'll mark this question as solved.

Sorry that my initial post was so sparse, I was messaging you from my phone so I wasn't able to be as elaborate as I would have wanted.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.