Mensaje inicial
La Mancha de la Calabaza que Ladra
mancha@galois.baras.ml.org
Mon, 28 Sep 1998 20:24:08 -0500
Hola, acabo de ver el mensaje de respuesta a la pregunta de Gilberto
Becerril y bueno, yo también ando empezando en esto del PostgreSQL y
las notas que he hecho las texí para poder usarlas el día que pierda
la memoria por completo.
Las anexo y espero que sean de utilidad a la comunidad y si alguien
tiene sugerencias o le hace añadiduras, agradeceré que manden copia de
regreso. Saludos.
(Para imprimirlo hace falta una instalación completa de TeX, como la
que viene con RedHat:
latex archivo.tex; latex archivo.tex; dvips archivo.dvi
)
==============================================================================
\documentclass{article}
\usepackage[spanish]{babel}
\usepackage{isolatin1}
\usepackage[dvips,spanish,all,light]{draftcopy}
\usepackage{algorithmic}
\usepackage{algorithm}
\setlength{\textheight}{9in}
\setlength{\textwidth}{6.5in}
\setlength{\topmargin}{-0.5in}
\setlength{\oddsidemargin}{0in}
\setlength{\evensidemargin}{0in}
\renewcommand{\listalgorithmname}{Lista de algoritmos}
\floatname{algorithm}{Algoritmo}
\def\fecha{\number\day\space de\space \ifcase\month\or
enero\or febrero\or marzo\or abril\or mayo\or junio\or
julio\or agosto\or septiembre\or octubre\or noviembre\or diciembre\fi
\space de \space\number\year}
\font\chirris=cmtt10 at 6pt
\title{Pequeño manual de algunas de las tripas de PostgreSQL\\ o de
cómo fui aprendiendo a lidiar con la bestia.}
\author{Por La Mancha de la Calabaza que Ladra\thanks{\tt mancha@breogan.iimas.unam.mx}}
\date{\fecha}
\makeindex
\begin{document}
\maketitle
\tableofcontents
\listofalgorithms
\section{Introducción}
¿A quién va dirigido? Digamos que está dirigido para el anónimo fulano
X que estudió matemáticas, comenzó a jugar con computadoras y acaba de
descubrir que hay un fulano Y con harta lana que está dispuesto a
desperdiciarla en la colección de CDs del fulano X, héroe de la
historia, a cambio de que éste le masajeé unas bases de datos.
Esto es algo así como un diario de las cosas que he ido aprendiendo de
las tripas de PostgreSQL. Estará escrito en desorden hasta que me
siente con calma a darle un poco más de estructura.
El meollo del asunto es saber usar el \verb+select+. Ese es tu mejor
amigo en las bases de datos relacionales con \verb+SQL+\footnote{Que,
por cierto, es un estándar para platicar con los bichos conocidos como
Bases de Datos. Si leiste esta nota al pie esperando una gran
explicación, es que vas mal... Si tan sólo la leiste porque siempre
lees las notas al pie, pues no andas tan perdido.}. De momento,
digamos que basta con tener un poco de sentido común para aprender de
que se trata el asunto, pero si crees que necesitas saber un poco más
de lo que describiré por aquí, la mejor referencia es \cite{Bowman} y
nunca, pero realmente \underline{nunca} \cite{Trimble}, que es con el
que aprendí.
\section{Si no estás en la compañía de un adulto...}
En la siguiente sección veremos como usar el cliente de la
base de datos, pero para esto es necesario tener unas bases de
datos. Aquellos que ya tienen acceso a una base de datos se pueden
pasar a la siguiente sección. Si no tienes, hay dos opciones: a) ir
con un niño mayor y que te cree una; b) aprender a hacerlo.
En primer lugar, para poder crear una base de datos o incluso
para hacer consultas en una existente, es necesario haber sido creado
como usuario del manejador. Esto lo hace el fulano que es conocido en
el medio como el DBA\footnote{En realidad son muchos fulanos y con
suerte hay uno cerca de tí.}: {\em Database Administrator}, el
administrador de las bases de datos. Es como el superusuario de UNIX,
pero no puede leer tu correo ni matar tus conexiones a sitios porno,
sólo crear usuarios y realizar las operaciones de administración de
las bases y el manejador\footnote{Hummmm... quizá de ahí venga el
nombre.}.
Como de seguro eres de aquella clase de personas que no se
quedan impasibles ante una figura de autoridad, ahí va el secreto: el
DBA correrá en el {\em shell\/} el programa \verb+createuser+ para
darte de alta. ¡Já! Ya no es un mago, no puede engañarte, pero aún así
no lo hagas enojar.
\verb+createuser+ es un programa que si no se le pasan
argumentos se pone en modo interactivo preguntando el nombre de la
cuenta del usuario ---que puede no ser la misma que en el sistema---,
el user ID ---que tiene que ser necesariamente la misma que en el
sistema---, si puede crear bases de datos y si puede añadir
usuarios. Pese a que el nombre del usuario es independiente del de el
sistema, si uno trata de asignar otro nombre al mismo usuario, no lo
permite\footnote{Este es un momento histórico, acabo de descubrir que
esto pasa.}.
En el siguiente ejemplo, tratamos de crear con otro nombre a
un usuario que ya existe. Luego del fracaso, creamos un usuario nuevo
con un nombre diferente al de su cuenta, y sin permiso para crear
bases de datos, así que seguirá dependiendo del DBA para que le cree
una. Por supuesto que tiene sentido que un usuario exista para el
manejador de la base de datos sin que pueda crear una bases de datos,
ya que con esto podrá consultar e incluso modificar o desgraciar una
existente si el DBA se descuida.
\begin{verbatim}
postgres@caserola:psql$ createuser %$
Enter name of user to add ---> lola
Enter user's postgres ID -> 500
createuser: 500 already belongs to mancha , pick another
Enter user's postgres ID -> 518
Is user ``lola''allowed to create databases (y/n) n
Is user ``lola''allowed to add users? (y/n) n
createuser: lola was successfully added
don't forget to create a database for lola
\end{verbatim}
Independientemente de si el DBA te dá permisos o no para crear
bases de datos\index{bases de datos|crear}, siempre es bueno saber
como se hace\footnote{Sobre todo por si un día el muy estúpido deja a
la mano una sesión abierta.}. \verb+createdb+, así se
hace. \verb+createdb nombre+ crea la base con nombre \verb+nombre+. No
hay más que decir.
\section{De los tipos de datos}
\subsection{Los que tiene}
En la tabla~\ref{tab:tipos}, robada vilmente del manual, con algunas
añadiduras se incluyen los tipos de datos que tiene construidos
internamente Postgres95 hasta la versión 6.2.3.
\begin{table}[t]
\begin{center}
\begin{tabular}{|l|l|p{8cm}|}
\hline\hline{\bf Postgres}&{\bf SQL92}&{\bf Descripción}\\\hline\hline
bool & boolean & Booleano, verdadero o falso. A la entrada puede
recibir los valores: (TRUE,'t','true','y','yes','1') que produce 't' o
(FALSE,'f','false','n','no','0') que produce 'f'\\\hline
box & N/A & Caja rectangular en un plano de dos dimensiones\\\hline
char($n$) & character($n$) & Cadena de $n$ carácteres de tamaño fijo\\\hline
circle & N/A & Círculo en un plano de dos dimensiones\\\hline
date & date & Fecha sin hora\\\hline
float4 & float($p$) & Número de punto flotante con precisión $p$\\\hline
float8 & real, double precision & Número de punto flotante con doble
precisión\\\hline
int2 & smallint & Entero de dos bytes con signo\\\hline
int4 & int, integer & Entero de cuatro bytes con signo\\\hline
int4 & decimal($p$, $s$) & Entero preciso para $p \le 9$, $s = 0$\\\hline
int4 & numeric($p$, $s$) & Entero preciso para $p = 9$, $s = 0$\\\hline
line & N/A & Línea infinita en un plano de dos dimensiones\\\hline
lseg & N/A & Segmento de línea en un plano de dos dimensiones\\\hline
money & decimal($9$, $2$) & Lana, marmaja, dinero en formato \$1,234.56\\\hline
path & N/A & Recorrido abierto o cerrado en un plano de dos dimensiones\\\hline
point & N/A & Punto en un plano de dos dimensiones\\\hline
polygon & N/A & Recorrido cerrado en un plano de dos dimensiones (un
polígono tal cual)\\\hline
time & time & Hora en formato $hh:mm:ss$ \\\hline
timespan & interval & Intervalo de tiempo de propósito general\\\hline
varchar($n$) & character varying($n$) & Cadena de carácteres con
tamaño máximo de $n$ carácteres\\\hline
\hline\hline
\end{tabular}
\caption{Descripción de tipos de datos de Postgres.}
\label{tab:tipos}
\end{center}
\end{table}
\section{Algunos trucos}
\subsection{Quiero copiar sólo unos campos de una tabla a otra...}
Supongamos que tenemos una tabla y queremos crear una segunda que
tenga sólo algunos de los campos que están en la primera, y además que
no me repita registros:
\begin{verbatim}
INSERT INTO nomfec SELECT DISTINCT nombre,apepat,apemat,fecnac FROM licencias;
\end{verbatim}
\subsection{Quiero borrar un determinado registro}
Una opción es dar un calificador que defina de manera única al
registro, pero cuando no se puede, está es la solución:
\begin{verbatim}
SELECT nombre,oid FROM nomfec;
DELETE FROM nomfec WHERE OID=1223744;
\end{verbatim}
\noindent dónde \verb+OID+ es el {\em Object ID\/} de cada registro, y
seleccionamos el de aquél que queremos borrar (o hacerle cualquier
otra cosa).
\subsection{Quiero tener un campo que sea un número secuencial automático}
Bien, la transa es esta: tenemos una tabla donde queremos que uno de
los campos tenga un número secuencial único y que además se actualiza
automáticamente. Fácil, creamos una secuencia\footnote{Sí, postgres
tiene una forma automática de hacerlo.}:
\begin{verbatim}
foo=> CREATE SEQUENCE misec INCREMENT 1 START 0 MINVAL 0 MAXVAL 999999;
\end{verbatim}
\noindent crea la secuencia {\tt misec} que comienza en cero, se
incrementa de uno en uno y tiene como valor máximo $999,999$. En
realidad, salvo por los valores mínimo y máximo, el resto es el
default. Ahora, cómo se usa. Lo que queremos es que cada vez que
inserto un registro, el valor de un campo sea un número secuencial que
se incrementa con respecto al último valor insertado, que pudo haber
sido usado hace meses. Esto se consigue así de fácil:
\begin{verbatim}
INSERT INTO mitabla VALUES ('un valorcillo textual', nextval('misec'))
\end{verbatim}
Si lo que queremos es saber el valor actual de la secuencia:
\verb+SELECT currval('misec')+
El empleo de estas bestias es una buena idea para el caso en que uno
quiera automatizar el uso de tablas auxiliares. Por ejemplo, digamos
que tenemos una tabla con $N$ registros y los valores que puede tomar
uno de los campos está en un dominio con un orden mucho menor a
$N$. De lo que estamos hablando es de normalizar la tabla. Bien, el
asunto es que para cada registro, vamos a tomar el campo en particular
que vamos a guardar en la tabla anexa y en su lugar almacenaremos un
identificador único para el valor que tiene esa instancia, el
algoritmo rápido y mal hecho se describe en~\ref{alg1}, mientras que
el algoritmo detallado para hacer la creación del identificador y la
inserción en la tabla anexa, se dá en el algoritmo~\ref{alg2}.
\begin{algorithm}
\caption{Para la substitución de valores por un identificador
obtenido de una tabla anexa.}
\label{alg1}
\begin{algorithmic}[1]
\WHILE{queden registros}
\STATE trae el siguiente registro
\IF{el valor existe en la tabla auxiliar}
\STATE asigna al campo el identificador asociado al valor
\ELSE
\STATE asocia al valor un identificador nuevo y único
\STATE inserta en la tabla auxiliar el valor
\ENDIF
\ENDWHILE
\end{algorithmic}
\end{algorithm}
\begin{algorithm}
\caption{Para la creación del identificador en la tabla anexa.}
\label{alg2}
\begin{algorithmic}[1]
\STATE SELECT idval FROM latabla WHERE campo = valor;
\IF{$ntuples > 0$}
\STATE return idval;
\ELSE
\STATE INSERT INTO $val$ VALUES ($valor$, NEXTVAL('secuencia'));
\STATE return CURRVAL('secuencia');
\ENDIF
\end{algorithmic}
\end{algorithm}
\section{La papa: cómo saber de las tripas en cuanto a bases, tablas,
campos y demás yerbas}
Para saber que bases de datos hay en el sistema:
\begin{verbatim}
SELECT * FROM pg_database;
\end{verbatim}
Para saber que tablas tengo en la base de datos actual:
\begin{verbatim}
SELECT * FROM pg_class;
\end{verbatim}
Lo mismo, pero solo las mias, excluyendo las del sistema:
\begin{verbatim}
SELECT * FROM pg_class WHERE relname !~~ 'pg%';
\end{verbatim}
Si sólo quiero saber cuantos registros tiene una tabla, basta con
preguntar:
\begin{verbatim}
SELECT relname,reltuples FROM pg_class WHERE relname='mechitos';
\end{verbatim}
\noindent donde {\tt mechitos} es el nombre de la tabla de la cual me
interesa saber el número de registros.
A continuación, la sopa de como extraer info de las tablas de la base
de datos. El asunto es el siguiente: \verb+pg_database+ tiene las
bases de datos, \verb+pg_class+ tiene las tablas, y
\verb+pg_attribute+ tiene los campos. Agarra uno y si lo que quiere
saber es el número de registros en una tabla determinada, pues basta
con preguntar lo siguiente, conociendo la tabla, con el plus de que
además se obtiene cuales son los campos. Para, además, saber los tipos
de los campos, habría que hacer un {\em query\/} a \verb+pg_type+ con
los \verb+oids+ de los campos, pero eso creo que ya es mucho rollo.
\begin{verbatim}
SELECT relname,reltuples,attname,attnum
FROM pg_class,pg_attribute
WHERE pg_class.relname='centel'
AND pg_attribute.attrelid=pg_class.oid
AND attnum > 0
ORDER BY attnum;
\end{verbatim}
Si lo quiero saber todo de todas las tablas de la base que no sean del
sistema, sino mias, por supuesto ordenadas por \verb+oid+, se hace lo
siguiente, pero con el problema de que incluye los indices:
\begin{verbatim}
SELECT pg_class.oid,relname,reltuples,attname
FROM pg_class,pg_attribute
WHERE pg_class.relname !~~ '%pg%'
AND pg_attribute.attrelid=pg_class.oid
AND attnum > 0
ORDER BY pg_class.oid;
\end{verbatim}
En el caso de que uno quiera saber cuales son los campos de
todas las tablas que se tienen en una base de datos, esta lindura de
{\em query\/} lo soluciona\footnote{Y además dice cuántos registros
tiene cada tabla. Claro que lo repite para cada campo, pero bueno, SQL
no fue hecho para formatear datos.}.
\begin{verbatim}
SELECT pg_class.relname,pg_attribute.attname,pg_class.reltuples,pg_type.typname
FROM pg_attribute
WHERE attrelid = pg_class.oid
AND attnum > 0
AND atttypid=pg_type.oid
AND pg_class.oid IN
(SELECT oid FROM pg_class WHERE relname !~~ 'pg%')
AND pg_type.oid IN (SELECT oid FROM pg_type);
\end{verbatim}
Fin... de momento
\section{La base no está en esta máquina, sino en la otra}
¿Y cómo se conecta uno a una base que no es local? Bueno, el
asunto es fácil. Si el operetas del otro sistema configuró bien el
asunto, y da acceso validando al usuario, entonces basta con usar la
bandera \verb+-h+:
\begin{verbatim}
psql -h maquina
\end{verbatim}
\noindent donde, por supuesto, \verb+maquina+ es la otra máquina.
\section{Algunas tablillas pachangueras y cochinas}
Las tablas que se ponen a continuación, son para referencia y aparecen
mencionadas en varias partes del texto.
%\chirris
\vspace{2pc}
\begin{table}[ht]
\begin{center}
\begin{tabular}{|l|l|}
\hline\hline
{\bf Nombre del catálogo}& {\bf Descripción}\\\hline\hline
pg$\_$database & Bases de datos\\\hline
pg$\_$class & Clases o tablas\\\hline
pg$\_$attribute & Atributos o campos de la clase o tabla\\\hline
pg$\_$index & Índices secundarios\\\hline
pg$\_$proc & Procedimientos (en C y en SQL)\\\hline
pg$\_$type & Tipos de datos (del sistema y definidos por el usuario)\\\hline
pg$\_$operator & Operadores (del sistema y definidos por el usuario)\\\hline
pg$\_$aggregate & Agregados y funciones agregadas\\\hline
pg$\_$am & Métodos de acceso\\\hline
pg$\_$amop & Operadores de métodos de acceso\\\hline
pg$\_$amproc & Funciones de soporte para métodos de acceso\\\hline
pg$\_$opclass & Clases de operadores de métodos de acceso\\\hline
\\\hline\hline
\end{tabular}
\caption{Catalogo del sistema PostgreSQL. Cada base de datos tiene
estas mismas tablas, salvo por la primera que es única, que almacenan
cada una de las partes que componen la base de
datos.}\label{tab:catalogo}
\end{center}
\end{table}
\vspace{2pc}
\begin{table}[ht]
\begin{center}
\begin{tabular}{|l|l|r|p{18pc}|}
\hline\hline
\multicolumn{4}{|c|}{\bf Table = pg$\_$database}\\\hline\hline
\bf Field&\bf Type&\bf Length&\bf Description\\\hline
datname&name&32&Nombre de la tabla\\
datdba&int4&4&Uid del dababase admin\\
datpath&text&var&El path para llegar hasta la tabla\\\hline\hline
\end{tabular}
\caption{Tabla que contiene todas las bases de datos que existen en el
sistema. Vale la pena consultarla en aplicaciones que impliquen
explorar diversos aspectos.}\label{tab:database}
\end{center}
\end{table}
\begin{table}[ht]
\begin{center}
\begin{tabular}{|l|l|r|p{18pc}|}
\hline\hline
\multicolumn{4}{|c|}{\bf Table = pg$\_$class}\\\hline\hline
\bf Field&\bf Type&\bf Length&\bf Description\\\hline
relname&name&32&Nombre de la tabla\\
reltype&oid&4&Eso, el Object Id de la tabla\\
relowner&oid&4&El UID (postgres) del dueño de la tabla\\
relam&oid&4&\\
relpages&int4&4&\\
reltuples&int4&4&\\
relhasindex&bool&1&\\
relisshared&bool&1&\\
relkind&char&1&\\
relnatts&int2&2&\\
relchecks&int2&2&\\
reltriggers&int2&2&\\
relhasrules&bool&1&\\
relacl&aclitem[]&var&\\\hline\hline
\end{tabular}
\caption{Tabla que contiene todas las bases de datos que existen en el
sistema. Vale la pena consultarla en aplicaciones que impliquen
explorar diversos aspectos.}\label{tab:class}
\end{center}
\end{table}
\begin{table}
\begin{center}
\begin{tabular}{|l|l|r|p{18pc}|}
\hline\hline
\multicolumn{4}{|c|}{\bf Table = pg$\_$attribute}\\\hline\hline
\bf Field&\bf Type&\bf Length&\bf Description\\\hline
attrelid&oid&4&OID del campo\\
attname&name&32&Nombre del campo\\
atttypid&oid&4&\\
attdisbursion&float4&4&\\
attlen&int2&2&\\
attnum&int2&2&\\
attnelems&int4&4&\\
attcacheoff&int4&4&\\
atttypmod&int2&2&\\
attbyval&bool&1&\\
attisset&bool&1&\\
attalign&char&1&\\
attnotnull&bool&1&\\
atthasdef&bool&1&\\\hline\hline
\end{tabular}
\caption{Tabla que contiene todos los campos de todas las bases de
datos que existen en el sistema. Vale la pena consultarla en
aplicaciones que impliquen explorar diversos
aspectos.}\label{tab:attribute}
\end{center}
\end{table}
\begin{thebibliography}{Bowman}
\bibitem[Bowman]{Bowman} {\bf The Practical SQL Handbook}, Judity
Bowman, Sandra Emerson, and Marcy Damovsky, ISBN: 0-201-44787-8,
Addisson-Wesley, 1997.
\bibitem[Trimble]{Trimble} {\bf A Visual Introduction to SQL},
J. Harvey Trimble Jr., and David Chappell, ISBN: 0-471-61684-2, John
Wiley \& Sons, Inc.
\end{thebibliography}
\end{document}
==============================================================================
PD. Gil, mándame un mensaje porque perdí tu dirección. Saludos.
--
La Mancha, http://breogan.iimas.unam.mx/~mancha
casa://AvRevolucion.1761.depto.5/~mancha
ring://550-2547.df.telmex.com.mx/~pedir.por.mancha
chamba://cubo-320.iimas.unam.cu/~mancha
rechamba://533-3035.df.telmex.com.mx/~pedir.por.el.director
--------- Pie de mensaje -------------------------------------------
Archivo historico: http://tlali.iztacala.unam.mx/maillist/pgsql-ayuda
Cancelar inscripcion:
mail to: majordomo@tlali.iztacala.unam.mx
text : cancelacion pgsql-ayuda