PostgreSQL

Installation

EnterpriseDB has developed installers that simplify the Postgres Plus installation process, download it here.
Note on re-installation or upgrade [1]: Postgres runs under a special operating system user account for security reasons. This account is created on your machine when the installer runs, and unless overridden on the command line, it will be called "postgres". When you upgrade Postgres on Windows, in order to re-install the service the installer will require that the service password be re-entered correctly. If you have forgotten the service password, you can reset it on the command line with the following command:

net user postgres *

Other tools

phppgadmin a web-based administration tool for PostgreSQL. It is perfect for PostgreSQL DBAs, newbies and hosting services.This is the postgres version of the phpmyadmin available for mysql database.
To use it just unzip the files to the Apache public folder (generally "htdocs") and access "http://localhost/phpPgAdminFolder/index.php".
Note: Logins via phpPgAdmin with no password or certain usernames (pgsql, postgres, root, administrator) are denied by default. Before changing this behaviour (setting $conf['extra_login_security'] to false in the /conf/config.inc.php file) please read the PostgreSQL documentation about client authentication and understand how to change PostgreSQL's pg_hba.conf to enable passworded local connections.

Notes

Constraints

Check the constraints manual
Disallowing empty strings:
In postgres NULL is different from an empty string. So empty strings can still be inserted into "not null" field constraints. A way to disallow them is with:
```
create table COLECCAO (
ID_COLECCAO     INT4     not null,
NOME            TEXT     not null CHECK(NOME <> ''),
constraint PK_COLECCAO primary key (ID_COLECCAO)
);
```
In this example we also kept the "not null". If we remove the "not null" constraint, NULL values could still be inserted into the field but not empty strings.
A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression (learn more about CHECK constraint here). We can use all sort of functions inside the CHECK condition, for example we could achieve the same result has above with CHECK(char_length(NOME) > 0)

The above constraint could also be added to an existing table with:
```
ALTER TABLE coleccao ADD CHECK (nome <> '');
```

Data Types

Data types overview;

There are 3 different types of strings in postgres:

`character varying(n)`, `varchar(n)`	variable-length with limit
`character(n)`, `char(n)`	fixed-length, blank padded
`text`	variable unlimited length

Note than when using character(n) the strings will befilled with blank spaces to fill the strings expecified size!! Use one of the other 2 types if you dont want this.

Insert notes

check the insert section on the manual;
Getting the just created id:
```
insert into tipo_coleccao (id_tipo_coleccao, nome)
VALUES(nextval('id_tipo_coleccao'), null)
RETURNING id_tipo_coleccao;
```
Note: the RETURNING clause is a PostgreSQL extension (not standard sql).

Alternatively you can also use a RULE:
```
CREATE RULE get_id_on_insert AS ON
INSERT TO Customers 
DO SELECT currval('customers_id_seq') AS id;
```
Every time you insert to the Customers table, postgreSQL will return a table with the id you just inserted. No need to worry about concurrency, the ressource is locked when the rule gets executed.
Note that in cases of multiple inserts:
```
INSERT INTO C1 ( ... ) ( SELECT * FROM C2); 
```
we would return the id of the last inserted row.
If the RULE didn't work for you, you can remove it with:
```
DROP RULE get_id_on_insert ON Customers;
```
Learn more about RULE here.

Another possibility is using oid. Check the section "output" on the insert manual.

Modifying Tables

When you create a table and you realize that you made a mistake, or the requirements of the application change, then you can drop the table and create it again. But this is not a convenient option if the table is already filled with data, or if the table is referenced by other database objects (for instance a foreign key constraint). The Solution is to alter the table. The command ALTER TABLE lets you:

Add columns,
Remove columns,
Add constraints,
Remove constraints,
Change default values,
Change column data types,
Rename columns,
Rename tables.
learn more about it HERE and HERE.

NULL values

In postgres (unlike Oracle), empty strings are different from NULL.
If you want to transform '' into NULL you can use the NULLIF function where value2='' NULLIF(value1, value2)
The NULLIF function returns a null value if and only if value1 and value2 are equal. Otherwise it returns value1.Example:
```
insert into sometable (id, col1) 
VALUES(nextval('id'), NULLIF('somename',''))
RETURNING id;
```

COALESCE: The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. This is often useful to substitute a default value for null values when data is retrieved for display, for example:
```
SELECT COALESCE(description, short_description, '(none)') ...
```
Like a CASE expression, COALESCE will not evaluate arguments that are not needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated.

Selecting NULL values:

SELECT id, title FROM books WHERE title IS NULL;

Strings

String Functions and Operators

[1] - Postgres, Passwords and Installers
[2] - PostgreSQL Official manual: this is the main source of info for postgres. There is also a pdf version for download and the search box is a great way to quickly find the info you need online;
[3] - Manual de Referência do PostgreSQL 7.2

IT

Pages