Apuntes de Postgresql

PostgreSQL (para los amigos postgres), es un potente y libre motor de base de datos relacional, con un uso que crece cada día, apoyado por una gran comunidad de usuarios, desarrolladores, empresas, centros de estudios, etc. hoy en día, postgres, es una excelente solución para nuestros “datos”.

A continuación un par de apuntes (útiles o no xD) sobre PostgreSQL.

Instalación de PostgreSQL en GNU/Linux Debian
Podemos instalar PostgreSQL en GNU/Linux Debian (o distribuciones basadas en debian) de una forma muy sencilla gracias a nuestro gestor de paquetes apt (o con otros gestores tipo yum, etc. en otras distribuciones).

apt-get update
apt-get install postgresql

Procedemos a crear el directorio del cluster

su
mkdir -p /var/pgsql/data
chown postgres:postgres /var/pgsql/data

Finalmente, solo nos falta crear el cluster para nuestra instancia de Postgresql

su - postgres
/usr/lib/postgresql/8.3/bin/initdb -E utf8 -U postgres -D /var/pgsql/data/

(suponiendo que hemos instalado Postgresql 8.3)

Finalmente levantamos el demonio de Postgresql

/etc/init.d/postgresql restart

Crear una Base de Datos en Postgresql

Desde la consola del sistema

createdb mydb

Desde la consola psql o conexión sql

CREATE DATABASE mydb;

Borrar una Base de Datos
Desde consola del sistema

dropdb mydb

Desde consola psql o conexión sql

DROP DATABASE mydb;

Crear Usuario

adduser myuser -P

Borrar un Usuario

dropuser myuser -P

Copia de Seguridad (Respaldo) de una DB

# Dump
pg_dump mydb > mydb.sql
# Dump with sintax INSERT
pg_dump -D mydb > mydb.sql

Restauración de una DB

psql mydb < mydb.sql

Creación de Tablas

-- BIGSERIAL crea una sequencia autoincremental en la tabla
CREATE TABLE mytable
(
    id BIGSERIAL NOT NULL PRIMARY KEY ,
    name VARCHAR(128) NOT NULL ,
    phone VARCHAR(32) NULL ,
    regtime TIMESTAMP DEFAULT NOW() ,
);

Creacion de Tablas con llaves foreanas (foreing key)

CREATE TABLE students
(
    id BIGSERIAL NOT NULL PRIMARY KEY ,
    name VARCHAR(32) NOT NULL ,
    lastname VARCHAR(32) NOT NULL ,
    birthdate DATE DEFAULT NOW()
);

CREATE TABLE classes
(
    id BIGSERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(32)
);

CREATE TABLE classes_inscription
(
    id BIGSERIAL NOT NULL PRIMARY KEY,
    class_ins INTEGER NOT NULL ,
    student_ins INTEGER NOT NULL ,
    date_ins TIMESTAMP DEFAULT NOW()
);

-- Index
CREATE INDEX idx_classes_inscription ON classes_inscription( class_ins , student_ins );
ALTER TABLE classes ADD UNIQUE (id) ;
ALTER TABLE students ADD UNIQUE (id) ;
-- Key Foreign
ALTER TABLE classes_inscription ADD FOREIGN KEY (class_ins) REFERENCES classes(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE classes_inscription ADD FOREIGN KEY (student_ins) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE;
-- Insert Data
INSERT INTO students (name,lastname,birthdate) VALUES ('Juan','Lopez','1985-01-01');
INSERT INTO students (name,lastname,birthdate) VALUES ('Pedro','Carcamo','1986-01-01');
INSERT INTO students (name,lastname,birthdate) VALUES ('Pablo','Marmol','1987-01-01');
INSERT INTO classes (name) VALUES ( 'Calculo I' );
INSERT INTO classes (name) VALUES ( 'Calculo II' );
INSERT INTO classes (name) VALUES ( 'Calculo III' );
INSERT INTO classes (name) VALUES ( 'Algoritmos y Estructura de Datos');

INSERT INTO classes_inscription (class_ins,student_ins) VALUES(1,1);
INSERT INTO classes_inscription (class_ins,student_ins) VALUES(1,2);
INSERT INTO classes_inscription (class_ins,student_ins) VALUES(2,1);
INSERT INTO classes_inscription (class_ins,student_ins) VALUES(2,2);
INSERT INTO classes_inscription (class_ins,student_ins) VALUES(2,3);
INSERT INTO classes_inscription (class_ins,student_ins) VALUES(3,1);
INSERT INTO classes_inscription (class_ins,student_ins) VALUES(3,2);

-- Selects
SELECT * FROM students;
SELECT * FROM students, classes, classes_inscription WHERE  students.id = classes_inscription.student_ins and classes.id = classes_inscription.class_ins;

Creación de una Vista

-- Inscripciones por estudiante
CREATE VIEW  inscriptions AS SELECT students.id,  students.name || ' ' || students.lastname as student , count(classes_inscription.id) as inscriptions FROM students, classes, classes_inscription WHERE  students.id = classes_inscription.student_ins and classes.id = classes_inscription.class_ins GROUP BY  students.id, students.name, students.lastname;
-- Consulta
SELECT * FROM inscriptions ;

Espacio ocupado de las base de datos

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;

Espacio ocupado de una base de datos

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database where datname = 'mydb';

Cambiar el dueño (owner) de una tabla

ALTER TABLE mytable OWNER TO myuser

Aumentar una Secuencia

SELECT pg_catalog.SETVAL( 'mytable_id_seq' , 1000 , false );

Full Text Search en Postgresql
Desde la versión 8.3, PostgreSQL implementa nativamente Full Text Search

-- Creación del diccionario
CREATE TEXT SEARCH DICTIONARY db_es ( template = snowball, LANGUAGE = spanish, stopwords = spanish ); CREATE TEXT SEARCH CONFIGURATION public.db_es ( COPY = pg_catalog.spanish );
-- Query de ejemplo (Ordena según ranking (la tabla conceptos) buscando los string "efectos", "lupa" o "web" )
SELECT
	id______con ,
	titulo__con ,
	rank
FROM
(
	SELECT
		id______con ,
		titulo__con ,
		(rank_c + rank_t) AS rank
	FROM
	(
		SELECT
			id______con ,
			titulo__con ,
			ts_rank_cd(to_tsvector('db_es', cuerpo__con),
			to_tsquery('db_es','efectos|lupa|web')) AS rank_c ,
			ts_rank_cd(to_tsvector('db_es', titulo__con),
			to_tsquery('db_es','efectos|lupa|web')) AS rank_t
		FROM
			concepto
	) AS busqueda_concepto

	ORDER BY rank DESC
) AS busqueda_concepto_rank
WHERE
	rank > 0
GROUP BY
	id______con ,
	titulo__con ,
	rank;

Ejemplo de Trigger en Postgresql
El siguiente trigger actualiza el campo LASTUPDATAG si el campo LOCALLIZETAG ha cambiado (en la tabla tag)

CREATE OR REPLACE FUNCTION trg_update_tag_translation() RETURNS "trigger"
AS $$
BEGIN
IF OLD.LOCALLIZETAG <> NEW.LOCALLIZETAG THEN
	NEW.LASTUPDATAG := now();
END IF;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER trg_update_tag_translation
BEFORE UPDATE ON tag FOR EACH ROW EXECUTE PROCEDURE trg_update_tag_translation();

¿Como calcular la edad de una persona en base a un campo date?

Una forma muy sencilla es utilizar la función nativa age, considerando una tabla como la siguiente:

CREATE TABLE users
(
	id BIGSERIAL NOT NULL,
	name VARCHAR(128),
	birthdate DATE
);
INSERT INTO users (name,birthdate) VALUES ( 'Jose Ignacio', '2008-05-10' );

Consulta

select name, date_part('year',age(birthdate)) as edad FROM users;

Resultado
Jose Ignacio | 3

Import datos desde un archivo CSV

Considerando el módelo anterior (users), podemos importar los datos de un archivo CSV como el siguiente (/tmp/users.csv)


100,Pedro,1985-11-01
101,Ignacio,1988-05-28
102,Juan,1984-03-15


COPY users FROM e'/tmp/users.csv' DELIMITERS ',';

¿Como extraer año, mes día de un campo DATE, TIMESTAMP, etc…?

Utilizando la función date_part, podemos obtener partes de un fecha, ejemplos

select date_part('year', NOW()) as year, date_part('month', NOW()) as month, date_part('day', NOW()) as day;
-- Considerando el camo birthdate como DATE,TIMESTAMP, etc... de la tabla users
select date_part('year',  birthdate) as year FROM users ;

¿Como crear una función?
La siguiente función nos entrega la cantidad de campos que tiene una tabla.

CREATE OR REPLACE FUNCTION number_fields(text)
RETURNS integer AS
$BODY$
DECLARE
cfields integer;
BEGIN
SELECT relnatts INTO cfields FROM pg_class WHERE relname= $1 LIMIT 1;
RETURN cfields;

end;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

select number_fields('users');