postgres

Postgres dump all but one table

Snippet

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 SHOW CREATE TABLE equivalent

Snippet

Sometimes, you want to create a table outside of a database dump import. MySQL has "SHOW CREATE TABLE" and Postgres does not.

However, close enough.

# just the table create command
pg_dump --schema-only --table TABLENAME DATABASE_URL
 
# include all the indices and alter tables and such
pg_dump -st tablename dbname

Postgres grant create db permission

Snippet

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

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
$ 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