Join en Tablas con PostgreSQL

Existen dos tipos de combinaciones

Combinaciones internas de tablas: Donde cada registro de la tabla A se combina con otro de la tabla B que cumpla las condiciones.

Inner Join
       
    select * from empleado as a, departamento as b where a.departamento_id = b.id;
    select * from empleado as a inner join departamento as b on a.departamento_id = b.id;
    select * from empleado as a join departamento as b on a.departamento_id = b.id;

Para PostgreSQL utilizar inner join o join se refiere a la misma instrucción, en ambos casos se hacer referencia a una unión interna donde cada uno de los registros debe quedar asociado a un registros de la otra tabla.

En lugar de utilizar ON también se puede utilizar USING para especificar el campo con el cual se desea realizar la igualdad cuando los nombre de los campos son iguales en ambas tablas.

    select * from departamento join oficina using (id);
    select * from departamento join oficina using (nombre);

En ambos casos se obtendrán diferentes resultados según los valores almacenados en cada tabla.

Usign también permite realizar la búsqueda por más de un campo, cuando más de un campo de la tabla tiene el mismo nombre.

    select * from departamento join oficina using (id, nombre);

Natural Join

    select * from departamento natural join oficina;

Cuando tenemos el mismo nombre de campo para diferentes tablas, podemos usar el o los campos con el mismo nombre para unir diferentes tablas. Esta unión es un tipo especial de inner join. Esta operación iguala todas las columnas que tienen el mismo nombre en ambas tablas presentando como resultados solo los registros que tienen correspondencia.

Combinaciones externas de tablas: Donde se recuperan todos los registros de una tabla tengan o no correspondencia en la otra tabla.

Outer Join por la Izquierda

    select * from empleado as a left join departamento as b on a.departamento_id = b.id; 
    select * from empleado as e left outer join departamento as d on e.departamento_id = d.id;

Esta consulta recuperará todos los registros de la tabla izquierda (empleado) tengan o no correspondencia en la tabla derecha (departamento).

Outer Join por la Derecha

    select * from empleado as a right join departamento as b on a.departamento_id = b.id; 
    select * from empleado as e right outer join departamento as d on e.departamento_id = d.id;

Esta consulta recuperará todos los registros de la tabla derecha (departamento) tengan o no correspondencia en la tabla izquierda (empleado).

Outer Join Full

    select * from empleado as e full join departamento as d on e.departamento_id = d.id;
    select * from empleado as a full outer join departamento as b on e.departamento_id = b.id;

Esta consulta recuperará todos los registros de la tabla derecha (departamento) e izquierda (empleado) tengan o no correspondencia en la tabla izquierda (empleado) y derecha (departamento) respectivamente.

En los tres casos anteriores los registros que no tengan correspondencia completarán los campos con el valor null.

Cross Join 

    select * from empleado, departamento; 
    select * from empleado cross join departamento;

La más simple unión, es una unión de cruce, esto crea un conjunto resultado de todas las posibles combinaciones de las filas entre ambas tablas.

Existen otros tipos de combinaciones que corresponden a modificaciones de las consultas anteriores, pero que pueden ser de gran ayuda en algunos caso particulares. 

    select * from empleado as e left join departamento as d on e.departamento_id = d.id
where d.id is null;

Esta consulta presenta todos los registros de la tabla izquierda (empleado) que no se encuentran asociados a los registros de la tabla derecha (departamento). 

    select * from empleado as e right join departamento as d on e.departamento_id = d.id
where e.id is null;

Esta consulta presenta todos los registros de la tabla derecha (departamento) que no se encuentran asociadas a los registros de la tabla izquierda (empleado).

    select * from empleado as e full join departamento as d on e.departamento_id = d.id
where e.id is null or d.id is null;

Esta consulta presenta todos los registros de la tabla izquierda (empleado) y de la tabla derecha (departamento) que no se encuentran asociados a la otra tabla.


Referecias:

https://www.imaginanet.com/blog/diferencias-entre-join-left-join-y-right-join.html
http://donnierock.com/2014/03/04/diferencia-entre-inner-join-left-join-y-right-join-sql/
http://www.postgresqlforbeginners.com/2010/11/sql-inner-cross-and-self-joins.html

"Gracias, por compartir tus conocimientos"

PostgreSQL: Extraer los campos de una fecha

Uno de los recursos más almacenados en las bases de datos son las fecha, existiendo ocasiones en las cuales se deben obtener algunos campos de la fecha, para este ejemplo se utilizará la tabla personas creada como se observa a continuación:

create table personas (
   id                     SERIAL      NOT NULL,
   rut                   varchar(15) NULL,
   nombre           varchar(30) NULL,
   apaterno         varchar(30) NULL,
   amaterno        varchar(30) NULL,
   fecha_nac        date   NULL,
   hora_nac         time NULL,
   sexo                 varchar(1)  NULL,
   direccion         varchar(30) NULL,
   ciudad             varchar(30) NULL,
   telefono           integer     NULL,
   created            timestamp   NOT NULL,
   modified          timestamp   NULL,
   constraint pk_personas primary key (id)
);

De esta tabla se rescataran cada uno de los campos de la creación del registro por separados y a demás se obtendrá el día de la semana al que corresponde la fecha de creación del registro.

SELECT
 rut,
 extract(year from created)::int as anyo,
 extract(month from created)::int as mes,
 extract(day from created)::int as dia,
 extract(hour from created)::int as hour,
 extract(minute from created)::int as minuto,
 extract(second from created)::int as segundo,
 CASE extract(dow from created)
  WHEN 0 THEN 'Domingo'
  WHEN 1 THEN 'Lunes'
  WHEN 2 THEN 'Martes'
  WHEN 3 THEN 'Miercoles'
  WHEN 4 THEN 'Jueves'
  WHEN 5 THEN 'Viernes'
  WHEN 6 THEN 'Sabado'
 END
 as dow
FROM
 personas
;

Con esta consulta se obtiene toda la fecha de creación desglosada completamente, a demás permite obtener el día de la semana en que fue creado utilizando el CASE que permite seleccionar entre los valores el valor obtenidos y lo convierte en una cadena con el nombre del día.

extract(campo from fuente)

Recupera los valores de una fecha/hora, donde la fuente es un campo del tipo de dato fecha/hora, en este caso en particular un timestamp, mientras que el campo es el tipo de valor recuperado de la fecha, los campos existentes en las variables de fecha/hora son los siguientes:

• century: devuelve el siglo de la fecha, no existe siglo 0, solo existe desde el -1 al 1. Ej. el año 2000 es el siglo 20, mientras que el año 2001 es el siglo 21.
• day: devuelve el día del mes, va desde el 1 al 31 según el mes que corresponda.
• decade: divide el campo del año en 10. Ej. el año 2001 sería la decada 200.
• dow: devuelve el día de la semana que va desde 0 a 6, donde el 0 corresponde al domingo.
• doy: devuelve el día del año que va desde 1 al 365/366 según corresponda.
• epoch: devuelve el numero de segundos desde el 1970-01-01. Este valor puede ser negativo.También se puede utilizar para obtener el numero de segundos en un intervalo de tiempo. Ej. SELECT EXTRACT(EPOCH FROM INTERVAL'5 day 3 hours');
• hour: devuelve la hora de la fecha, este valor esta entre 0 y 23.
• microseconds: El tiempo en segundos, incluyendo la parte fraccionaria multiplicada por 1.000.000.
• millennium: devuelve el valor del milenio del año. Ej. el año 1990 es el milenio 2 y el año 2001 es el milenio 3.
• milliseconds: devuelve los segundos del campo tiempo multiplicado por 1.000.
• minute: devuelve los minutos del campo, este valor esta definido entre 0 y 59.
• month: Para valores timestamp, devuelve el numero del mes sin el año, los valores están definidos entre 0 y 12. También permite obtener intervalos de meses. Ej. SELECT EXTRACT (MONTH FROM INTERVAL '2 years 3 months');
• quarter: Divide el año en cuatro y devuelve valores entre 1 y 4, según el intervalo en que se encuentre el día en el año.
• second: devuelve la cantidad de segundos incluyendo la parte fraccionaria del campo.
• timezone: La zona horaria en UTC, medida en segundos, los valores positivos corresponden a la zona horaria este de UTC y los valores negativos a la zona horaria oeste de UTC.
• timezone_hour: La hora correspondiente de la zona horaria.
• timezone_minute: Los minutos correspondientes a la zona horaria.
• week: El número de la semana del año.
• year: devuelve el año de la fecha, no hay año 0, así que hay años antes de Cristo y después de Cristo.

"Gracias, por compartir tus conocimientos" 

PostgreSQL: Ingreso a la base de datos sin clave

El usuario de una base de datos postgres puede desear acceder al motor de base de datos sin tener que ingresar la clave.
Para lograr esto se debe crear el archivo ".pgpass" en el directorio home del usuario que desea ingresar sin la password.
El archivo debe contener líneas con el siguiente formato.

• hostname:port:database:username:password

En el archivo los campos descritos contienen la siguiente información:

• hostname -> debe escribir "localhost" si la base de datos se encuentra en el mismo equipo, de encontrarse en otro se debe escribir la ip del equipo o el nombre del equipo en la red.
• port -> se debe ingresar el numero del puerto de conexión al postgres, por defecto el puerto de conexión de postgres es "5432".
• database -> se debe escribir el nombre de la base de datos a la cual se desea ingresar.
• username -> se debe escribir el nombre de usuario con el cual se desea acceder a la base de datos.
• password -> se debe ingresar la password utilizada por el usuario para ingresar a la base de datos.

Este archivo debe tener estrictamente los permisos de lectura y escritura solo para el usuario y ningún permiso para el grupo u otros. Para revisar los permisos del archivo se puede ejecutar el siguiente comando.

• ls -la .pgpass

Esto entregara la siguiente línea como respuesta.

• -rw-------     1     nombreusuario     nombregrupo     tamaño     fechamodificacion     .pgpass

Si los permisos del archivo no están como se observa en la línea anterior, se debe utilizar el siguiente comando para cambiar los permisos del archivo.

• chmod 600 .pgpass

"Gracias, por compartir tus conocimientos"

PostgreSQL: Copiar resultado de un SELECT a un archivo

Existen ocasiones en las que es deseable pasar los resultados de un SELECt a un archivo, postgres permite que realices esta operación. Para esto debes ejecutar el siguiente comando en la consola del postgres.

• postgres=#copy (select * from nombre_tabla) to 'directorio_name/file_name';

Esto permite copiar todos los datos obtenidos en el select al archivo ubicado en el directorio seleccionado. Para que quede más claro se puede observar el siguiente ejemplo que es ejecutado en la consola de postgres en un equipo con linux instalado.

• postgres=#copy (select * from alumnos) to '/tmp/datos.txt';

IMPORTANTE:
Es importante recordar algunos comandos de postgres que permiten obtener ayuda.

• postgres=#\h => permite obtener ayuda de los comandos de postgres.
• postgres=#\help comando => permite obtener ayuda de un comando especifico de postgres.
• postgres=#\? => permite obtener ayuda de los comandos del cliente de postgres.

Antes de terminar debo aclarar que "postgres=#" indica que el comando es ejecutado en la consola del postgres.

"Gracias, por compartir tus conocimientos"

PostgreSQL: Modos de ingreso, creación de usuarios y base de datos

Después de instalar PostgreSQL en un equipo con Ubuntu se debe ingresar al sistema con un usuario y a una base de datos existentes en el motor de base de datos, para esto se puede utilizar el siguiente comando.

• sudo -u usuario psql base_de_datos

Como usuario se debe utilizar postgres y como base de datos postgres, esto permitirá al usuario ingresar al motor de base de datos sin necesidad de una clave más que la del superusuario.

Una vez que se ha ingresado se debe crear un usuario para trabajar con las bases de datos, esto se puede realizar con el siguiente comando.

• CREATE USER "usuario" WITH CREATEDB CREATEUSER PASSWORD 'password'

Como usuario se debe ingresar el nombre del usuario que se creará y como password la clave de ingreso del usuario, por ejemplo para crear como usuario el mismo de php se debe escribir lo siguiente.

• CREATE USER "www-data" WITH CREATEDB CREATEUSER PASSWORD 'www-data'

Para crear una base de datos que sea propiedad del usuario que se ha creado se debe utilizar el comando escrito a continuación.

• CREATE DATABASE nombre_base_datos WITH OWNER = "usuario"

Una vez que el usuario y la base de datos del mismo han sido creados se puede ingresar a PostgreSQL con el siguiente comando.

• psql -h localhost -U usuario nombre_base_datos

Luego el motor solicita la password ingresada para el usuario en el proceso de creación. Ahora puedes realizar todas las operaciones que desees con la base de datos que acabas de crear.

"Gracias, por compartir tus conocimientos"

PostgreSQL: Control de Secuencias

En PostgreSQL se pueden crear tablas con "primary key" autoincrementables, como se pude observar en el siguiente ejemplo.

create table alumnos (
   id        SERIAL       not null,
   nombre varchar(255) NULL,
   constraint pk_beneficios primary key (id));


Esto creara una secuencia autoincrementable para la "primary key" como se observa a continuación.

 id      integer        not null valor por omisión nextval('alumnos_id_seq'::regclass)

En ocaciones es necesario observar o modificar los valores de la secuencia, para realizar estas operaciones, se pueden utilizar las siguientes sentencias.

• "SELECT last_value FROM nombre_secuencia;": Retorna el último valor de la secuencia.
• "SELECT nextval('nombre_secuencia');": Retorna el valor del último número de la secuencia y lo incrementa en 1.
• "SELECT setval ('nombre_secuencia', valor);": Asigna el valor a la secuencia, obligando a nextval a retornar (valor + 1).
• "SELECT setval('nombre_secuencia',valor,true);": Funciona del mismo modo que la sentencia anterior.
• "SELECT setval('nombre_secuencia',valor,false);": Asigna el valor a la secuencia, obligando a nextval a retornar (valor).
• "SELECT currval('nombre_secuencia');": Retorna el valor del último número de la secuencia.

El nombre_secuencia puede ser remplazado por "alumno_id_seq" del primer ejemplo, mientras que el valor debe ser un entero mayor que 0 (cero).

"Gracias, por compartir tus conocimientos"