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/28 13:13] alfred [Tips and Tricks] |
wiki2:postgresql [2020/05/09 09:25] (actual) |
||
|---|---|---|---|
| Línea 2: | Línea 2: | ||
| ===== Basics ===== | ===== Basics ===== | ||
| - | ''psql -U postgres'' enter into console with user postgres. | ||
| - | |||
| - | ''psql -U postgres -h 127.0.0.1'' for doing by TCP. | ||
| - | |||
| - | ''export PGPASSWORD=mysecretpassword && psql -h 127.0.0.1 -U postgres'' using a password. | ||
| - | |||
| ''\q'' exit. | ''\q'' exit. | ||
| Línea 17: | Línea 11: | ||
| * ''\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 ==== | ||
| + | ''psql -U postgres'' enter into console with user postgres. | ||
| + | |||
| + | ''psql -U postgres -h 127.0.0.1'' for doing by TCP. | ||
| + | |||
| + | ''export PGPASSWORD=mysecretpassword && psql -h 127.0.0.1 -U postgres'' using a password. | ||
| + | |||
| + | <code> | ||
| + | $ 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 | ||
| + | </code> | ||
| ==== Create schema ==== | ==== Create schema ==== | ||
| <code> | <code> | ||
| Línea 61: | 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.1546002780.txt.gz · Última modificación: 2020/05/09 09:24 (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
