Postgres notes

Date: 2009-05-10 | Modified: 2009-08-01 | Tags: django, notestoself, sql

Here are some Postgres notes to myself.

How to add a column to an existing table

I'm going to add the "tags" column to the "product" table

cd /srv/HandsOnCards/handsoncards
python manage.py sqlall product

It returns (among other things):

CREATE TABLE "product_product" (
    "id" serial NOT NULL PRIMARY KEY,
    "site_id" integer NOT NULL REFERENCES "django_site" ("id") DEFERRABLE INITIALLY DEFERRED,
    "name" varchar(255) NOT NULL,
    "slug" varchar(80) NOT NULL,
    "sku" varchar(255) NULL,
    "short_description" text NOT NULL,
    "description" text NOT NULL,
    "items_in_stock" integer NOT NULL,
    "meta" text NULL,
    "date_added" date NULL,
    "active" boolean NOT NULL,
    "featured" boolean NOT NULL,
    "ordering" integer NOT NULL,
    "weight" numeric(8, 2) NULL,
    "weight_units" varchar(3) NULL,
    "length" numeric(6, 2) NULL,
    "length_units" varchar(3) NULL,
    "width" numeric(6, 2) NULL,
    "width_units" varchar(3) NULL,
    "height" numeric(6, 2) NULL,
    "height_units" varchar(3) NULL,
    "total_sold" integer NOT NULL,
    "taxable" boolean NOT NULL,
    "taxClass_id" integer NULL REFERENCES "tax_taxclass" ("id") DEFERRABLE INITIALLY DEFERRED,
    "shipclass" varchar(10) NOT NULL,
    "tags" varchar(255) NULL,
    UNIQUE ("site_id", "sku"),
    UNIQUE ("site_id", "slug")
)

su postgres
psql handsoncards_db

ALTER TABLE product_product ADD tags varchar(255) NULL;
\q

exit

See the Postgresql ALTER TABLE documentation for more information.

Create a new database

Create a database named django_db. Assume the user, django_user, has already been created.

su postgres
psql template1

CREATE DATABASE django_db OWNER django_user ENCODING 'UTF8';
\q

exit

Configure access to the database. Add the following line to /etc/postgresql/8.3/main/pg_hba.conf:
```
local   django_db   django_user                       md5
```
Restart the postgres server:
```
sudo /etc/init.d/postgresql-8.3 restart
```