postgresql

View enums in postgres

Snippet Instead of being asleep at 11:43 on 25 May 2022, kitt created this:

enums help verify data is valid in the database by limiting what specific values can be saved to particular fields. Yay!

Viewing them, however, if you don't know what they are, isn't obvious. Use enum_range and unnest

development=> \d authors
                                          Table "public.authors"
   Column    |              Type              | Collation | Nullable |               Default               
-------------+--------------------------------+-----------+----------+-------------------------------------
 id          | bigint                         |           | not null | nextval('authors_id_seq'::regclass)
 email       | character varying              |           |          | 
 author_role | author_role                    |           | not null | 'external'::author_role
 name        | character varying              |           |          | 
...
 
development=> SELECT enum_range(NULL::author_role)
development-> ;
      enum_range       
-----------------------
 {external,fte,intern}
(1 row)
 
development=> SELECT unnest(enum_range(NULL::author_role));
  unnest  
----------
 external
 fte
 intern
(3 rows)

Postgres dump all but one table

Snippet kitt decided around 14:07 on 30 December 2019 to publish this:

Those versions and logs tables? You don't really want them in the dump. So, don't include them, use --exclude-table.

pg_dump --exclude-table=TABLE_TO_EXCLUDE $DATABASE_URL

Postgres grant create db permission

Snippet Instead of being asleep at 11:46 on 24 September 2019, kitt created this:

Let a user create a db.

kitt=# \du 
                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 kitt        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 burble_dev  |                                                            | {}

kitt=# alter user burble_dev createdb;
ALTER ROLE
kitt=# \du 
                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 kitt        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 burble_dev  | Create DB                                                  | {}

ALTER USER username CREATEDB;

Postgres output query to file

Snippet Yeah, kitt finished writing this at 12:34 on 1 September 2019

Ways to output the results of a Postgres query to a file:

  1. Use the command line switch options -c to input the query and -o to redirect the output to a file:
    % psql -U user -W -c "SELECT id, name FROM person WHERE name like '%smith%'" -o smiths.txt database
  2. Use the query construct \g 'filename' to write the results to file from inside the Postgres command line interface:
    % psql -U user -W database
database=> SELECT id, name FROM person WHERE name like '%smith%' \g '/tmp/smiths.txt' ;
Query OK, 10 rows affected (0.01 sec)

    If you want to add to an existing file, use cat:

    % psql -U user -W database
database=> SELECT id, name FROM person WHERE name like '%smith%' \g | cat >> '/tmp/smiths.txt' ;
Query OK, 10 rows affected (0.01 sec)

See also, the mysql versions.

Be sure where you have write permissions where the output file will be written to. Specify the full path if in doubt.

SELECT id, name FROM person WHERE name like '%smith%' \g | cat >> '/tmp/smiths.txt';

Postgresql Add UTF8 database

Snippet Written with a loving hand by kitt some time around 14:50 on 24 August 2016 
$ createdb -E UTF8 -T template0 --locale=en_US.UTF-8 <name>
 
# to figure out actual locale value (sometimes listed as utf8), or UTF8, use locale
$ locale -a | grep en