Search code examples
sqlpostgresqlddlpgadmin-4

How do I create a PostgreSQL database using SQL's DDL on pgadmin4?

I'm learning DDL to create and define an SQL database with Postgresql 10. I have the something like the following SQL code in an .sql file, and I want to input it in psql or PgAdmin 4, just to test the syntax and see the database structure:

CREATE DATABASE database;

CREATE TYPE t_name AS 
(      first    VARCHAR(30),
       last     VARCHAR(60) 
);

CREATE TABLE telephone_m 
(   tnumber  VARCHAR(15) NOT NULL UNIQUE
);

CREATE TABLE people 
(   curp        CHAR(18)    NOT NULL PRIMARY KEY,
    pname       t_name      NOT NULL,
    birth_date  DATE        NOT NULL,
    telephone_m VARCHAR(15) REFERENCES telephone_m
);

CREATE TABLE clients
(   curp        CHAR(18)    NOT NULL PRIMARY KEY,
    cid         SERIAL      NOT NULL REFERENCES cards,  
    clocation   VARCHAR(29)
)   INHERITS (people);

CREATE TABLE cards
(   cid         BIGSERIAL   NOT NULL PRIMARY KEY,
    curp        CHAR(18)    NOT NULL REFERENCES clients,
    trips       SMALLINT,
    distance    NUMERIC, 
    points      NUMERIC
);

CREATE TABLE drivers
(   curp        CHAR(18)    NOT NULL PRIMARY KEY,
    rfc         CHAR(22)    NOT NULL UNIQUE,
    adress      t_adress    NOT NULL
)   INHERITS (people);

I've tried in PgAdmin 4 making right-click on a new database -> CREATE Script, it opens Query Editor, I copy paste my code and execute it, but it returns: 

ERROR:  CREATE DATABASE cannot be executed from a function or multi-command string
SQL state: 25001

I've also tried using Query Tool directly from the PgAdmin tools menu with the same results.

Solution

  • The database is created just fine. But to create objects in the new DB, connect to it first. In any client, including pgAdmin4.

    You cannot run CREATE DATABASE inside a transaction. Executing multiple commands at once is automatically wrapped into a single transaction in pgAdmin.

    Execute CREATE DATABASE mydb; on its own (for instance by selecting only that line and pressing F5 while being connected to any DB in the same DB cluster, even the maintenance db "postgres". Then click on "Databases" in the object browser in the pgadmin4 main window / tab, hit F5 to refresh the view, click on the new DB, open up a new query tool with the flash icon (in a new window / tab) and execute the rest of your script there.

    psql scripts manage by using the meta-command \c to connect to the new db after creating it, within the same session.

    Asides:

    CREATE TYPE AS (...), but just CREATE TABLE (...). No AS.

    And you typically don't want to use the data type CHAR(18). See: