Herramientas de usuario
wiki2:postgresql
Diferencias
Muestra las diferencias entre dos versiones de la página.
| Ambos lados, revisión anterior Revisión previa Próxima revisión | Revisión previa | ||
|
wiki2:postgresql [2018/12/31 12:45] alfred [Basics] |
wiki2:postgresql [2020/05/09 09:25] (actual) |
||
|---|---|---|---|
| Línea 10: | Línea 10: | ||
| * ''\dt'': list all tables in the current database | * ''\dt'': list all tables in the current database | ||
| * ''\connect database_name'' to switch database | * ''\connect database_name'' to switch database | ||
| + | |||
| + | To access to a recently installed postgres server: | ||
| + | <code> | ||
| + | su - postgres | ||
| + | pgsql | ||
| + | </code> | ||
| + | |||
| + | To add a password: ''\password'' | ||
| ==== Command-line utils ==== | ==== Command-line utils ==== | ||
| Línea 19: | Línea 27: | ||
| <code> | <code> | ||
| - | $ export PGPASSWORD=mysecretpassword && dropdb -h 127.0.0.1 -U postgres fok | + | $ export PGPASSWORD=mysecretpassword && dropdb -h 127.0.0.1 -U postgres fok |
| $ export PGPASSWORD=mysecretpassword && createdb -T template0 -h 127.0.0.1 -U postgres fok | $ export PGPASSWORD=mysecretpassword && createdb -T template0 -h 127.0.0.1 -U postgres fok | ||
| </code> | </code> | ||
| Línea 66: | Línea 74: | ||
| </code> | </code> | ||
| + | ===== JSONB ===== | ||
| + | You can create a table with json data: | ||
| + | <code> | ||
| + | create table macarrodes.bookmarks ( | ||
| + | id serial primary key, | ||
| + | title text, | ||
| + | meta jsonb | ||
| + | ); | ||
| + | </code> | ||
| + | |||
| + | ==== Query ==== | ||
| + | Simple query showing a field: | ||
| + | <code> | ||
| + | select distinct meta->'tags' from macarrodes.macarrodes.bookmarks; | ||
| + | </code> | ||
| + | |||
| + | Query by field: | ||
| + | <code> | ||
| + | SELECT * FROM users WHERE metadata @> '{"country": "Peru"}'; | ||
| + | SELECT * FROM users WHERE metadata->>'country' = 'Peru'; | ||
| + | </code> | ||
| + | |||
| + | Query nested values: | ||
| + | <code> | ||
| + | SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla"; | ||
| + | SELECT * FROM users WHERE metadata @> '{"company":{"name": "Mozilla"}}'; | ||
| + | </code> | ||
| + | |||
| + | Query if exists: | ||
| + | <code> | ||
| + | SELECT * FROM users WHERE metadata->>'country' IS NOT NULL; | ||
| + | </code> | ||
| + | |||
| + | Query in array: | ||
| + | <code> | ||
| + | SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}'; | ||
| + | SELECT * FROM users WHERE metadata->>'countries' IN ('Chad', 'Japan'); | ||
| + | </code> | ||
| + | |||
| + | Remove an attribute: | ||
| + | <code> | ||
| + | UPDATE users SET metadata = metadata - 'country'; | ||
| + | </code> | ||
| ===== Funciones ===== | ===== Funciones ===== | ||
wiki2/postgresql.1546260337.txt.gz · Última modificación: 2020/05/09 09:25 (editor externo)
Herramientas de la página
Excepto donde se indique lo contrario, el contenido de este wiki esta bajo la siguiente licencia: CC Attribution-Noncommercial-Share Alike 4.0 International
