Saltycrane logo

SaltyCrane Blog

Notes on Python, Django, and web development on Ubuntu Linux

    

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

Give a user the privilege to create databases

  • su postgres
  • psql template1
  • List users 
    \du
  • ALTER USER my_username WITH CREATEDB;
  • \q
  • exit

See http://www.postgresql.org/docs/8.4/static/sql-alteruser.html

Post a comment
Required
Required, but not displayed
Optional

Format using Markdown. (No HTML.)
  • Code blocks: prefix each line by at least 4 spaces or 1 tab (and a blank line before and after)
  • Code span: surround with backticks
  • Blockquotes: prefix lines to be quoted with >
  • Links: <URL>
  • Links w/ description: [description](URL)
Created with Django | Hosted by Slicehost