PostgreSQL es una base de datos sql pero con muchas features que la hacen única, como la posibilidad de almacenar json de forma nativa sin pasarlo a texto plano.

En este tutorial exploraremos todas esas características de postgres, si aun no sabes mucho sobre bases de datos y sql te recomiendo mi tutorial de Tutorial de sql y Mysql.

Para instalar postgres esta es la pagina.

Si tienes alguna duda con la instalación, pregunta en mi twitter o deja un comentario con gusto te ayudare.

Comandos básicos

Todo el tutorial lo haremos con la terminal con psql, también existen versiones gráficas como pgadmin.

Estos son los comandos básicos:

• createdb nombre

Crea una base de datos con el nombre que se le especifique

• \h

Para mostrar comandos sobre el manejo de tablas y bases de datos

• \?

Muestra comandos de psql

• \q

Para salir de psql

• \l

Listar las bases de datos

• \c nombre

Seleccionar una base de datos

• \d table

Muestra una descripción de la tabla

Para el tutorial necesitamos crear una base de datos:

createdb tutorial;

Json

En postgres se puede guardar los archivos json de dos formas, una con todos los espacios en blanco que trae un documento y otra eliminando los espacios.

Esto sirve para optimizar el sistema y que sea mas rápido y ligera su lectura, la escritura suele ser un poco mas lenta porque tiene que quitarle los espacios en blanco, estas formas son json y jsonb.

Primero vamos a crear una tabla:

create table users(id serial primary key, name varchar(50), preferences json);

Aquí tenemos dos nuevos tipos de dato que postgres ofrece, el primero es serial, que es un tipo entero auto incrementable, el segundo es json(o podríamos poner jsonb). En el query estamos diciendo que la columna preferences sea del tipo json.

ahora para ingresar datos a esta tabla hacemos:

insert into users(name,preferences) values('vicente', '{"color": "blue", "settings": ["big","small","medium"] }');

hay que tener cuidado en usar comillas simples en lugar de comillas dobles, el json lo escribimos como en cualquier lenguaje de programación.

Si ejecutamos un:

select * from users;

Veremos nuestro json en la columna preferences.

Para manejar json, postgres nos da algunas funciones:

Si queremos extraer el valor de alguna llave de json:

select json_extract_path_text(preferences, 'color') from users;

o podríamos usar:

select preferences->> 'color' as color from users;

y para arrays:

select preferences#>>'{settings}' as settings from users;

podemos usar esto dentro de un query where

select * from users where preferences->>'color' = 'blue';

incluso podemos convertir archivos de la base de datos a json, supongamos que tenemos una tabla posts:

select row_to_json(posts) from posts;

este query convertirá estos datos a json, también podemos elegir que datos convertir a json, solo que el query es un poco largo:

select row_to_json(p) from(select id,title from posts limit 3) p;

lo que hace el query es tomar la referencia que deja el from p, que solo selecciona el id y el title de los posts y eso lo pasamos a la funcion row_to_json

Postgres nos ofrece muchas funciones para manejar archivos json, existen operadores que solo funcionan con jsonb, pero podemos pasar de json a jsonb con un cast simple:

select preferences::jsonb @> '{"color":"blue"}' as color from users;

Los :: indican que los datos de la izquierda pasaran a ser tipo jsonb, @> devuelve true si los datos de la izquierda contienen los datos que le pasemos a la derecha, nuestra columna preferences si contiene los datos color: blue.

Existe su contra parte <@ que verifica si los datos de la derecha contienen los de la izquierda.

select preferences::jsonb ? 'color' as color from users;

el operador ? devuelve true si existe la key que se le indique (color).

el operador ?| devuelve true si existe al menos una key que se le indique en el array

select preferences::jsonb ?| array['color', 'send'] from users;

por ultimo ?& devuelve true si todas las key que se le pasen en el array existen.

hay mucho mas funciones para datos json aqui puedes checar todas.

Hstore

Hstore funciona con relación llave:valor, es para almacenar datos no estructurados y es una extension que no viene activa en postgres, para activarla:

create extension hstore;

y agregaremos una nueva columna a nuestra tabla del tipo hstore:

alter table users add column profile hstore;

ahora llenamos un nuevo registro:

insert into users(name,profile) values('juan','age=>38, color=>red');

la forma de ingresar datos en hstore es => donde antes pones la llave y luego el valor.

para acceder a los datos es parecido a como lo hacemos con json solo que es diferente dependiendo el tipo de valor que tenga la llave:

select profile->'color' as color from users;

nos devuelve el valor de color, también podemos hacerlo con age

select profile->'age' as age from users;

podemos combinarlo con el query where:

select * from users where profile @> 'age=>38';

y con los operadores de json y jsonb.

Para agregar un dato a una columna de tipo hstore:

update users set profile = profile || 'city=>mexico'::hstore where id = 2 limit 1;

con esto estamos diciendo que agregue una nueva llave con su valor, nosotros lo escribimos como texto 'city=>mexico'::hstore y con ::hstore le hacemos un cast a tipo hstore

Para eliminar una llave, valor:

update users set profile = delete(profile, 'city');

Arrays

Para decirle a postgres que queremos un arreglo, solo tenemos que poner []: ages[].

Podemos pasar una string a un array con una función:

select string_to_array('5,6,7,8,9',',') as arr;

con esta función indicamos que queremos que el texto haga cast a un array por cada coma que encuentre.