Importar datos externos a nuestra base de datos PostgreSQL mediante COPY

Algo muy importante en nuestra base de datos, es sin duda eso, los datos, ¿que hariamos sin ellos?, ¿que proposito tendria entonces tener una base de datos?

Muy bien, nuestra base de datos ya esta creada, tenemos un esquema relacional perfecto y tenemos normalizadas las tablas, ahora necesitamos poblar dichas tablas, pero no escribirlas de una en una.

Supongamos que la información la conseguimos en formato digital o es información almacenada en un archivo de hoja de calculo o algo por el estilo.

Aqui necesitamos primero que nada un formato estandar para poder mover la información de un formato propietario (por ejemplo excel, xls) a nuestras tablas de la base de datos, en este caso las tablas creadas previamente en postgresql.

Ese formato estandar se llama "CSV" o valores separados por comas.

Veamos un ejemplo de un archivo en excel que contiene algunos de los codigos postales de la Ciudad de México.

Archivo: CodigosPostalesMexicanos.xls



Pues bien, este archivo de excel lo queremos insertar dentro de nuestra base de datos ahora el problema es como lo hacemos. Lo bueno es que la solución es muy facil, simplemente lo guardamos como archivo de texto separado por comas o CSV y a continuación lo abrimos con un editor de texto y eliminamos todo lo que no tenga que ver con la información que vamos a importar, como por ejemplo encabezados de columna, etc.

Archivo: codigos_postales.csv

"d_codigo","d_asenta","d_tipo_asenta","D_mnpio","d_estado"
"01000","San Angel","Colonia","Alvaro Obregon","Distrito Federal"
"01010","Los Alpes","Colonia","Alvaro Obregon","Distrito Federal"
"01020","Guadalupe Inn","Colonia","Alvaro Obregon","Distrito Federal"
"01028","Secretaria de Contraloria y Desarrollo Administrativo","Gran usuario","Alvaro Obregon","Distrito Federal"
"01029","Infonavit","Gran usuario","Alvaro Obregon","Distrito Federal"
"01030","Axotla","Pueblo","Alvaro Obregon","Distrito Federal"
"01030","Florida","Colonia","Alvaro Obregon","Distrito Federal"
"01040","Campestre","Colonia","Alvaro Obregon","Distrito Federal"
"01048","Las Aguilas","Unidad habitacional","Alvaro Obregon","Distrito Federal"

Si notan la primera linea contiene el encabezado que aparecia en excel, es muy importante eliminar esa o las lineas que no sean necesarias para evitar errores a la hora de importarlas, y quedaria asi:

"01000","San Angel","Colonia","Alvaro Obregon","Distrito Federal"
"01010","Los Alpes","Colonia","Alvaro Obregon","Distrito Federal"
"01020","Guadalupe Inn","Colonia","Alvaro Obregon","Distrito Federal"
"01028","Secretaria de Contraloria y Desarrollo Administrativo","Gran usuario","Alvaro Obregon","Distrito Federal"
"01029","Infonavit","Gran usuario","Alvaro Obregon","Distrito Federal"
"01030","Axotla","Pueblo","Alvaro Obregon","Distrito Federal"
"01030","Florida","Colonia","Alvaro Obregon","Distrito Federal"
"01040","Campestre","Colonia","Alvaro Obregon","Distrito Federal"
"01048","Las Aguilas","Unidad habitacional","Alvaro Obregon","Distrito Federal"

Ya hecho lo anterior lo guardamos y procedemos a crear la tabla en nuestra base de datos postgresql como se muestra a continuación:

\d codigos_postales
          Tabla «public.codigos_postales»
   Columna      |          Tipo          | Modificadores
-------------------+------------------------+---------------
cp                | character(5)           |
asentamiento      | character varying(255) |
tipo_asentamiento | character varying(120) |
municipio         | character varying(120) |
estado            | character varying(120) |

La forma de crear el esquema anterior es muy simple ya que utiliza SQL estandar y en postgres quedaria como sigue:


CREATE TABLE codigos_postales (
cp char(5),
asentamiento varchar(120),
tipo_asentamiento varchar(120),
municipio varchar(120),
estado varchar(120)
);


Ya lo unico que nos falta es poblar dicha tabla con el comando COPY y CSV. Pues manos a la obra, y queda asi:

COPY codigos_postales FROM '/tmp/codigos_postales.csv' DELIMITERS ',' CSV;

Recordemos que el comando COPY lo escribimos dentro de psql y que el propietario del archivo codigos_postales debe de poder ser leido por el usuario postgres en cuestion, en unix con un simple chmod 644 /tmp/codigos_postales.csv quedaria para poder ser leido por el postmaster de postgresql. Otra cosa importante a notar es que el unico que tiene permiso para hacer esta importación es el usuario postgres y debe de tener tambien permiso de lectura sobre el directorio en cuestion, en este caso /tmp. En windows la ruta del archivo es sustituida por la ruta correspondiente en Windows, por ejemplo "c:\temp\codigos_postales.csv".

Esta forma de importación es muy importante sobre todo cuando queremos migrar datos entre distintas plataformas o bases de datos que no nos ofrezcan alguna herramienta de migración mediante acceso a datos como ADO, ODBC, etc. O si lo que queremos es importar datos de archivos estadisticos o de excel.

Si ahora lo que queremos es crearle una llave primaria a la tabla codigos postales, lo que podemos hacer es jugar un poco con los datos de la siguiente forma:

ALTER TABLE codigos_postales add column id serial;

Con esto logramos crear una columna nueva de nombre "id" y es de tipo serial que ademas de ser un tipo entero tambien crea una secuencia implicita para poder incrementar el id de cada registro de una forma facil y segura.

ahora necesitamos poblar ese campo id si es que postgresql no le asigno ya un numero, seria de la siguiente forma: UPDATE codigos_postales SET id = nextval('codigos_postales_id_seq');

Ya nadamas nos falta aplicar la restriccion de llave primaria y eso lo logramos con: ALTER TABLE codigos_postales ADD PRIMARY KEY (id);

Y con esto terminamos nuestro ejemplo de importación mediante CSV con el comando COPY.