TNick.github.io

home

PostgreSql

09 Apr 2014

Overview

The PostgreSql is advertised as "The world's most advanced open source database". In Ubuntu, it can be installed using:

sudo apt-get install postgresql postgresql-contrib postgresql-client

To interact at the command line use:

psql -U postgres -h localhost

An database may be created as simple as:

CREATE DATABASE dbname WITH OWNER = postgres
  WITH OWNER = postgres
  ENCODING = 'UTF8'
  TABLESPACE = pg_default
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8'
  CONNECTION LIMIT = -1;

Listing databases is done using \l and connecting with \c dbnae. \dt will list tables in current database. \d+ tablename will list the columns for that table.

Create a table with:

CREATE TABLE IF NOT EXISTS tblex (
    id         NUMERIC NOT NULL PRIMARY KEY,
    name       TEXT NOT NULL,
    age        NUMERIC,
    height     NUMERIC(5, 2)
);
ALTER TABLE 'tblex' 
  OWNER TO postgres;

And then delete it with:

DROP TABLE tblex;

Deleting individual rows where a condition is satisfied:

DELETE FROM tblex WHERE id=1;
DELETE FROM tblex WHERE id!=1;

Tagged with walkthrough

"Any sufficiently advanced troll is indistinguishable from a genuine kook." Alan Morgan