Notas sobre PostgreSQL
Adicionar la ruta del directorio Bin del postgresql al path del DOS.
Adicionar la variable de la ruta de la data de PostgreSQL:
set PGDATA=d:\data\pgsql\data
Esta es la ruta donde se guarda la configuación de Postgres pero la ruta de la data real se puede definir con los tablespaces.
pg_ctl permite saber el estado del servicio y arrancarlo y detenerlo
pg_ctl --help
pg_ctl status -D d:\data\pgsql\data -> para saber el estado del la instancia.
pg_ctl stop -m immediate -> detiene el servicio inmediatamente
Adicionar la ruta del directorio Bin del postgresql al path del DOS.
Adicionar la variable de la ruta de la data de PostgreSQL:
set PGDATA=d:\data\pgsql\data
Esta es la ruta donde se guarda la configuación de Postgres pero la ruta de la data real se puede definir con los tablespaces.
pg_ctl permite saber el estado del servicio y arrancarlo y detenerlo
pg_ctl --help
pg_ctl status -D d:\data\pgsql\data -> para saber el estado del la instancia.
pg_ctl stop -m immediate -> detiene el servicio inmediatamente
pg_ctl -D d:\data\pqsql\data reload -> para recargar la base de datos luego de cambios en el archivo de configuración.
para crear un tablespace:
CREATE TABLESPACE espacioextra LOCATION 'g:\data'
select * from pg_tablespace;
para renombrar:
ALTER TABLESPACE extraespacio RENAME TO ts_extraespacio;
DROP TABLESPACE ts_extraespacio;
Backup:
pg_dump -U postgres -c -f c:\temp\rdata.bup rdata
pg_restore -U postgres -d rdata -f c:\temp\rdata.bup
Para conectarse a la base de datos:
pg_restore -U postgres rdata -f c:\temp\rdata.bup
para salir:
\q
para cambiar base de datos:
\connect basedatos
para ejecutar un script .sql:
\i script.sql
para editar un script:
\e script.sql
para redirecionar todo a un archivo:
\o salida.sql
para ver el estado actual:
\set
para ayuda de los comandos:
\h
para la sintaxis de un comando (select):
\h select
para ejecutar un comando postgres desde el SO:
pssql -d rdata -U postgres -c "SELECT * FROM cuentas" -o "salida.txt"
PostgrSQL puede manejar varias bases de datos al mismo tiempo. La colección de bases de datos se llama cluster.
Para crear una base de datos:
CREATE DABASE compannia;
para conectarse una base de datos:
\c compannia;
para borrar una base de datos:
DROP DATABASE compannia;
si estás conectado a la base de datos no puedes eliminarla, para eso primero hay que conectarse a otra base de datos antes. Se puede usar template1:
\c template1
DROP DATABASE compannia;
para modificar la base de datos con ALTER DABASE:
ALTER DATABASE compannia RENAME TO prueba;
No se puede renombrar una base de datos que esté en uso.
Los esquemas contienen tablas, vistas, funciones y otros objetos dentro de una base de datos.
Se pueden crear varios esquemas dentro de una base de datos. Las ventajas son que puedes tener la data organizada por esquemas de acuerdo a los sistemas que la usan o a otras características.
Se pueden manejar los esquemas:
CREATE SCHEMA esquema;
ALTER SCHEMA esquema RENAME TO nuevo_esquema;
DROP SCHEMA esquema CASCADE;
Para referenciar una tabla que se encuentra en un esquema se usa la forma esquema.tabla. Para evitar poner el nombre del esquema se pueden aumentar al schema search path, que funciona igual al path del DOS.
para ver:
show search_path;
para asignar:
set search_path "$user",public,miesquema;
Para crear tablas:
CREATE TABLE empleados
(empleado SERIAL UNIQUE NOT NULL,
nombres VARCHAR(40) NOT NULL,
apellidos VARCHAR(40) NOT NULL,
correo VARCHAR(80),
telefono VARCHAR(20),
PRIMARY KEY (empleado)
);
Se puede crear una tabla en un esquema determinado colocando el nombre del esquema antes del de la tabla:
CREATE TABLE esquema.tabla
Para copiar una tabla o crear otra desde una que ya existe:
CREATE TABLE empleados2 AS SELECT * FROM empleados;
La nueva tabla creada no va a tener los triggers, valores default o constrains que se implementaron en la tabla original.
Se puede crear una tabla con solamente algunos campos de la tabla original:
CREATE TABLE empleados3 AS SELECT nombres, apellidos FROM empleados;
Se pueden crear tablas temporales, las cuales duran el tiempo que dura la sesión de conexión. Estas tablas generalmente se usan para crear partes de información de tablas mayores para agilizar los procesos.
CREATE TEMPORARY TABLE emp_temp AS SELECT nombres, apellidos FROM empleados;
Las tablas temporales se crean en otro esquema, algo como pg_temp_1 y el manejo de ese esquema lo hace la base de datos.
Se pueden crear tablas temporales que duren hasta que termine la transacción en la que se ha creado, es decir, hasta que se haga commit (entre BEGIN y COMMIT). Estas tablas se crean como:
CREATE TEMPORARY TABLE emp_temp2
( nombres varchar(40),
apellidos varchar(40),
correo varchar(80)
) ON COMMIT DROP;
Tener en cuenta que la existencia de la tabla es entre el BEGIN y el COMMIT. Si no se manejan transacciones la tabla se borraré inmediatamente.
Para ver las tablas que tenemos:
\dt
Para ver la estructura de una tabla:
\d empleados
Para eliminar una tabla:
DROP TABLE empleados;
DROP TABLE empleados1, empleados2;
Si la tabla tiene referencias a otras tablas debe usarse con CASCADE:
DROP TABLE empleados CASCADE;
Para adicionar campos a una tabla:
ALTER TABLE empleados ADD COLUMN cumpleannos TIMESTAMPZ;
Para cambiar el tipo de dato a fecha solamente, sin hora:
ALTER TABLE empleados ALTER COLUMN cumpleannos TYPE DATE;
Para cambiar el nombre de un campo:
ALTER TABLE empleados RENANE COLUMN cumpleannos TO fecha_nacimiento;
Para eliminar una columna:
ALTER TABLE empleados DROP COLUMN fecha_nacimiento;
Las secuencias son objetos que devuelven números únicos para asignarlos a una tabla y se usan generalmente para asignar llaver primarias sobre todo en los casos de muchos insert concurrentes.
Funciones para sequences:
SELECT nextval('secuencia') -> devuelve el siguiente valor de la secuencia
SELECT currval('secuencia') -> devuelve el valor actual de la secuencia, pero debe haberse hecho por lo menos un nextval antes.
SELECT lastval() devuelve el último valor generado por alguna secuencia.
SELECT setval('secuencia', valor) -> asigna un nuevo valor a la secuencia. nextval devolverá el valor siguiente.
SELECT setval('secuencia', valor, false) -> asigna un nuevo valor a la secuencia y nextval devolverá ese valor.
Tipos de datos y atributos
Tipos de datos fuerzan a la data a que cumpla una serie de reglas inherentes a cada tipo de dato como tamaño, tipo (numérico, string, fecha) y formato. El comportamiento de los tipos de datos se pueden complementar con los atributos.
Adicionalmente, se pueden crear tipos compuestos y dominios. Tipos compuestos son una relación de tipos de dato asociados a los campos y los dominios son derivados de otros tipos pero basados en un tipo particular.
Tipos de datos
Postgres permite definir tipos de datos propios, pero vamos a revisar los más comunes agrupados en fecha, numéricos, string y booleanos.
Tipos de datos fecha y hora
DATE
Se usa para guardar el día solamente. Se muestra como YYYY-MM-DD pero puede almacenarse como YYYY-MM-DD, YYYYMMDD.
TIME[p] (sin timezone)
Almacena información de la hora solamente. Se puede almacenar como HH:MM:SS, hh:mm:ss p.m. o sin los ":". La precisión se usa para mostrar los números de decimales de los segundos.
TIME[p] WITH TIMEZONE
Almacena la hora correspondiente a la zona horaria del equipo.
INTERVAL[p]
El tipo intervalo almacena los intervalos de tiempo. Se puede grabar en horas, minutos, segundos, años, siglos, etc.
Tipos de datos numéricos
SMALLINT
Soporta valores desde -32,768 a 32,767. Ocupa 2 bytes. Alias INT2.
INTEGER
Soporta valores desde -2,147,483,648 a 2,147,483,647. Ocupa 4 bytes. Alias INT o INT4.
BIGINT
Solporta valores desde -9,223,372,036,854,775,808 a 9,223,372,036,854,775,807. Ocupa 8 bytes, Alias INT8.
NUMERIC[p,(s)]
Almacena números de hasta 1.000 dígitos de precisión y desarrolla los cálculos de forma exacta. Se usa para almacenar valores monetarios. Son más lentos que los otros tipos numéricos. Se puede indicar la precisión del campo en entero y decimales. Por ejemplo, 123.45 es NUMERIC(5,2). Si tuviera más decimales se redondea a la cantidad de decimales indicados. Se puede definir el campo NUMERIC sin precisión y asume cualquier entero con cualquier decimal pero no se recomienda por performance.
DECIMAL
Es igual a NUMERIC
REAL
Es un tipo de dato de punto flotante de precisión variable. Los valores soportados son desde 1E-37 a 1E+37 y soporta una preción de por los menos 6 decimales. Se recomienda NUMERIC en lugar de REAL por la mayor precisión del primero. Ocupa 4 bytes.
DOUBLE PRECISION
Es de precisión variable y punto flotante. Almacena valores desde 1E-307 a 1E+308 con por lo menos 15 dígitos de precisión. Ocupa 8 bytes,
FLOAT[p]
Es un starndard para los tipos de números inexactos. FLOAT(1) a FLOAT(24) equivalen a REAL y FLOAT(25) a FLOAT(53) a DOUBLE PRECISION.
SERIAL
El tipo SERIAL no es realmente un tipo sino un equivalente para definir un campo como auto-increment.
CREATE TABLE tabla (campo SERIAL);
equivale a
CREATE TABLE tabla (campo INTEGER DEFAUL nextval('tabla_campo_seq') NOT NULL);
Al definirse como SERIAL se crea automáticamente un SEQUENCE asociado a ese campo. Si el campo se elimina también se elimina el SEQUENCE.
Se puiede usar también el tipo BIGSERIAL, que define el campo como BIGINT.
Tipos String
CHAR
Son strings de longitud fija. Si se inserta un string más largo que el tamaño del campo da error, a menos que sean espacios. Si el valor es menor que la longitud del campo se llena de espacios hasta completarlo. CHAR sin extensión equivale a CHAR(1).
VARCHAR
Son strings de longitud variable. Si se inserta un string más largo que el tamaño del campo da error, a menos que sean
espacios. Si el valor es menor que la longitud del campo no se llena de espacios, el campo se almacena tal cual. VARCHAR sin extensión acepta cualquier string de cualquier longitud.
TEXT
Ofrece almacenamiento de longitud variable y acepta strings de cualquier longitud.
Tipos booleanos
Los campos booleanos aceptan tres estados: TRUE, FALSE o NULL (desconocido). TRUE puede representarse como TRUE, 't', 'true', 'y', 'yes', '1' y FALSE puede representarse como FALSE, 'f', 'false', 'n', 'no', '0'.
Atributos de campos
CHECK
Provee una forma de restringir los valores que se van a almacenar en un campo y generalmente se refiere como un constraint. El constraint debe devolver un valor booleano. Por ejemplo:
dias_vacaciones_ganados INTEGER CHECK (dias_vacaciones_ganados > 0)
dias_vacaciones_tomados INTEGER CHECK (dias_vacaciones_tomados < dias_vacaciones_ganados)
Se puede definir el constraint a nivel de la tabla, permitiendo definir cada uno con un nombre único, lo que simplifica deducir los errores:
CREATE TABLE empleados
( empleado SERIAL UNIQUE NOT NULL,
dias_vacaciones_ganadas INTEGER CHECK (dias_vacaciones_ganadas > 0),
dias_vacaciones_tomadas INTEGER CHECK (dias_vacaciones_tomadas > 0),
CONSTRAINT sin_dias_libres CHECK (dias_vacaciones_tomadas <= dias_vacaciones_ganadas)
);
para crear un tablespace:
CREATE TABLESPACE espacioextra LOCATION 'g:\data'
select * from pg_tablespace;
para renombrar:
ALTER TABLESPACE extraespacio RENAME TO ts_extraespacio;
DROP TABLESPACE ts_extraespacio;
Backup:
pg_dump -U postgres -c -f c:\temp\rdata.bup rdata
pg_restore -U postgres -d rdata -f c:\temp\rdata.bup
Para conectarse a la base de datos:
pg_restore -U postgres rdata -f c:\temp\rdata.bup
para salir:
\q
para cambiar base de datos:
\connect basedatos
para ejecutar un script .sql:
\i script.sql
para editar un script:
\e script.sql
para redirecionar todo a un archivo:
\o salida.sql
para ver el estado actual:
\set
para ayuda de los comandos:
\h
para la sintaxis de un comando (select):
\h select
para ejecutar un comando postgres desde el SO:
pssql -d rdata -U postgres -c "SELECT * FROM cuentas" -o "salida.txt"
PostgrSQL puede manejar varias bases de datos al mismo tiempo. La colección de bases de datos se llama cluster.
Para crear una base de datos:
CREATE DABASE compannia;
para conectarse una base de datos:
\c compannia;
para borrar una base de datos:
DROP DATABASE compannia;
si estás conectado a la base de datos no puedes eliminarla, para eso primero hay que conectarse a otra base de datos antes. Se puede usar template1:
\c template1
DROP DATABASE compannia;
para modificar la base de datos con ALTER DABASE:
ALTER DATABASE compannia RENAME TO prueba;
No se puede renombrar una base de datos que esté en uso.
Los esquemas contienen tablas, vistas, funciones y otros objetos dentro de una base de datos.
Se pueden crear varios esquemas dentro de una base de datos. Las ventajas son que puedes tener la data organizada por esquemas de acuerdo a los sistemas que la usan o a otras características.
Se pueden manejar los esquemas:
CREATE SCHEMA esquema;
ALTER SCHEMA esquema RENAME TO nuevo_esquema;
DROP SCHEMA esquema CASCADE;
Para referenciar una tabla que se encuentra en un esquema se usa la forma esquema.tabla. Para evitar poner el nombre del esquema se pueden aumentar al schema search path, que funciona igual al path del DOS.
para ver:
show search_path;
para asignar:
set search_path "$user",public,miesquema;
Para crear tablas:
CREATE TABLE empleados
(empleado SERIAL UNIQUE NOT NULL,
nombres VARCHAR(40) NOT NULL,
apellidos VARCHAR(40) NOT NULL,
correo VARCHAR(80),
telefono VARCHAR(20),
PRIMARY KEY (empleado)
);
Se puede crear una tabla en un esquema determinado colocando el nombre del esquema antes del de la tabla:
CREATE TABLE esquema.tabla
Para copiar una tabla o crear otra desde una que ya existe:
CREATE TABLE empleados2 AS SELECT * FROM empleados;
La nueva tabla creada no va a tener los triggers, valores default o constrains que se implementaron en la tabla original.
Se puede crear una tabla con solamente algunos campos de la tabla original:
CREATE TABLE empleados3 AS SELECT nombres, apellidos FROM empleados;
Se pueden crear tablas temporales, las cuales duran el tiempo que dura la sesión de conexión. Estas tablas generalmente se usan para crear partes de información de tablas mayores para agilizar los procesos.
CREATE TEMPORARY TABLE emp_temp AS SELECT nombres, apellidos FROM empleados;
Las tablas temporales se crean en otro esquema, algo como pg_temp_1 y el manejo de ese esquema lo hace la base de datos.
Se pueden crear tablas temporales que duren hasta que termine la transacción en la que se ha creado, es decir, hasta que se haga commit (entre BEGIN y COMMIT). Estas tablas se crean como:
CREATE TEMPORARY TABLE emp_temp2
( nombres varchar(40),
apellidos varchar(40),
correo varchar(80)
) ON COMMIT DROP;
Tener en cuenta que la existencia de la tabla es entre el BEGIN y el COMMIT. Si no se manejan transacciones la tabla se borraré inmediatamente.
Para ver las tablas que tenemos:
\dt
Para ver la estructura de una tabla:
\d empleados
Para eliminar una tabla:
DROP TABLE empleados;
DROP TABLE empleados1, empleados2;
Si la tabla tiene referencias a otras tablas debe usarse con CASCADE:
DROP TABLE empleados CASCADE;
Para adicionar campos a una tabla:
ALTER TABLE empleados ADD COLUMN cumpleannos TIMESTAMPZ;
Para cambiar el tipo de dato a fecha solamente, sin hora:
ALTER TABLE empleados ALTER COLUMN cumpleannos TYPE DATE;
Para cambiar el nombre de un campo:
ALTER TABLE empleados RENANE COLUMN cumpleannos TO fecha_nacimiento;
Para eliminar una columna:
ALTER TABLE empleados DROP COLUMN fecha_nacimiento;
Las secuencias son objetos que devuelven números únicos para asignarlos a una tabla y se usan generalmente para asignar llaver primarias sobre todo en los casos de muchos insert concurrentes.
Funciones para sequences:
SELECT nextval('secuencia') -> devuelve el siguiente valor de la secuencia
SELECT currval('secuencia') -> devuelve el valor actual de la secuencia, pero debe haberse hecho por lo menos un nextval antes.
SELECT lastval() devuelve el último valor generado por alguna secuencia.
SELECT setval('secuencia', valor) -> asigna un nuevo valor a la secuencia. nextval devolverá el valor siguiente.
SELECT setval('secuencia', valor, false) -> asigna un nuevo valor a la secuencia y nextval devolverá ese valor.
Tipos de datos y atributos
Tipos de datos fuerzan a la data a que cumpla una serie de reglas inherentes a cada tipo de dato como tamaño, tipo (numérico, string, fecha) y formato. El comportamiento de los tipos de datos se pueden complementar con los atributos.
Adicionalmente, se pueden crear tipos compuestos y dominios. Tipos compuestos son una relación de tipos de dato asociados a los campos y los dominios son derivados de otros tipos pero basados en un tipo particular.
Tipos de datos
Postgres permite definir tipos de datos propios, pero vamos a revisar los más comunes agrupados en fecha, numéricos, string y booleanos.
Tipos de datos fecha y hora
DATE
Se usa para guardar el día solamente. Se muestra como YYYY-MM-DD pero puede almacenarse como YYYY-MM-DD, YYYYMMDD.
TIME[p] (sin timezone)
Almacena información de la hora solamente. Se puede almacenar como HH:MM:SS, hh:mm:ss p.m. o sin los ":". La precisión se usa para mostrar los números de decimales de los segundos.
TIME[p] WITH TIMEZONE
Almacena la hora correspondiente a la zona horaria del equipo.
INTERVAL[p]
El tipo intervalo almacena los intervalos de tiempo. Se puede grabar en horas, minutos, segundos, años, siglos, etc.
Tipos de datos numéricos
SMALLINT
Soporta valores desde -32,768 a 32,767. Ocupa 2 bytes. Alias INT2.
INTEGER
Soporta valores desde -2,147,483,648 a 2,147,483,647. Ocupa 4 bytes. Alias INT o INT4.
BIGINT
Solporta valores desde -9,223,372,036,854,775,808 a 9,223,372,036,854,775,807. Ocupa 8 bytes, Alias INT8.
NUMERIC[p,(s)]
Almacena números de hasta 1.000 dígitos de precisión y desarrolla los cálculos de forma exacta. Se usa para almacenar valores monetarios. Son más lentos que los otros tipos numéricos. Se puede indicar la precisión del campo en entero y decimales. Por ejemplo, 123.45 es NUMERIC(5,2). Si tuviera más decimales se redondea a la cantidad de decimales indicados. Se puede definir el campo NUMERIC sin precisión y asume cualquier entero con cualquier decimal pero no se recomienda por performance.
DECIMAL
Es igual a NUMERIC
REAL
Es un tipo de dato de punto flotante de precisión variable. Los valores soportados son desde 1E-37 a 1E+37 y soporta una preción de por los menos 6 decimales. Se recomienda NUMERIC en lugar de REAL por la mayor precisión del primero. Ocupa 4 bytes.
DOUBLE PRECISION
Es de precisión variable y punto flotante. Almacena valores desde 1E-307 a 1E+308 con por lo menos 15 dígitos de precisión. Ocupa 8 bytes,
FLOAT[p]
Es un starndard para los tipos de números inexactos. FLOAT(1) a FLOAT(24) equivalen a REAL y FLOAT(25) a FLOAT(53) a DOUBLE PRECISION.
SERIAL
El tipo SERIAL no es realmente un tipo sino un equivalente para definir un campo como auto-increment.
CREATE TABLE tabla (campo SERIAL);
equivale a
CREATE TABLE tabla (campo INTEGER DEFAUL nextval('tabla_campo_seq') NOT NULL);
Al definirse como SERIAL se crea automáticamente un SEQUENCE asociado a ese campo. Si el campo se elimina también se elimina el SEQUENCE.
Se puiede usar también el tipo BIGSERIAL, que define el campo como BIGINT.
Tipos String
CHAR
Son strings de longitud fija. Si se inserta un string más largo que el tamaño del campo da error, a menos que sean espacios. Si el valor es menor que la longitud del campo se llena de espacios hasta completarlo. CHAR sin extensión equivale a CHAR(1).
VARCHAR
Son strings de longitud variable. Si se inserta un string más largo que el tamaño del campo da error, a menos que sean
espacios. Si el valor es menor que la longitud del campo no se llena de espacios, el campo se almacena tal cual. VARCHAR sin extensión acepta cualquier string de cualquier longitud.
TEXT
Ofrece almacenamiento de longitud variable y acepta strings de cualquier longitud.
Tipos booleanos
Los campos booleanos aceptan tres estados: TRUE, FALSE o NULL (desconocido). TRUE puede representarse como TRUE, 't', 'true', 'y', 'yes', '1' y FALSE puede representarse como FALSE, 'f', 'false', 'n', 'no', '0'.
Atributos de campos
CHECK
Provee una forma de restringir los valores que se van a almacenar en un campo y generalmente se refiere como un constraint. El constraint debe devolver un valor booleano. Por ejemplo:
dias_vacaciones_ganados INTEGER CHECK (dias_vacaciones_ganados > 0)
dias_vacaciones_tomados INTEGER CHECK (dias_vacaciones_tomados < dias_vacaciones_ganados)
Se puede definir el constraint a nivel de la tabla, permitiendo definir cada uno con un nombre único, lo que simplifica deducir los errores:
CREATE TABLE empleados
( empleado SERIAL UNIQUE NOT NULL,
dias_vacaciones_ganadas INTEGER CHECK (dias_vacaciones_ganadas > 0),
dias_vacaciones_tomadas INTEGER CHECK (dias_vacaciones_tomadas > 0),
CONSTRAINT sin_dias_libres CHECK (dias_vacaciones_tomadas <= dias_vacaciones_ganadas)
);
