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

Question

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.

Erwin got it correct in his answer, but I'd also point out that you are creating a database (poorly named database), but all of the tables you are creating are not in that database. After creating it, you will need to connect to it. — Jeremy, Sep 06 '19 at 01:04

Erwin Brandstetter · Accepted Answer · 2019-09-06T01:39:24.423

4

The database is created just fine. But if you want to create object in the new DB, you have to connect to it. In any client, including pgAdmin4.

And you cannot run CREATE DATABASE inside of a transaction. Must be committed on it's own. Executing multiple commands at once is automatically wrapped into a single transaction in pgAdmin.

You have to execute CREATE DATABASE mydb; on its own (for instance by selecting only that line and pressing F5 while being connected to any DB, 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:

"database" is no good name for a database.

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

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

edited Sep 06 '19 at 01:39

answered Sep 06 '19 at 00:58

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks, I removed "AS" and the error was no more. I read your other answer, is it really useful to put no limit on the ammount of characters in any given String or char type? – Diego Sep 06 '19 at 01:12
@Diego: If your really need a limit use `varchar(n)` or a `CHECK` constraint. Never use `char(n)` which is basically outdated. I added a couple more links to basics. – Erwin Brandstetter Sep 06 '19 at 01:26
I'm already reading your other questions just now, thank you! What I still don't get is **But if you want to create object in the new DB, you have to connect to it.** How do I connect the tables to the DB? – Diego Sep 06 '19 at 01:29
1

You don't "connect tables", you connect your session. Then run `CREATE TABLE ...` I added step-by-step instructions above. – Erwin Brandstetter Sep 06 '19 at 01:34
The case of 'VARCHAR(#)' and 'TEXT' is equivalent with 'NUMERIC' for number types? – Diego Sep 06 '19 at 01:48
@Diego: Not sure I understand the question in your last comment. Maybe ask another *question* (instead of a comment here)? – Erwin Brandstetter Sep 09 '19 at 00:28

score 0 · Answer 2 · answered Sep 06 '19 at 00:45

0

There is the ; missing after the CREATE DATABASE database (and perhaps give the db a better name).

answered Sep 06 '19 at 00:45

Islingre

2,030
6
18

This answer has nothing to do with the OP question – thanos.a Oct 22 '21 at 09:58

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

2 Answers2