Today I learned@Cybertec

Posts tagged "postgres"

docker-compose for postgres with db init script

Single script

postgres:
  image: postgres
  volumes:
    - ./init.sql:/docker-entrypoint-initdb.d/init.sql

Multiple scripts

Multiple scripts run in alphabetical order, thus it's good practice to prefix them with a number.

volumes:
  - ./schema.sql:/docker-entrypoint-initdb.d/1-schema.sql
  - ./data.sql:/docker-entrypoint-initdb.d/2-data.sql

Directory containing scripts

volumes:
  - ./init-scripts:/docker-entrypoint-initdb.d

Convert JSON to table type

The function json_populate_record can be used to cast a JSON object to a table type:

CREATE TABLE foo(bar int);

SELECT  *
FROM    json_populate_record(NULL::foo, '{"bar": 42}')
+-------+
| bar   |
|-------|
| 42    |
+-------+

This can also be used to insert into the table:

INSERT INTO foo
SELECT *
FROM   json_populate_record(NULL::foo, '{"bar": 42}')

Get granted roles with psql

With psql, there is an easy way to check which roles are granted to a role: \du

henk=> CREATE ROLE accounting;
CREATE ROLE
henk=> CREATE ROLE alex;
CREATE ROLE
henk=> GRANT accounting TO alex;
GRANT ROLE
henk=> \du accounting
             List of roles
 Role name  |  Attributes  | Member of
------------+--------------+-----------
 accounting | Cannot login | {}

henk=> \du alex
              List of roles
 Role name |  Attributes  |  Member of
-----------+--------------+--------------
 alex      |              | {accounting}