4.7. Tipos de datos

4.7. Tipos de datos Dataprix 23 Octubre, 2009 - 13:48

4.7.1. Tipos logicos

4.7.1. Tipos logicos Dataprix 23 Octubre, 2009 - 13:54

PostgreSQL incorpora el tipo lógico boolean, también llamado bool. Ocupa un byte de espacio de almacenamiento y puede almacenar los valores falso y verdadero.

Valor Nombre
Falso false, ‘f’, ‘n’, ‘no’, 0
Verdadero true, ‘t’, ‘y’, ‘yes’, 1

PostgreSQL soporta los operadores lógicos siguientes: and, or y not.

Aunque los operadores de comparación se aplican sobre prácticamente todos los tipos de datos proporcionados por PostgreSQL, dado que su resultado es un valor lógico, describiremos su comportamiento en la siguiente tabla:

Operador Descripción
> Mayor que
<    Menor que   
<= Menor o igual que
>= Mayor o igual que

<>

!=

Distinto de

 

4.7.2. Tipos numericos

4.7.2. Tipos numericos Dataprix 23 Octubre, 2009 - 14:08

PostgreSQL dispone de los tipos enteros smallint, int y bigint que se comportan como lo hacen los enteros en muchos lenguajes de programación.

Los números con punto flotante real y double precisión almacenan cantidades con decimales. Una característica de los números de punto flotante es que pierden exactitud conforme crecen o decrecen los valores.

Aunque esta pérdida de exactitud no suele tener importancia en la mayoría de las  ocasiones,  PostgreSQL  incluye  el  tipo  numeric,  que  permite  almacenar cantidades muy grandes o muy pequeñas sin pérdida de información. Por supuesto, esta ventaja tiene un coste, los valores de tipo numeric ocupan un espacio  de  almacenamiento  considerablemente  grande  y  las  operaciones  se ejecutan sobre ellos muy lentamente. Por lo tanto, no es aconsejable utilizar el tipo numeric si no se necesita una alta precisión o se prima la velocidad de procesamiento.

 

Nombre Tamaño Otros nombres Comentario
smallint 2 bytes int2
int 4 bytes int4, integer
bigint 8 bytes int8
numeric(p,e) 11 + (p/2) ‘p’ es la precisión, ‘e’ es la escala
real 4 bytes float, float4
double precision 8 bytes float8
serial No es un tipo, es un entero autoincrementable

Serial

La declaración serial es un caso especial, ya que no se trata de un nuevo tipo. Cuando se utiliza como nombre de tipo de una columna, ésta tomará automáticamente valores consecutivos en cada nuevo registro.

Ejemplo de una tabla que define la columna folio como tipo serial.

create table Factura(
folio serial,
cliente varchar(30),
monto real
);

PostgreSQL respondería esta instrucción con dos mensajes:

•  En el primero avisa que se ha creado una secuencia de nombre factura_folio_seq:

NOTICE: CREATE TABLE will create implicit sequence `factura_folio_seq´ for SERIAL column `

•  En el segundo avisa de la creación de un índice único en la tabla utilizando la columna folio:

 

NOTICE: CREATE TABLE / UNIQUE will create implicit index `factura_folio_key´ for table `factura´ CREATE

Si se declaran varias columnas con serial en una tabla, se creará una secuencia y un índice para cada una de ellas.

 

4.7.3. Operadores numericos

4.7.3. Operadores numericos Dataprix 23 Octubre, 2009 - 14:12

PostgreSQL ofrece un conjunto predefinido de operadores numéricos, que presentamos en la siguiente tabla:

 

Ejemplo

select |/ 9;
select 43 % 5;
select !! 7;
select 7!;

 Símbolo  Operador
 +    Adición   
 -  Substracción
 *  Multiplicación
 /  División
 %  Módulo   
 ^  Exponenciación   
 |/   Raíz cuadrada
 ||/   Raíz cúbica
 !  Factorial
 !!   Factorial como operador fijo
 @    Valor absoluto   
 &    AND binario   
 |      OR binario   
 #    XOR binario   
 ~   Negación binaria
<<   Corrimiento binario a la izquierda
 >>   Corrimiento binario a la derecha

 

4.7.4. Tipos de caracteres

4.7.4. Tipos de caracteres Dataprix 23 Octubre, 2009 - 15:46
Recordad
Las comillas dobles delimitan identificadores que contienen caracteres especiales.

Los valores de cadena en PostgreSQL se delimitan por comillas simples.

demo=# select `Hola mundo´;
?column?
------------
Hola mundo
(1 row)

Se puede incluir una comilla simple dentro de una cadena con \’ o ‘ ‘:

demo=# select `Él dijo: `"Hola"´
?column?
------------------
Él dijo: `Hola´
(1 row)

Las cadenas pueden contener caracteres especiales utilizando las llamadas secuencias de escape que inician con el caracter ‘\’:

 

\n nueva línea
\r retorno de carro
\t tabulador
\b retroceso
\f cambio de página
\r retorno de carro
\\ el caracter \

 

Las secuencias de escape se sustituyen por el carácter correspondiente:

demo=# select `Esto está en \n dos renglones´;
?column?
------------------------------
Esto está en
dos renglones
(1 row)

PostgreSQL ofrece los tipos siguientes para cadenas de caracteres:

Tipo Otros nombres Descripción
char(n) character(n) Reserva n espacios para almacenar la cadena
varchar(n) character var-ying(n) Utiliza los espacios necesarios para almacenar una cadena menor o igual que n
text Almacena cadenas de cualquier magnitud

4.7.5. Operadores

4.7.5. Operadores Dataprix 23 Octubre, 2009 - 16:03

En la siguiente tabla se describen los operadores para cadenas de caracteres:

Operador Descripción ¿Distingue mayúsculas y minúsculas?
|| Concatenación -
~ Correspondencia a expresión regular
~* Correspondencia a expresión regular No
!~ No correspondencia a expresión regular
!~* No correspondencia a expresión regular -

 

Bibliografía
Es recomendable consultar el             manual para obtener la referencia      completa de funciones.                      

En la siguiente tabla se muestran algunas funciones de uso común sobre cadenas de caracteres:

 

Función Descripción
Length(cadena) Devuelve la longitud de la cadena
lower(cadena) Convierte la cadena a minúsculas
ltrim(cadena,caracteres) Elimina de la izquierda los caracteres specificados
substring(cadena from patrón) Extrae la subcadena que cumple el patrón especificado

Sobre las cadenas también podemos utilizar los operadores de comparación que ya conocemos.

 

Ejemplo

En este caso, el resultado de la comparación menor que es VERDADERO:

demo=# select `HOLA´ &lt; `hola´;
?column?
----------
(1 row)

4.7.6. Fechas y horas

4.7.6. Fechas y horas Dataprix 26 Octubre, 2009 - 10:46

En la siguiente tabla se muestran los tipos de datos referentes al tiempo que ofrece PostgreSQL:

 

Tipo de dato Unidades Tamaño Descripción Precisión
date día-mes-año 4 bytes Fecha Día
time hrs:min:seg:micro 4 bytes Hora Microsegundo
timestamp día-mes-año hrs:min:seg:micro 8 bytes Fecha más hora Microsegundo
interval second, minute, hour, day, week, month, year, decade, century, millennium* 12 bytes Intervalo de tiempo Microsegundo

                                                                                                                                                                            

* También admite abreviaturas.

Existe un tipo de dato timez que incluye los datos del tipo time y, además, la zona horaria. Su sintaxis es la siguiente:

hh:mm[:ss[.mmm]][am|pm][zzz]

El tipo de datos date almacena el día, mes y año de una fecha dada y se muestra por omisión con el formato siguiente: YYYY-MM-DD:

demo=# set datestyle = `german´;
SET VARIABLE
demo=# select * from persona;
nacimiento
------------
22.05.2004
(1 row)

Para cambiar el formato de presentación, debemos modificar la variable de entorno datestyle:

 

demo=# create table Persona ( nacimiento date );
CREATE
demo=# insert into persona values ( `2004-05-22´ );
INSERT 17397 1
demo=# select * from persona;
nacimiento
------------
2004-05-22
(1 row)

Nombre
del
formato

Formato Ejemplo
ISO Año-mes -día 2004-05-22
GERMAN Día-mes-año 22-05-2004
PORTUGUES día-mes-año 22-05-2004
SQL mes/día/año 05/22/2004

 

4.7.7. Arrays

4.7.7. Arrays Dataprix 26 Octubre, 2009 - 11:18

El tipo de datos array es una de las características especiales de PostgreSQL, permite el almacenamiento de más de un valor del mismo tipo en la misma columna.

Definición
Los arrays no cumplen la pri-   mera forma normal de Cood,     por lo que muchos los               consideran inaceptables en el  modelo relacional.                     

 

demo=# create table Estudiante (
demo(# nombre varchar(30),
demo(# parciales int [3]
demo(# );
CREATE

 

La columna parciales acepta tres calificaciones de los estudiantes.

 

También es posible asignar un solo valor del array:

demo=# insert into Estudiante( nombre, parciales[2]) values ( `Pedro´ ,`{90}´); INSERT 17418 1
demo=# select * from Estudiante ;
nombre      | parciales
------------+------------
John Lennon |
Juan        | {90,95,97}
Pedro       | {90}
(3 rows)

Los arrays, al igual que cualquier columna cuando no se especifica lo contrario, aceptan valores nulos:

demo=# insert into Estudiante values ( `John Lennon´ );
INSERT 17416 1
demo=# insert into Estudiante values ( `Juan´ , `{90,95,97}´ );
INSERT 17417 1

Los valores del array se escriben siempre entre llaves.

 

demo=# select * from Estudiante;
nombre        | parciales
--------------+------------
John Lennon   |
Juan          | {90,95,97}
(2 rows)

Para seleccionar un valor de un array en una consulta se especifica entre corchetes la celda que se va a desplegar:

 

Sólo Juan tiene calificación en el tercer parcial.                                 

demo=# select nombre, parciales[3] from Estudiante;
nombre        | parciales
--------------+-----------
John Lennon   |
Juan          | 97
Pedro         |
(3 rows)

 

La columna parciales del registro       Pablo incluye cuatro celdas y sólo    la última tiene valor.                            

En muchos lenguajes de programación, los array se implementan con longitud fija, PostgreSQL permite aumentar su tamaño dinámicamente:
 

 

demo=# insert into Estudiante( nombre, parciales[4]) values ( `Pablo´ , `{70}´); INSERT 17419 1
demo=# select * from Estudiante;
nombre        | parciales
--------------+------------
John Lennon   |
Juan          | {90,95,97}
Pedro         | {90}
Pablo         | {70}
(4 rows)

Mediante la función array_dims() podemos conocer las dimensiones de un array:

demo=# select nombre, array_dims(parciales) from Estudiante;
nombre        | array_dims
--------------+------------
John Lennon   |
Juan          | [1:3]
Pedro         | [1:1]
Pablo         | [1:1]
(4 rows)

 

4.7.8. BLOB

4.7.8. BLOB Dataprix 26 Octubre, 2009 - 11:44

El tipo de datos BLOB (Binary Large OBject) permite almacenar en una columna un objeto de gran tamaño. PostgreSQL no conoce nada sobre el tipo de información que se almacena en una columna BLOB, simplemente lo considera como una secuencia de bytes. Por este motivo, no se tienen operaciones sobre los tipos BLOB, con la excepción del operador de concatenación, que simplemente une el contenido de dos BLOB en uno.

Veamos cómo almacenar una fotografía en una tabla de personas mediante tipos BLOB.

Una primera manera de hacerlo es importando el contenido del archivo que contiene la imagen mediante la función lo_import():

demo=# select lo_import(`/home/quiron/mi-foto.jpg´);
lo_import
-----------
17425
(1 row)

 

Esta función devuelve como resultado el OID del objeto insertado.¿Dónde se ha almacenado la fotografía si no hemos utilizado el comando insert? Post- greSQL mantiene una tabla de nombre pg_largeobject con el objetivo de almacenar BLOB. Podemos utilizar el OID para hacer referenciar al objeto en una tabla:

demo=# create table persona (
demo(# nombre varchar(30),
demo(# direccion varchar(30),
demo(# fotografia oid
demo(# );
CREATE

Los registros insertados en esta tabla llevan un número entero como OID que, en el siguiente caso, se ha obtenido solicitándolo a la función lo_import() anterior.

demo=# insert into persona values (`Julio´ , `Cedro 54´, 17425);

La inserción anterior pudo haberse realizado en un solo paso:

 

demo=# insert into persona
values ( `Julio´ , `Cedro 54´, lo_import(`/home/quiron/mi-foto.jpg´));

Para extraer el contenido de la columna, se utiliza la función lo_export()

demo=# select lo_export(17425,`/tmp/mi-foto.jpg´);
lo_export
-----------
1
(1 row)

La función lo_unlink() permite borrar un BLOB almacenado en pg_largeobject:

select lo_unlink(17425);

 

Veamos el formato que utiliza PostgreSQL para visualizar BLOB.

Se ha recortado la salida para hacer más comprensible la tabla.

loid  | pageno | data
------+--------+------------------------------------------------------------------
17425 | 0      | \377\330\377\340\000\020JFIF\000\001\001\001\000H\000H\000\000\37
17425 | 1      | \256-}\306\267\032s[\336)\245\231\370|L\206\275\364\224\321\237\2
17425 | 2      | \341\226;\015l\232\033f\\\371\251\0323\003t\307\207~\035GB\271\17
(3 rows)

La fotografía se ha dividido en tres registros, que son las páginas 0, 1 y 2 del BLOB. Los tres registros tienen el mismo loid, lo que significa que son el mismo objeto. Obsérvese también, que los bytes son desplegados como caracteres de la forma ‘\ddd’, donde ddd son tres dígitos octales.

Para almacenar un BLOB en una tabla de forma directa; es decir, sin utilizar la tabla del sistema pg_largeobject utilizamos el tipo de dato bytea:

 

En el primer caso se está insertando un BLOB de un solo byte, el carácter ASCII cero.

En el segundo caso se están insertando 4 bytes, los tres primeros están representados directamente por los caracteres imprimibles ASCII y el tercero, el caracter ASCII 30 octal, como no es imprimible, se escribe
en notación ‘\\ddd’.

 

demo=# create table persona (
demo(# nombre varchar(30),
demo(# direccion varchar(30),
demo(# fotografia bytea
demo(# );
CREATE

 La columna fotografía es de tipo bytea, lo que permite almacenar objetos de gran tamaño, cerca de 1GB. Para insertar valores en estas columnas, hacemos lo siguiente:

demo=# insert into persona values ( `Jorge´ , `Cerezo 55´, `\\000´);
INSERT 17436 1
demo=# insert into persona values ( `Luis´ , `Encino 67´, `abc\\030´);
INSERT 17437 1

La consulta se visualiza como sigue:

demo=# select * from persona;
nombre  | direccion | fotografia
--------+-----------+------------
Jorge   | Cerezo 55 | \000
Luis    | Encino 67 | abc\030
(2 rows)

 

Los caracteres en notación octal se muestran con una barra invertida y con dos tal como se escribieron. Esto es debido a que, en realidad, sólo llevan una barra invertida, pero por cuestiones de diseño PostgreSQL, las literales BLOB deben escribirse con doble barra invertida.