Columnas que se auto-incrementan en PostgreSQL

PostgreSQL

MySQL tiene la posibilidad de crear columnas que se auto-incrementan en la misma definición de la columna a travé del comando AUTO_INCREMENT, no así PostgreSQL. En Postgres es más complicado, y de esta manera lo hago yo.

1. Crear secuencia

Con pgAdmin III

pgAdmin3 es una aplicación multi-plataforma que sirve para administrar las bases de datos PostgreSQL hecha por la misma comunidad PostgreSQL. Si l quieres hacer por líneas de comandos, avanza un poco más en este artículo.

Una vez abierto el pgAdmnin3 y hecha la conexión:

  1. Expando mi base datos, mi Esquema, elijo con el botón secundario del mouse la sección Secuencias y elijo en el menú contextual la opción Nueva Secuencia…

    Menú contextual sobre las Secuencias de una base de datos PotgreSQL en pgAdmin3.

  2. En el formulario le pongo un nombre (en mi caso se llamará transferencias_id), el usuario que hará uso de esta secuencia (en mi caso, el usurio se llama ooscarr) y OK. También puedo elegir si quier que se auto-incremente a partir de un número diferente al 1 y/o si quiero que el número vaya aumentando de 2 en 2, 3 en 3, etc. en vez de 1 en 1 como se pone por defecto.

    Diálogo de secuencia nueva en pgAdminIII. Si sólo se llenan los campos Nombre y Propietario, PostgreSQL los llenará automáticamente con incremento de 1 en 1, desde el número 1, y le asignará una OID, etc, etc.

Lo mismo, desde la línea de comandos

si seleccionas la nueva secuencia que se agrega a la lista puedes ver el comando SQL que se utilizó para crearla. Lo mismo se pudo haber hecho desde la línea de comandos psql con:

CREATE SEQUENCE transferencias_id;

ó

CREATE SEQUENCE transferencias_id
  INCREMENT 1
  MINVALUE 1
  START 1
  CACHE 1;
ALTER TABLE transferencias_id OWNER TO ooscarr;

transferencias_id es el nombre que le asigné a la secuencia y ooscarr es el usuario de PostgreSQL que es dueño de esta variable auto-incrementable.

2. Usar la secuencia en una columna

Con pgAdmin3

  1. Expandemos la tabla que queremos modificar, expandemos Columnas, y seleccionamos la columna de la tabla que se deseas que se auto-incremente con esta variable, vas a las Propiedades….
  2. En el inspector de columna, le asignamos el Valor por defecto NEXTVAL('transferencias_id'). Siendo transferencias_id el nombre de la secuencia.

    Diálogo de Propiedades de columna en pgAdminIII, con el campo Valor por defecto NEXTVAL('transferencias_id').

  3. OK

Con comandos (psql)

Sería…

ALTER TABLE transferencias 
    ALTER COLUMN id 
        SET DEFAULT NEXTVAL('transferencias_id');

transferencias_id es el nombre de la secuencia, id es el nombre de la columna a asignarle la secuencia y transferencias es el nombre de mi tabla donde está la columna con al secuencia.

Actualizar filas anteriores sin la secuencia

Con comandos

Para cambiar los valores null de las filas creadas antes de la asignación de la variable auto-incrementable, consultamos:

UPDATE transferencias 
    SET id = NEXTVAL('transferencias_id');

Ese paso final es una de las cosas más lentas que puedes pedirle a Postgres que haga posiblemente. Para una tabla de tamaño mediano (alrededor de 5,000,000 tuplas, con unas cuantas columnas de pequeños números y textos), eso tomó cerca de 2.5 horas en hardware poderoso – así que querrás dejar esto por un tiempo tranquilo. Afortunadamente Postgres interpreta el UPDATE como una transacción atómica: nada se realiza hasta que el comando se complete, así será difícil que dejes los datos en un estado inconsistente.

Y ya con eso, cada vez que crees una nueva fila en esa tabla, esa columna se llenará automáticamente con un número distinto automáticamente.

Referencias

Artículos relacionados

Conectar a la base de datos desde JSP

Netbeans

Con JSTL, conectarse a una base de datos es sencillo y no requiere todo ese peligroso código Java que antes producía dolores de cabeza y frustración.

Preparación

ACTUALIZACIÓN: Netbeans 6.5 agregó al asistente para crear nuevos proyectos web, un formulario extra para ingresar los drivers y los datos de la base de datos desde el principio.

Luego de crear el proyecto JSP y antes de conectar a la base de datos, hay que instalar las librerías Java para la base de datos.

  1. Vamos a las propiedades del proyecto, en la sección Librerías presionamos el botón Add Library… (Agregar librería…)

    Panel de propiedades del proyecto en Netbeans

  2. Importar

    Importar librería en Netbeans

  3. Seleccionamos la librería de PostgreSQL (o Mysql, según…)

    PostgreSQL JDBC Driver
    Aprovechar de agregar la JSTL si no ha sido incluida.

  4. Add Library (Agregar Librería)

    Add Library

  5. OK

Conexión

Luego creamos un JSPF para la conexión

  1. Nuevo archivo > JSP

  2. Le ponemos un nombre…

    Nuevo archivo JSP(F) en Netbeans
    Nos aseguramos que el archivo quede en la carpeta WEB-INF/jspf

El árbol de archivos del proyecto indicando Web Pages > WEB-INF > jspf > conexion.jspf

conexion.jspf

El código del archivo conexion.jspf contendría sólo y nada más que:

<sql:setDataSource driver="org.postgresql.Driver"
 url="jdbc:postgresql://locahost:5432/usuario"
 user="usuario"
  password="contraseña"
 var="bdatos" />

* Si se utiliza Mysql, cambiar parte de la segunda línea por jdbc:mysql://
** Si el servidor se encuentra en otra máquina, cambiar la parte locahost:5432 por la IP y el puerto correspondiente.

La conexión se guarda en una variable var="bdatos" para poder ser usada después en el código.

Código

Ya en el código JSP, primero agregamos el tag sql

<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>

Y después llamamos a la parte de la conexión como siempre con:

<%@include file="/WEB-INF/jspf/conexion.jspf" %>

Por ejemplo, el código completo de una página con una consulta simple sería:

index.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  <title>Página JSP</title>
 </head>
 <body>
  <h1>¡Hola, mundo!</h1>

<%@include file="/WEB-INF/jspf/conexion.jspf" %>
<sql:transaction dataSource="${bdatos}">

 <c:catch var="ex">
  <sql:query var="encontrados">
   SELECT primer_nombre FROM persona LIMIT 4
  </sql:query>
 </c:catch>


 <c:choose>
  <c:when test="${not empty ex}">
   <p>Problema: <c:out value="${ex.message}"/></p>
  </c:when>
  <%-- Si no hubo ninguna excepción --%>
  <c:otherwise>


   <c:choose>

    <%-- Si encontró algo --%>
    <c:when test="${encontrados.rowCount>0}">
     <ol>
      <c:forEach items="${encontrados.rows}" var="persona">
       <li><c:out value="${persona.primer_nombre}" /></li>
      </c:forEach>
     </ol>


    </c:when>
    <c:otherwise>
     <p>No se encontraron datos.</p>
    </c:otherwise>
   </c:choose>

  </c:otherwise>
 </c:choose>
</sql:transaction>
 </body>
</html>

Ejecución

ver index.jsp.

Referencias

Artículos relacionados

¿Respaldemos la base de datos?

PostgreSQL

Mi nombre es Oscar, y dentro de una hora nos van a borrar/formatear las cuentas que usamos para trabajar en mi Universidad. Y si bien respaldé mis archivos mediante FTP, todavía me queda información y trabajo almacenado dentro de las bases de datos que están en los servidores. Lo mismo podría ser útil si uno se va a cambiar de empresa de hosting (si es que hubiera alguna que ofrezca PostgreSQL en sus planes más económicos). Éste es un tutorial for dummies™.

Conectando al servidor ACInfo (acinfo.unap.cl:5432)

PgAdmin III

Con las últimas versiones del software PgAdmin III, respaldar una base de datos es muy sencillo. Si nuestra base de datos no es muy complicada en cuanto a usuarios, grupos y esquemas; o su volumen no es demasiado grande (por ejemplo, 6 millones de datos).

El respaldado

Para respaldar con el programa, como primer paso lo único que hay que hacer es hacer click con el botón derecho sobre algún objeto de tu dominio y elegir la opción Resguardo… del menú contextual.

También accesible desde el menú “Herramientas” > “Resguardo...”

Luego aparecerá una cuadro de diálogo con distintas opciones dependiendo de dónde vas a hacer la restauración.

Si no tienes permiso de super usuario en el servidor destino (donde vas a hacer la restauración), debes elegir la opción PLAIN:

Imagen del cuadro de diálogo “Resguardar”
En el caso del tipo PLAIN, lo recomendable es que el archivo termine con la extensión .sql
En Windows, es necesario escribir la ruta completa del nombre de archivo a mano (C:...)

Este tipo de respaldos no permite blobs; para eso, se utilizan los otros formatos y se hace mediante líneas de comandos.

Si tienes permiso de super usuario en el servidor destino lo recomendado es utilizar cualquiera de las opciones COMPRESS o TAR por igual.

Imagen del cuadro de diálogo Resguardar
En el caso del tipo COMPRESS o TAR, lo recomendable es que el archivo termine con la extensión .backup

Luego de eso, es cosa de esperar. Según el sistema operativo, puede o no quedarse el computador paralizado hasta que termine. Ten paciencia o cambia de pantalla con las teclas control + tab (o command + tab en los especiales teclados mac).

Por mientras te cuento que si tienes los puertos bloqueados y no puedes conectarte con pgAdmin III, también existe un sistema para manejar la base de datos desde la web (como phpmyadmin de MySQL) llamado phpPgAdmin, que permiten exportar los datos además de los formatos SQL y pg_dump, en los formatos método COPY (binario) , XML, XHTML, CSV o datos separados por tabs y saltos de líneas. Opciones también disponibles desde las líneas de comandos.

/Applications/pgAdmin3.app/Contents/SharedSupport/pg_dump -i -h acinfo.unap.cl -p 5432 -U ooscarr -F p -O -D -v -f "/Users/ooscarr/Desktop/respaldo.sql" -n 'public' ooscarr

El proceso retornó el código de salida 0.

Para una mejor descripción de cada una de las opciones de exportación con el comando pg_dump (en inglés), visita la documentación oficial de PostgreSQL

La restauración

Hay dos formas de hacer la restauración con el programa pgAdmin III, y dependerá del formato del archivo de respaldo o los permisos de usuario que tengas en el servidor donde deseas recuperar la información.

Con el archivo .sql

SQL

Si no tienes permiso de super usuario e hiciste el respaldo en modo PLAIN, lo único que hay que hacer es ir al editor SQL (Herramientas > Herramienta para Consultas), abrir el archivo desde ahí y presionar el botón Play (Consulta > Ejecutar). Hecho.

Con el archivo .backup

Si tenemos un archivo .backup, necesitaremos los permisos necesarios en la base de datos para seleccionarla con el botón derecho y elegir la opción Restaurar…

También accesible desde el menú “Herramientas” > “Restaurar...”

Cuadro de opciones de restauración

Y con eso estaría respaldado y restaurado todo. 🙂 Por supuesto que podrían haber riesgos y complicaciones y habría que entrar a picar por el SSH, pero para qué nos vamos a poner tan negativos. Yo asumo que las bases de datos destino estaban vacías y la configuración de las tablas no era tan especial.

Fin

Ya tengo mis archivos respaldados, mi base de datos está respaldada… Existen otros métodos para replicar los datos sin necesidad de crear un archivo sino que directamente y otras formas de respaldo automático (Slony-I). Ahora me iré a ver cómo respaldar mi base de datos MySQL, nos vemos.

Referencias