Seguim el manual de la versió 8.3

http://www.postgresql.org/docs/8.3/static/
http://www.postgresql.org/docs/manuals/ (llista de manuals, et pots descarregar el pdf)

Contingut

1 Què és PostgreSQL
2 Instal.lació
3 Crear i accedir a una base de dades
4 El llenguatge SQL
- 4.1 Repàs de SQL
  - 4.1.1 exemple funcions de dates (extract)
5 Aplicacions client de PostgreSQL

Què és PostgreSQL

PostgreSQL és un sistema gestor de bases de dades relacionals i d'objectes (SGBDRO, ORDBMS) basat en POSTGRES, Version 4.2, desenvolupat per la Universitat de Califòrnia en el Berkeley Computer Science Department. POSTGRES va ser pionera de molts conceptes que només van ser possibles en bases de dades comercials molt després.

PostgreSQL és de codi lliuire i descendeix directament del codi original de Berkeley. Suporta la major part del SQL estàndard i ofereix moltes característiques modernes:

queries complexes
foreign keys, claus forànies
triggers (disparadors)
vistas
integritat transaccional
control de concurrència (quan moltes persones ataquen a la base de dades alhora)

PostgreSQL es pot extendre de múltiples maneres. Per exemple, afegint:

tipus de dades
funcions
operadors
funcions d'agregació
mètodes d'indexació
llenguatges procedimentals

And because of the liberal license, PostgreSQL can be used, modified, and distributed by everyone free of charge for any purpose, be it private, commercial, or academic.

Instal.lació

A http://www.postgresql.org/download/ et pots baixar els binaris per a Linux i Windows. La instal.lació és realment fàcil.

Per a Ubuntu: Instal.lació_i_configuració_de_Postgres

Després de la instal.lació del servidor PostgreSQL, a Linux tindràs el dimoni del servidor:

$ sudo /etc/init.d/postgresql-8.3 stop | start | restart

i a Windows tindràs un servei dins de Herramientas Administrativas > Servicios.

El dimoni o el servei han d'estar engegats. En la instal.lació a més s'instal.len clients com ara el psql (línia de comandes, CLI), i a Windows sí que trobaràs un entorn gràfic.

Crear i accedir a una base de dades

El servidor PostgreSQL pot gestionar moltes bases de dades. Per crear una base de dades:

$ createdb mydb

Ara bé, això normalment no funcionarà per varis motius:

el PATH no sap on està createdb

$ /usr/local/pgsql/bin/createdb mydb

Pot ser que el servidor no estigui encès:

$  sudo /etc/init.d/postgresql-8.3 start

o és molt comú que dongui l'error:

createdb: could not connect to database postgres: FATAL:  role "joe" does not exist

on joe és el teu login del sistema. Això passa sempre que l'administrador no ha creat un usuari PostgreSQL específic per a l'usuari del sistema (els comptes d'usuari de PostgreSQL són diferents dels del sistema operatiu). En el procés de la instal.lació es crea un usuari de sistema que és el postgres. Loga't com a postgres (su postgres), i amb aquest usuari pots crear el teu primer compte d'usuari de la base de dades: normalment postgres. Ara bé, no confonguem: una cosa és l'usuari del SO, i una altra cosa l'usuari de la BD.

joan$ sudo bash
root$ su postgres
postgres$ psql
postgres => \q
postgres$

Hem de canviar a l'usuari postgres passant per root per que encara no té password. Si li posem password ja no caldrà:

$ sudo passwd postgres
joan$ sudo su postgres
postgres$

Per tant, per connectar-nos al PostgreSQL des de l'usuari del sistema joan utilitzant l'usuri del PostgreSQL postgres, utilitzaré l'opció -U o definiré la variable d'entorn PGUSER:

$ psql -h localhost -p 5432 -U postgres -d postgres
postgres#

i ara la base de dades es pot crear com una comanda SQL:

postgres# CREATE DATABASE gugel encoding='LATIN1';

creació d'un usuari

podem crear un usuari de la base de dades amb la comanda createuser (comanda client del PostgreSQL, no confondre amb adduser, o bé amb la sentència SQL CREATE USER. Són maneres equivalents.

joan$ sudo bash
root$ su postgres
postgres$ createuser pere
Shall the new role be a superuser? (y/n) y
CREATE ROLE
$ psql -h localhost -p 5432 -U postgres -d postgres
postgres# ALTER USER pere WITH ENCRYPTED PASSWORD 'pere';
ALTER ROLE

o bé

$ psql -h localhost -p 5432 -U postgres -d postgres
postgres=> CREATE USER maria;
ROLE CREATED
postgres=> ALTER USER maria WITH ENCRYPTED PASSWORD 'maria';
ALTER ROLE

accedir a la base de dades

$ psql
postgres#

psql vol connectar-se al servidor local, amb l'usuari postgres i a la bd postgres. És l'opció per defecte. Ara bé, de forma més general:

$ psql -h localhost -p 5432 -U pere -d postgres
postgres#

$ psql -h localhost -p 5432 -U abanq -d abanq
abanq#

Per començar a treballar necessito saber les comandes bàsiques:

postgres#
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

postgres=# select version();
                                              version                                              
---------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.8 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.3-5ubuntu4) 4.3.3

Podem utilitzar el PostgreSQL com una calculadora:

postgres# SELECT 2 + 2;
 ?column?
----------
        4

i per executar un script que conté comandes SQL:

postgres# \i basics.sql

accedir des de la xarxa

Configurar els arxius pg_hba.conf i postgresql.conf. Els fitxers es troben dins de /etc/postgresql/8.3/main

postgresql.conf

#port = 5432
listen_addresses = '*'

pg_hba.conf

# All IPv4 connections from localhost
host all all 0.0.0.0 0.0.0.0 password

i reiniciem el servei:

$ sudo /etc/init.d/postgresql-8.3 restart

i ja podem accedir des de la xarxa. Des de l'equip remot accedim a la bd del servidor posant la IP del servidor:

$ psql -h 192.168.1.10 postgres
postgres#

Accedir a la base de dades amb un usuari que no és el propietari

# GRANT ALL PRIVILEGES ON DATABASE abanq TO pere;

Evidentment, per acceidr no cal donar tots els privilegis, sinó que als usuaris se'ls ha de donar els privilegis per fer només allò que els és permès fer.

El llenguatge SQL

creem dues taules:

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

CREATE TABLE cities (
    name            varchar(80),
    location        point
);

i afegim files, amb vàries possibilitats:

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);

i ja podem fer consultes:

SELECT * FROM weather;

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

     city      | temp_avg |    date
---------------+----------+------------
 San Francisco |       48 | 1994-11-27
 San Francisco |       50 | 1994-11-29
 Hayward       |       45 | 1994-11-29
(3 rows)

SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;
SELECT * FROM weather ORDER BY city;
SELECT * FROM weather ORDER BY city, temp_lo;

Podem ajuntar les dues taules fent un JOIN:

SELECT * FROM weather, cities WHERE city = name;

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)

Obtenim el mateix resultat fent un JOIN explícit:

SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);

Hi ha altres tipus de joins (LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN):

SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)

Funcions d'agregació:

SELECT max(temp_lo) FROM weather;
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
SELECT city, max(temp_lo) FROM weather GROUP BY city;
SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;
SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%' GROUP BY city HAVING max(temp_lo) < 40;

updates i deletes:

UPDATE weather SET temp_hi = temp_hi-2,  temp_lo = temp_lo-2 WHERE date > '1994-11-28';
DELETE FROM weather WHERE city = 'Hayward';

Creació de vistes:

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;

SELECT * FROM myview;

Claus forànies:

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

i la següent instrucció dóna un error de clau forània, doncs la ciutat Berkeley encara no existeix:

INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');

ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(Berkeley) is not present in table "cities".

Repàs de SQL

exemple funcions de dates (extract)

CREATE TABLE punts (dia timestamp, valor integer);

INSERT INTO punts VALUES ('01/01/2012',10);
INSERT INTO punts VALUES ('05/01/2012',12);
INSERT INTO punts VALUES ('15/01/2012',8);
INSERT INTO punts VALUES ('01/02/2012',10);
INSERT INTO punts VALUES ('12/02/2012',15);
INSERT INTO punts VALUES ('14/02/2012',16);
INSERT INTO punts VALUES ('22/02/2012',17);
INSERT INTO punts VALUES ('01/03/2012',18);
INSERT INTO punts VALUES ('24/03/2012',4);
INSERT INTO punts VALUES ('26/03/2012',8);

SELECT * FROM punts;

SELECT EXTRACT(MONTH FROM dia), EXTRACT(YEAR FROM dia), SUM(valor) FROM punts GROUP BY EXTRACT(MONTH FROM dia), EXTRACT(YEAR FROM dia);

Aplicacions client de PostgreSQL

Podríem dividir les aplicacions en tres tipus: CLI (línia de comandes), aplicacions d'excriptori, i aplicacions web.

psql: CLI (command line interface). S'instal.la per defecte, i és la forma natural de relacionar-se amb la base de dades. Necessari per als administradors que volen administrar la base de dades amb scripts.
pgAdmin. Proporciona una interfície gràfica, útil per a administrar. http://www.pgadmin.org (Linux, Windows). Ubuntu: sudo apt-get install pgadmin3; Windows: és l'aplicació gràfica client que s'instal.la per defecte.
phpPgAdmin (entorn web). http://phppgadmin.sourceforge.net/
Squirell (Java, multiplataforma). http://squirrel-sql.sourceforge.net/. Necessites els drivers JDBC
Tora (Linux). Open Source, http://torasql.com/, suport nadiu per a PostgreSQL i MySQL.
Toad (Windows).www.toadsoft.com/

Hi ha més exemples d'aplicacions i possibilitats a http://www.postgresql.org/download/products/1

creat per Joan Quintana Compte, novembre 2009

UD6 DAI-C8-EC. PostgreSQL: Instal.lació i configuració. Consola

Contingut

Què és PostgreSQL

Instal.lació

Crear i accedir a una base de dades

creació d'un usuari

accedir a la base de dades

accedir des de la xarxa

Accedir a la base de dades amb un usuari que no és el propietari

El llenguatge SQL

Repàs de SQL

exemple funcions de dates (extract)

Aplicacions client de PostgreSQL

Menú de navegació

Eines de l'usuari

Espais de noms

Variants

Vistes

Més

Cerca

Navegació

Institut Jaume Balmes

Màquines recreatives

CNC

Informàtica musical

joanillo.org Planet

Eines