Diferencias

Muestra las diferencias entre dos versiones de la página.

--- wiki2:postgresql [2018/04/13 14:22]
alfred [Make a role]
+++ wiki2:postgresql [2020/05/09 09:25] (actual)
@@ Línea 2: / Línea 2: @@
 ===== Basics =====
-''psql -U postgres'' enter into console with user postgres.
 ''\q'' exit.
+Know the postgresql version: ''SELECT version();''
+To explore the server:
+  * ''\list'' or ''\l'': list all databases
+  * ''\dt'': list all tables in the current 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 ====
 <code>
@@ Línea 45: / Línea 69: @@
 </code>
+==== Change user password ====
+<code>
+ALTER USER user_name WITH PASSWORD 'new_password';
+</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 =====
+==== Condicional CASE ====
+  * https://www.postgresql.org/docs/9.2/static/functions-conditional.html
+<code>
+SELECT * FROM test;
+ a
+---
+SELECT a,
+       CASE WHEN a=1 THEN 'one'
+            WHEN a=2 THEN 'two'
+            ELSE 'other'
+       END
+    FROM test;
+ a | case
+---+-------
+| one
+| two
+| other
+</code>
+<code>
+case when p.parent_id is null then false else true end as has_parent
+</code>
+==== CAST y tipos ====
+Crear un tipo:
+<code>
+CREATE TYPE temp_options_type AS (id integer, description text);
+</code>
+Castear:
+<code>
+CAST ( expression AS type )
+expression::type
+</code>
+==== Objetos anidados con array y row ====
+<code>
+create type inner_value as (id integer, name text);
+create view macarrodes.bookmarks as
+  select
+    ...
+    case
+      when t_bookmarks.folder is null then null
+      else ROW(t_bookmarks.folder, t_folders.name)::inner_value
+    end as folder,
+    array(
+        select
+          row(macarrodes.t_tags.id, macarrodes.t_tags.name)::inner_value
+        from
+          macarrodes.t_tags, macarrodes.t_bookmarks_tags
+        where
+          t_tags.id = t_bookmarks_tags.tag
+          AND t_bookmarks.id = t_bookmarks_tags.bookmark
+    ) as tags
+  from
+    macarrodes.t_bookmarks
+    ...
+</code>
+''ROW(t_bookmarks.folder, t_folders.name)::inner_value'' crea un objeto ''inner_value''.  Se ha de definir antes el tipo ''inner_value''.
+''array(...)'' crea un array de ''inner_value''s.
+También podrías coger una tabla entera como tipo:
+<code>
+select
+    dep.*,
+    array(
+        select row(emp.*)::emp from emp
+        where emp.depid = dep.depid
+    ) emps
+from dep
+</code>
+===== Notes =====
+==== Backups and tools ====
+There is a command to create a DB:
+<code>
+createdb -T template0 restored_database
+</code>
+To create a backup:
+<code>
+pg_dump postgres > postgres_db.bak
+</code>
+To restore a backup:
+<code>
+createdb -T template0 restored_database
+psql restored_database < database.bak
+</code>
+Backup and restore all DB:
+<code>
+pg_dumpall > backup_file
+</code>
+You can restore the databases by passing the file to psql, with the default database:
+<code>
+psql -f backup_file postgres
+</code>
+==== Tips and Tricks ====
+Script for restart and adding data using docker.
+<code>
+docker stop `cat last_docker_id.txt`
+docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword postgres:9.4 > last_docker_id.txt
+sleep 6
+export PGPASSWORD=mysecretpassword
+psql -h 127.0.0.1 -U postgres -f /home/alfred/Workspaces/macarrodes/pgsql/schema.pgsql
+psql -h 127.0.0.1 -U postgres -f /home/alfred/Workspaces/macarrodes/pgsql/fake_data.pgsql
+</code>
+=== Launch a command on CLI ===
+<code>
+export PGPASSWORD=mysecretpassword && psql -h 127.0.0.1 -p {db.local_port} -U postgres -c 'create database puma;'
+</code>
+==== Instalar servidor ====
+<code>
+apt-get install postgresql postgresql-contrib
+# Crear un cluster para 9.3
+pg_createcluster 9.3 main --start
+# Crear servicio
+update-rc.d postgresql enable
+# Activar servicio
+service postgresql start
+# Conecta correctamente?
+su postgres
+psql
+\q
+# Ctrl+d
+</code>
+Para permitir otras conexiones locales campiaremos
+<code>
+local   all             postgres                                peer
+</code>
+Por:
+<code>
+local   all             postgres                                md5
+</code>

Programming

Herramientas de usuario

Herramientas del sitio

Diferencias

Herramientas de la página