Iniciándose en PostgreeSQL

Referencias:

• http://www.guia-ubuntu.org/index.php?title=PostgreSQL

Paquetes:

• Motor de Datos y Administrador: postgresql postgresql-client pgadmin3
  
• Soporte PHP y Administrador basado en php: phppgadmin php5-pgsql
• Visualizar Diagramas Fisicos: postgresql-autodoc dia-gnome

Configuración
1) La instalación creó un usuario del sistema postgres, al cual le cambié la contraseña a q1w2e3:
$ sudo passwd postgres

2) Accedemos a la consola pgsql:
$ sudo su postgres -c "psql template1"

3)Dentro de la consola:

template1=# ALTER USER postgres WITH PASSWORD 'q1w2e3;

Salida de los pasos 1) 2) 3):
ubuntu@ubuntu:~$ sudo passwd postgres
Introduzca la nueva contraseña de UNIX:
Vuelva a escribir la nueva contraseña de UNIX:
passwd: contraseña actualizada correctamente

ubuntu@ubuntu:~$ sudo su postgres -c "psql template1"
Bienvenido a psql 8.3.7, la terminal interactiva de PostgreSQL.

Teclee: \copyright para ver los términos de distribución
\h para ayuda sobre órdenes SQL
\? para ayuda sobre órdenes psql
\g o punto y coma («;») para ejecutar la consulta
\q para salir

template1=# ALTER USER postgres WITH PASSWORD 'q1w2e3';
ALTER ROLE
template1=# \q

ubuntu@ubuntu:~$

4) Modificar el archivo de configuración para permitir conecciones externas.

$ sudo gedit /etc/postgresql/8.2/main/postgresql.conf

buscar #listen_addresses = 'localhost' y cambiar por listen_addresses = '*'
buscar #password_encryption = on y cambiar por password_encryption = on

Problemas:
Problema 1: http://localhost/phppdamin por defecto no permite conectarse al usuario postgres, por motivos de seguridad, para permitirselo:
en ¿conf/config.inc.php? en este existe una variable llamada:
$conf['extra_login_security'] por defecto esta en true, si tratas de ingresar con el usuario postgres no te lo permitirá, ponlo en false.

Problema 2: Si ocupamos mayúsculas/minúsculas para el nombre de los campos en las tablas, forzamos a que en el insert se ocupe " (comilla doble) para el nombre del campo. Además postgres nos obliga a utilizar ' (comilla simple) en el valor del campo.

Quedando la consulta así: insert into planeta("idPlaneta","nombrePlaneta") values(4,'Mercurio');

Esto genera inconvenientes en la creación de Scripts en php, dado lo anterior se utilizará solo minúsculas en los nombres de los campos y de las tablas. Las consultas quedarán
insert into planeta(idplaneta,nombreplaneta) values(4,'Mercurio');

Problema 3: ¿Cómo hacer que una llave primaria se autoincremente? Primero es necesario crear una secuencia, luego colocar como valor por defecto de la llave el siguiente valor de la secuencia. ver: http://www.faqts.com/knowledge_base/view.phtml/aid/204/fid/16

Primero crear secuencia:

CREATE SEQUENCE secuenciaplaneta
INCREMENT 1
START 4;

Luego al crear la tabla:
CREATE TABLE planeta
(
idplaneta integer NOT NULL DEFAULT nextval('secuenciaplaneta'::regclass),
nombreplaneta character varying(20),
CONSTRAINT planeta_pkey PRIMARY KEY (idplaneta)
)
WITH (OIDS=FALSE);
ALTER TABLE planeta OWNER TO postgres;

Problema 4: Uno de nuestros problemas con mysql eran las fallas silentes y muchas veces nos quedabamos con integridad referencial rota. He aquí como forzar que no permita integridad referencial rota:

CREATE TABLE continente
(
idcontinente integer NOT NULL,
nombrecontinente character varying(20),
fkplaneta integer,
CONSTRAINT continente_pkey PRIMARY KEY (idcontinente),
CONSTRAINT continente_fkplaneta_fkey FOREIGN KEY (fkplaneta)
REFERENCES planeta (idplaneta) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (OIDS=FALSE);
ALTER TABLE continente OWNER TO postgres;


Problema 5: obtención de diagramas fisicos de las bases creadas.
Con ello podremos generar diagramas de la base de datos que posteriormente con dia podemos editar y visualizar.
$ postgresql_autodoc -d nombrebasededatos -h localhost -u usuario --password
pedira la clave del usuario y generara archivos .dia que nos servirán para trabajar.