GAMBAS BOOK 3.19.1

One of the tasks of a DB administrator is to

• to create new database users,
• create new databases and
• define the required DB rights for each DB user.

You can use suitable software such as pgadmin4 or dbeaver for this work or use a DB client programme such as psql. So that you can follow all the examples presented, you should create a DB user 'test' and a database 'test' for your DBMS PostgreSQL. The new DB user is granted full access to the database `test`.

DB-Benutzer:    test
DB-Passwort:    test
Datenbankname:  test

Start the DB monitor psql in a console as DB administrator with the DB name postgres and the password appw, which is requested by the option -W, in order to establish a connection to the DB server:

$ psql -h 127.0.0.1 -p 5432 -U postgres -W

The following applies when working with the DB client programme:

• First select the specified SQL statement or psql command, which always begins with a backslash,
• then switch to the console with the DB monitor and finally
• enter the SQL statement or the psql command with <CTRL>+<SHIFT>+V or with 'Paste' from the context menu (RMT):

postgres=# CREATE ROLE test WITH LOGIN ENCRYPTED PASSWORD 'test';
postgres=# COMMENT ON ROLE test IS 'PostgreSQL-Test-User';

This gives you an overview of all DB users created on the DB server:

postgres=# \du+

Creating a database is done quickly. Note that the database has an owner - the DB user test created in the first section.

• First select the specified SQL statement or psql command, which always begins with a backslash,
• then switch to the console with the DB monitor and finally
• enter the SQL statement or the psql command with <CTRL>+<SHIFT>+V or with 'Insert' from the context menu (RMT):

The following statement creates a new database on the DB server:

CREATE DATABASE test
  WITH OWNER = test
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'de_DE.UTF-8'
       LC_CTYPE = 'de_DE.UTF-8'
       CONNECTION LIMIT = -1;

This is followed by the execution in the DB client programme, which will report 'CREATE DATABASE' if successful:

postgres=# CREATE DATABASE test
  WITH OWNER = test
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'de_DE.UTF-8'
       LC_CTYPE = 'de_DE.UTF-8'
       CONNECTION LIMIT = -1;
CREATE DATABASE
postgres=#

The database is then optionally given a short description:

postgres=# COMMENT ON DATABASE test IS 'Test-Database';

The command \d+ displays an overview of all databases on the DB server:

postgres=# \d+
postgres=# \quit

When you exit the DB monitor psql, your work as DB administrator is finished for the time being. Now switch from DB administrator to your role as DB user. This starts with you entering

• establish a connection to the DB server from the DB client with the DB account of the DB user (test, test),
• connect to the database `test`,
• create a new DB table,
• insert data into the table and
• read data from the DB table from certain DB fields.

hans@mint20:~$ psql -h 127.0.0.1 -p 5432 -U test -W
Password:
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
 
test=> \connect test
Password:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "test" as user "test".
test=>

The direct connection to the `test` database with the command extended by the –dbname option is faster:

hans@mint20:~$ psql -h 127.0.0.1 -p 5432 --dbname=test -U test -W

To create a table, use the following SQL statement:

CREATE TABLE IF NOT EXISTS
  test
    (
  	id 		SERIAL PRIMARY KEY,
  	vorname 	TEXT NOT NULL,
  	nachname 	TEXT NOT NULL,
  	wohnort 	TEXT NOT NULL,
  	plz 		TEXT,
  	strasse 	TEXT,
  	gebdatum 	DATE, 		-- Geburtstag. Das DB-Speicherformat ist:  yyyy-mm-dd. ← Kommentar
  	tfestnetz 	TEXT,
  	tmobil 	TEXT,
  	email 		TEXT		-- Ohne abschließendes Komma!
)
;

Option: The DB table `test` is then also provided with a short description:

postgres=# COMMENT ON TABLE test IS 'Test-Table';

With the psql command `\d test` or `\d+ test` you always receive a description of the DB fields of a DB table:

test=> \d test
                              Table "public.test"
  Column   |  Type   | Collation | Nullable |             Default
-----------+---------+-----------+----------+----------------------------------
 id        | integer |           | not null | nextval('test_id_seq'::regclass)
 vorname   | text    |           | not null |
 nachname  | text    |           | not null |
 wohnort   | text    |           | not null |
 plz       | text    |           |          |
 strasse   | text    |           |          |
 gebdatum  | date    |           |          |
 tfestnetz | text    |           |          |
 tmobil    | text    |           |          |
 email     | text    |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

Enter 45 data records in the DB table `test`. Select the SQL statement below, switch to the console with the DB monitor and enter the SQL statement with <CTRL>+<SHIFT>+V:

INSERT INTO test (vorname,nachname,wohnort,plz,strasse,gebdatum,tfestnetz,tmobil,email)
  VALUES
    ('Arno','Adler','Arneburg','39606','Am Hafen 3','1981-12-08','03937864322','1715749482','arno.adler@arneburg.de'),
    ('Bruno','Bär','Berlin','10404','Bode-Strasse 1','1986-12-06','03094157777','1716771528','bruno.baer@freenet.de'),
    ('Gerda','Geier','Gera','07997','Gartenweg 23','1980-09-12','03657788989','1714472473','gerda.geier@gera.de'),
    ('Lutz','Lama','Leipzig','04103','Lessing-Allee 5','1989-02-25','0641432222','1717346836','lutz.lama@wwleipzig.de'),
    ('Maria','Meise','München','80805','Malergasse 10','1980-07-20','0867554324','1716821096','maria.meise@mawa.com'),
    ('Emil','Elch','Erfurt','99033','Eggert-Strasse 3c','1989-04-18','0361334455','1714287196','emil.elch@erfurt.de'),
    ('Detlef','de Hirsch','Dresden','01067','Deichweg 8','1984-01-28','0351876544','1716271745','detlef.dehirsch@dresden.de'),
    ('Hans-Helmut','Huhn','Hamburg','22111','Hafengasse 90','1988-01-26','04067554008','1716360418','hhhuhn@arcor.com'),
    ('Friedrich','Fledermaus','Frankfurt a.M.','60308','Flusenweg 12','1983-01-01','06101666664','1715209075','fledermaus74@web.de'),
    ('Norbert','Natter','Nürnberg','90402','Nord-Strasse 6a','1980-04-05','091155224324','1713545289','norbert.natter@web.de'),
    ('Clara','Chamäleon','Celle','29229','Claus-Kurt-Weg 1','1990-05-28','05141554678','1717398273','clara.camae@leon.de'),
    ('Ingelore','Igel','Immenstadt i.A.','87509','Imm-Reute 3','1988-12-10','08328552233','1715500891','ingelore.igel@web.de'),
    ('Stephanie','Storch','Stendal','39596','Strohgasse 55b','1989-05-10','039312232366','1716249744','stephanie.storch@reiseland.de'),
    ('Kurt','Kater','Köln','29229','Kanalstrasse 107','1985-05-11','0221767878789','1716968271','kurt.kater@koeln-nord.de'),
    ('Susanne','Sperling','Stuttgart','70173','Strasse der Einheit 3','1988-11-28','05141554678','1713955401','susanne.sperling@aol.com'),
    ('Doreen','Delphin','Dessau-Roßlau','06842','Drosselweg 2','1983-08-01','03455662271','1714171247','ddelphin@arcor.net'),
    ('Andreas','Ameise','Aachen','52064','An der Mauer 4','1982-04-19','02419854332','1715571201','andreas.ameise@aachen.de'),
    ('Fred','Fliege','Fürstenfeldbruck','82276','Feldrain 2','1985-06-23','08148642278','1717564483','fred.fliege@web.de'),
    ('Brunhilde','Bussard','Berlin','10405','Berg-Strasse 44c','1987-11-12','03066422783','1714613147','brunhilde.bussard@web.de'),
    ('Karl-Heinz','Kranich','Kleve','82276','Kurze Strasse 9','1985-02-11','028216666774','1715671343','khkranich@kranich.net'),
    ('Ziri','Zebra','Zittau','02763','Zur Post 33e','1983-06-08','035853376223','1716739494','ziri.zebra@zittauerland.de'),
    ('Paul','Pferd','Pinneberg','25421','Paradegasse 5','1990-03-01','041015555577','1714546720','paul.pferd@rennbahn.de'),
    ('Quirina','Qualle','Querfurt','06268','Quartz-Steg 33','1985-04-28','03477123456','1713455231','quirina.qualle@arcor.de'),
    ('Rüdiger','Rüsselkäfer','Rüdesheim','65385','Radweg 23','1980-06-03','067220042278','1714576104','r.kaefer@web.de'),
    ('Victor','Vogelspinne','Vogelsberg','99610','Vor dem Tore 4-5','1980-11-12','03637290887','1714737048','vvspinne@herbarium.de'),
    ('Jens-Peter','Jaguar','Jena','82276','Janusweg 66','1984-09-14','036410815','1716538095','jp.jaguar@juga.de'),
    ('Otto','Otter','Oldenburg i.H.','23779','Oberer Steg 2','1981-06-22','046313423888','1716342612','otto.otter@freenet.de'),
    ('Thomas','Taube','Taubenheim','02689','Tannwald-Strasse 4','1984-10-18','03593650441','1714320997','tom.taube@taubeev.de'),
    ('Ullrich','Uhu','Ulm','89073','Ulmenweg 7','1988-02-07','073181862258','1713517261','ullrich.uhu@ulm.de'),
    ('Walter','Wisent','Wittenberge','19322','Wiesenweg 50','1989-05-14','038771111258','1715563424','walter.wisent@web.de'),
    ('Dorothea','Dorsch','Düsedau','39606','Dammer Weg 123','1982-07-03','03937345222','1715128617','dodo@dorsch.com'),
    ('Ernst','Esel','Erfurt','99033','Eckstrasse 6','1989-01-18','0361337612','1715533977','ernst.esel@aol.com'),
    ('Hannelore','Haifisch','Hamburg','22131','Hochbauamt 2-7','1980-05-18','04012384566','1714506260','halohai@freenet.de'),
    ('Hans','Hundt','Hameln','31785','Hoher Weg 34','1984-10-16','0515176395588','1716869476','hans.hundt@haba.com'),
    ('Nancy','Nasenbär','Naumburg','06618','Neue Strasse 44a','1984-10-15','0344567888','1714332937','nancy.nb@zoo-nb.de'),
    ('Wolfgang','Wolf','Werra','06766','Waldweg 6a','1984-10-14','036922123','1716355311','wolf.wolter@werra-berg.de'),
    ('Erwin','Eule','Erfurt','99099','Erzberg 6','1988-11-11','0361234333','1718885311','eeule@freenet.de'),
    ('Bernd','Bisam','Berlin','10315','Bad-Ring 1c','1990-09-09','0304409999','1527035312','bernd@bisam-park.de'),
    ('Hans','Hase','Hassel','39695','Hain 12','1988-03-05','0393217752','1727775311','hassel.hase@web.de'),
    ('Stefan','Spatz','Stendal','39576','Seeweg 6a','1989-11-11','03931844123','1715454561','spatz89@aol.com'),
    ('Karl','Keiler','Kassel','34117','Kleiner Weg 3','1988-12-12','05618967944','1527022212','karl.k@arcor.de'),
    ('Xenia','Xanter','Xanten','46509','Xyloner See 2','1989-04-03','0280134556','1612345799','x.y.jolle@freenet.de'),
    ('Luise','Laus','Löbau','02708','Langer Kolken 1','1988-06-23','03587622022','1622323239','familia.l@genua.it'),
    ('Zeno','Zander','Zingst','18374','Ziegenhof 4c','1987-04-04','03823299744','1530345888','zeno.zack@web.de'),
    ('Volker','von Viper','Verden','27283','Volksbad 5c','1987-09-03','04231667293','1724522277','von.viper@web.de');

You can use the following SQL statement to output the data from the fields “first name”, “last name”, “postcode”, “city” and the calculated current age, for example - but only from the cities whose postcode begins with a 3:

SELECT vorname AS "Vorname", nachname AS "Nachname", plz AS "PLZ", wohnort AS "Ort",
EXTRACT(YEAR FROM age(gebdatum)) AS "Alter"
FROM test
WHERE plz LIKE '3%'
ORDER BY EXTRACT(YEAR FROM age(gebdatum)) DESC;

test=> SELECT vorname AS "Vorname", nachname AS "Nachname", plz AS "PLZ", wohnort AS "Ort",
test-> EXTRACT(YEAR FROM age(gebdatum)) AS "Alter"
test-> FROM test
test-> WHERE plz LIKE '3%'
test-> ORDER BY nachname DESC;
 
  Vorname  | Nachname |  PLZ  |   Ort    | Alter
-----------+----------+-------+----------+-------
 Stephanie | Storch   | 39596 | Stendal  |    32
 Stefan    | Spatz    | 39576 | Stendal  |    31
 Karl      | Keiler   | 34117 | Kassel   |    32
 Hans      | Hundt    | 31785 | Hameln   |    36
 Hans      | Hase     | 39695 | Hassel   |    33
 Dorothea  | Dorsch   | 39606 | Düsedau  |    38
 Arno      | Adler    | 39606 | Arneburg |    39
(7 rows)
 
test=> quit
hans@mint20:~$