CODEWITHSUNDEEP
sqldatabasepostgresql
Vlad Morzhanov
Vlad Morzhanov

Reputation: 1298

PostgreSQL: Create Database, Table and populate data in a single SQL script file

I'm trying to create a database and add create a table in a single SQL script:

CREATE DATABASE w3;

CREATE TABLE w3.public.examples (
    id SERIAL PRIMARY KEY,
    text VARCHAR(200) NOT NULL
);

INSERT INTO
    w3.public.examples (text)
VALUES
    ('val1');

INSERT INTO
    w3.public.examples (text)
VALUES
    ('val2');

INSERT INTO
    w3.public.examples (text)
VALUES
    ('val3');

SELECT
    *
FROM
    w3.public.examples;

When I'm exucuting this script via psql: psql -U postgres -a -f script.sql I'm getting error:

psql:script.sql:26: ERROR: cross-database references are not implemented: "w3.public.examples"

LINE 4: w3.public.examples;

Does it mean I cannot create and use a database in a single SQL file?

Upvotes: 1

Views: 5532

Answers (1)

Abelisto
Abelisto

Reputation: 15614

After CREATE DATABASE w3; add \c w3 then remove w3. references:

CREATE DATABASE w3;

-- connect to the newly created database
\c w3

CREATE TABLE public.examples (
    id SERIAL PRIMARY KEY,
    text VARCHAR(200) NOT NULL
);

INSERT INTO
    public.examples (text)
VALUES
    ('val1'), ('val2'), ('val3');

SELECT
    *
FROM
    public.examples;

Log:

$ psql
psql (12.2 (Ubuntu 12.2-2.pgdg18.04+1), server 11.7 (Ubuntu 11.7-2.pgdg18.04+1))
Type "help" for help.

postgres=# CREATE DATABASE w3;
CREATE DATABASE
postgres=# 
postgres=# -- connect to the newly created database
postgres=# \c w3
psql (12.2 (Ubuntu 12.2-2.pgdg18.04+1), server 11.7 (Ubuntu 11.7-2.pgdg18.04+1))
You are now connected to database "w3" as user "nd".
w3=# 
w3=# CREATE TABLE public.examples (
w3(#     id SERIAL PRIMARY KEY,
w3(#     text VARCHAR(200) NOT NULL
w3(# );
CREATE TABLE
w3=# 
w3=# INSERT INTO
w3-#     public.examples (text)
w3-# VALUES
w3-#     ('val1'), ('val2'), ('val3');
INSERT 0 3
w3=# 
w3=# SELECT
w3-#     *
w3-# FROM
w3-#     public.examples;
 id | text 
----+------
  1 | val1
  2 | val2
  3 | val3
(3 rows)

w3=#

Upvotes: 1

Related Questions