Postgresql foundation for beginner

Install postgresql on ubuntu linux

root@ubuntu:# apt-get install postgresql postgresql-client postgresql-contrib

Change local authentication

• psql: FATAL: Ident authentication failed for user “postgres”

If you can the above error, you need to change the from Ident to md5, here the step you need to follow:

root@htvlc:/etc/postgresql/9.0/main# ls
environment  pg_ctl.conf  pg_hba.conf  pg_ident.conf  postgresql.conf  start.conf
root@htvlc:/etc/postgresql/9.0/main# vim pg_hba.conf 

Change to

local   all         postgres                          md5

user name to access postgresql database

By default postgres is the admin user created for postgresql server.

Change the admin password

root@ubuntu:# su postgres -c psql template1
psql (8.4.10)
Type "help" for help.
postgres=# ALTER USER postgres WITH PASSWORD 'caojiang';
ALTER ROLE
postgres=# \q

Change or reset the postgres admin password for the operating system

This changes the password for the user postgres in the OS (NOT in Postgresql)

root@ubuntu:~# passwd -d postgres
passwd: password expiry information changed.
root@ubuntu:~# su postgres -c passwd
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully

Create Database for postgresql

postgres@ubuntu:~$ psql template1
psql (8.4.10)
Type "help" for help.

template1=# CREATE DATABASE myworld;

CREATE DATABASE
template1=# SELECT datname from pg_database;
  datname   
------------
 template1
 template0
 postgres
 moodle
 test2
 myworld
(6 rows)

template1=# 

Delete database

template1=# DROP DATABASE IF EXISTS myworld;
DROP DATABASE
template1=# 

Create Table

You need to create a database test2 before you can start with this tutorial.

postgres@ubuntu:~$ psql test2;
psql (8.4.10)
Type "help" for help.

test2=# CREATE TABLE my_table
test2-# (
test2(# id INT NOT NULL PRIMARY KEY,
test2(# name VARCHAR(200) NOT NULL);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "my_table_pkey" for table "my_table"
CREATE TABLE
test2=# 

Drop or delete table

test2=# DROP TABLE my_table;
DROP TABLE
test2=# 

Rename Table using ALTER

Make sure, you have the mytime table before you can rename it.

test2=# ALTER TABLE mytime RENAME TO ourtime;
ALTER TABLE
test2=# \d ourtime;
           Table "public.ourtime"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 id     | integer                | not null
 name   | character varying(200) | not null
 time   | bigint                 | not null
Indexes:
    "mytime_pkey" PRIMARY KEY, btree (id)

Show All the database in Postgresql

This function statement lists all the databases in your Postgresql system.

test2=# select datname from pg_database;
  datname   
------------
 template1
 template0
 moodle
 postgres
 test2
(4 rows)

How to use or change the database

Exit the current database and reconnect with a new database that you want to use.

test2-# ^Z
[2]+  Stopped                 psql test2
postgres@ubuntu:~$ psql moodle;
psql (8.4.10)
Type "help" for help.

moodle=# 

How to list all the tables in the database

postgres@ubuntu:~$ psql test2;
psql (8.4.10)
Type "help" for help.

test2=# \dt
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | mytesttime | table | postgres
 public | mytime     | table | postgres
(2 rows)

List table structure and table data type

test2-# \d mytesttime
 id     | integer                | not null
 name   | character varying(200) | not null
 time   | bigint                 | not null

List datatypes

\dT

List Functions

\df

List Indexes

\di

test2-# \di 
 public | mytesttime_pkey | index | postgres | mytesttime
 public | mytime_pkey     | index | postgres | mytime

List Views

\dv

Summary

Postgresql Command Summary
Command			Detail
--------------------------------------------
\d

\dt			List all tables

\di			List all indexes

\ds			List all sequences

\dv			List all views

\dS			List all PostgreSQL-defined tables

\d table-name		Show table definition

\d index-name		Show index definition

\d view-name		Show view definition

\d sequence-name	Show sequence definition

\dp			List all privileges

\dl			List all large objects

\da			List all aggregates

\df			List all functions

\df function-name	List all functions with given name

\do			List all operators

\do operator-name	List all operators with given name

\dT			List all types

\l			List all databases in this cluster