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

Question

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?

Abelisto · Accepted Answer · 2020-03-18 12:37:40Z

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=#

Thanks! Not it works. By the way, I've tried \c earlier and received Syntax error. I guess it was because I added semicolon after the statement and thought it was \c statement error and I cannot use it there.
@VladMorzhanov It is not a statement but "metacommand" of the psql utility: postgresql.org/docs/current/…. Thus it works in the psql only. I updated the answer with IO.
Note taht if your tables are created in the default schema 'public' you don't need to reference to them by dot notation, just remove the 'public.' part of your clauses

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related