Postgresql cross-database references are not implemented

Question

I'm trying to create a database and then create a table in it. I wrote the query in an sql file to go with the postgres image using docker-compose.yml

When I to use docker-compose build and then docker-compose up I get an error

"psql:/docker-entrypoint-initdb.d/init.sql:18: ERROR: cross-database references are not implemented: "real_estate.public.estate" postgres | LINE 1: CREATE TABLE real_estate.public.estate ("

Here is my init.sql file

CREATE DATABASE "real_estate"
    WITH OWNER "postgres"
    ENCODING 'UTF8';
CREATE TABLE real_estate.public.estate (
    estate_title TEXT,
    estate_address TEXT,
    estate_area TEXT,
    estate_description TEXT,
    estate_price TEXT,
    estate_type TEXT,
    estate_tag TEXT,
    estate_date TEXT,
    estate_seller_name TEXT,
    estate_seller_address TEXT,
    estate_seller_phone TEXT,
    estate_seller_mobile TEXT,
    estate_seller_email TEXT
 );

Answer 1

You get that error because you are not connected to the real_estate database.

I assume that you are connected to postgres, because that's what you typically do to run CREATE DATABASE.

You'll have to terminate the existing database connection and then start one to the newly created database. Only then you are allowed to create objects in the database.

It is a feature that different databases are strictly separated, and any request to change that will be turned down.

How to do this best depends on how you run the script. If you run it with psql, a simple \c real_estate would do.