PostgreSQL

PostgreSQL is Article description::a free and open source relational database management system (RDBMS). It supports such things as transactions, schemata and foreign keys, and is often touted to more strictly adhere to the SQL standards and to be more secure, by default, than any other database, commercial or otherwise.

Visit the About page on postgresql.org for more information.

Installation, Upgrading, and Migration
See Gentoo PostgreSQL Quick Start Guide for details about installing, upgrading, or migrating PostgreSQL.

Add User
psql (9.1.1) Type "help" for help.

postgres=# CREATE ROLE username WITH LOGIN; CREATE ROLE postgres=# \password username Enter new password: Enter it again: postgres=# CREATE DATABASE testdb WITH OWNER username; -- username has all privileges on testdb CREATE DATABASE postgres=# GRANT CONNECT ON DATABASE otherdb TO username; -- username can now connect to otherdb GRANT postgres=# \c otherdb You are now connected to database "otherdb" as user "postgres". otherdb=# GRANT SELECT ON test TO username; -- username can now query (SELECT statements) the test table on otherdb. GRANT See [https://www.postgresql.org/docs/current/user-manag.html Chapter 20. Database Roles] of the official PostgreSQL documentation for more on role management.

See the PostgreSQL documentation for more on GRANT and REVOKE,

Changing the Default Encoding of New Databases
When creating a new database (e.g. with ) PostgreSQL actually copies a template database. There are two predefined templates: template0 is vanilla, while template1 is meant as an on-site template changeable by the administrator and is used by default. In order to change the default encoding of new databases, one of the options is to change on-site template1. To do this, log into PostgreSQL shell and execute the following:

1. First we need to drop template1. As templates cannot be dropped, we first need to change it to an ordinary database: UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1'; 2. After that, it is possible to drop it: DROP DATABASE template1; 3. The next step is to create a new database from template0 with a new default encoding. (Gotcha: In PostgreSQL, Unicode is synonymous with UTF-8.) CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE'; 4. Now we need to change template1 back to the template: UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1'; 5. (OPTIONAL) If you do not want anyone connecting to this template, set datallowconn to FALSE: UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';

Now you can create a new database by running from regular shell:

If you log in back to psql and check the databases, you should see the proper encoding of your new database:

psql (9.1.1) Type "help" for help.

postgres=# \l List of databases Name   |  Owner   | Encoding  | Collation | Ctype |   Access privileges --+--+---+---+---+-- testdb   | postgres | UTF8      | C         | C     | postgres | postgres | SQL_ASCII | C         | C     | template0 | postgres | SQL_ASCII | C        | C     | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8     | C         | C     |