# PostgreSQL Notes

# PostgreSQL Notes
## General commands

```sql
\l -- list databases
\c <db> -- connect to database
\dt -- list tables
\d <table> -- show columns of a table
\du -- list user roles
\h -- show help
```

## ANALYZE and VACUUM

```
ANALYZE; -- Analyze all tables in current database
VACUUM; -- Vacuum all tables

-- Show relevant timestamps with regard to analyze and Vacuum
SELECT relname, last_vacuum, last_analyze, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables;
-- Show dead tuples
SELECT relname, n_dead_tup FROM pg_stat_user_tables;
```

Activate autovacuum in postgresql.conf

```
track_counts = on
autovacuum = on
```

## Change encoding of 'template1'

On a UTF-8 setup you might still require a LATIN1 database template because your supplier tells you so. Most likely you don't want this - but if you do, that's what you need to do:

```
UPDATE pg_database SET datistemplate=false WHERE datname='template1';
DROP DATABASE template1
CREATE DATABASE template1 WITH OWNER=postgres ENCODING = 'LATIN1' LC_CTYPE = 'POSIX' LC_COLLATE = 'POSIX' TEMPLATE = 'template0';
UPDATE pg_database SET datistemplate=true WHERE datname='template1';
```

## Migrating a database to another encoding

Your only option is to dump the database and recreate it. Here is what I did when I had to migrate from UTF-8 to LATIN1. First dupm the database from a shell

```
psql_dump <db_name> > <db_name>.sql
```

Then move the database and recreate it with the correct settings

```
ALTER DATABASE <db_name> RENAME TO <db_name>_backup;
CREATE DATABASE <db_name> ENCODING = 'LATIN1' LC_CTYPE = 'POSIX' LC_COLLATE='POSIX' TEMPLATE template1;

```

Then restore the dump, setting the client to the old encoding

```
PGCLIENTENCODING=UTF8 psql -f <db_name>.sql <db_name>
```

If everything is successful drop the backup of the database

```
DROP DATABASE <db_name>_backup;
```