PostGreSQL

Installation

apt-get install postgresql-9.5 pgadmin3

Ohne Admin-Passwort arbeiten (z.B. temporär)

• /etc/postgresql/9.5/main/pg_hba.conf
  • host all all 127.0.0.1/32 trust
  • host all all  ::1/128 trust

Administration

Benutzer anlegen

• pgadmin3 aufrufen
  • Login-Rollen: neue Rolle eintragen
• SQL:
  • CREATE ROLE jonny LOGIN ENCRYPTED PASSWORD 'md582dfb88f80303e0d0e2ffdbdc9e82b64' SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;

Test-DB

create table region (
  rid integer primary key,
  name varchar(64)
);
create table person (
  pid integer primary key,
  name varchar(64),
  rid integer references region(rid)
  );
create table sale (
  sid integer primary key,
  pid integer references person(pid),
  price numeric(13,2),
  time date
);
insert into region values(1, 'Baden-Würtemberg');
insert into region values(2, 'Bayern');
insert into region values(3, 'Hessen');

insert into person values(1, 'Mayer', 2);
insert into person values(2, 'Huber', 2);
insert into person values(3, 'Schmidt', 3);
insert into person values(4, 'Seggele', 1);

insert into sale values(1, 1, 100, '2016-07-22'); 
insert into sale values(2, 1, 200, '2016-08-22); 
insert into sale values(3, 2, 300, '2016-09-03'); 
insert into sale values(4, 2, 400, '2016-10-09'); 
insert into sale values(5, 4, 500, '2016-11-22'); 
insert into sale values(6, 4, 600, '2016-12-02');