==== Tworzenie bazy w PostgreSQL ====


Tworzenie użytkownika:

<code>
ali@ali:~$ sudo su
[sudo] password for ali:

root@ali:/home/ali# su - postgres

postgres@ali:~$ createuser ali
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
CREATE ROLE
<postgres@ali:~$ 

postgres@ali:~$ su - ali
Hasło: 
ali@ali:~$ 
</code>


Tworzenie bazy:

<code>
ali@ali:~$ psql -d template1
Welcome to psql 8.2.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=> create database pracownicy with encoding='latin-2';
CREATE DATABASE
template1=> \q

ali@ali:~$ cd DATABASES/PRAC/
ali@ali:~/DATABASES/PRAC$ psql -d pracownicy 
Welcome to psql 8.2.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

pracownicy=> 
</code>


Tworzenie tablic:

<code>
pracownicy=> \dt
No relations found.
pracownicy=> \i prac2.sql
SET
SET
psql:prac2.sql:8: ERROR:  table "prac" does not exist
psql:prac2.sql:9: ERROR:  table "dzial" does not exist
psql:prac2.sql:20: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dzial_pk" for table "dzial"
CREATE TABLE
psql:prac2.sql:35: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "prac_pk" for table "prac"
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
ALTER TABLE
pracownicy=> \dt
       List of relations
 Schema | Name  | Type  | Owner 
--------+-------+-------+-------
 public | dzial | table | ali
 public | prac  | table | ali
(2 rows)
</code>


Przeglądanie bazy:

<code>
pracownicy=> select * from prac;
 id_prac |  nazwisko  |   imie   |  data_ur   | dzial | stanowisko | pobory  
---------+------------+----------+------------+-------+------------+---------
 1100    | Kowal      | Adam     | 15/12/1989 | PD303 | robotnik   | 1500.00
 110     | Kowalik    | Artur    | 13/12/1998 | PD303 | kierownik  | 1500.00
 1110    | Kowalewski | Adam     | 15/11/1989 | PR202 | robotnik   | 3500.00
 101     | Kowalczyk  | Amadeusz | 17/12/1998 | PK101 | kierownik  | 1000.00
 1101    | Kowalski   | Antoni   | 15/12/1999 | PD303 | robotnik   | 4500.00
 1011    | Kowalowski | Alojzy   | 15/11/1998 | PK101 | robotnik   | 2500.00
 111     | Kowalczuk  | Adam     | 12/11/1998 | PR202 | kierownik  | 2500.00
 1010    | Kawula     | Alojzy   | 15/11/1998 | PK101 | robotnik   | 2500.00
(8 rows)

pracownicy=> select * from dzial;
 id_dzial |    nazwa    | lokalizacja | kierownik 
----------+-------------+-------------+-----------
 PD303    | Produkcyjny | Mysiecko    | 110  
 PK101    | Projektowy  | Mysieko     | 101  
 PR202    | Promocji    | Mysieoczko  | 111  
(3 rows)

pracownicy=> \l
        List of databases
    Name    |  Owner   | Encoding 
------------+----------+----------
 postgres   | postgres | UTF8
 pracownicy | ali      | LATIN2
 template0  | postgres | UTF8
 template1  | postgres | UTF8
(4 rows)

pracownicy=> \du
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of 
-----------+-----------+-------------+-----------+-------------+-----------
 ali       | no        | yes         | yes       | no limit    | 
 postgres  | yes       | yes         | yes       | no limit    | 
(2 rows)

pracownicy=> \q

ali@ali:~/DATABASES/PRAC$ 
</code>

Skrypt w prac2.sql do pobrania: {{:bazydanych:prac2.sql.tar.gz|}}