PostGres SQL

• Presented by :

Chandan , Markandey

Amit , Kiran

Harshada , Prachi

Introduction

• What is ‘ PostGres ‘ ?

• An overview on

Classes

Inferitance

Arrays

Geometric Types

Inheritance

• A class can inherit from zero or more other classes.

• A query can reference to either all instances of a class or all instances of a class plus all of its descendents.

• The inheritance hierarchy is a directed acyclic graph.

Inheritance ( Cont. )

• CREATE TABLE cities ( name text ,

population int,

altitude int ) ;

• CREATE TABLE capitals ( state char10 )

INHERITS ( cities ) ;

Arrays

• Valid for variable-length & fixed-length multi-dimensional arrays.

• Array with n elements start by array[1] and ends with array[n].

• CREATE TABLE sal_emp ( name text,

pay int4[ ],

schedule text[ ][ ] ) ;

Geometric Types

• Represent two dimensional spatial object.

• Points , Circle , Infinite Line , Rectangular Box etc.

• PostGres geometric type :

( x , y ) Point in Space

( (x1,y1) , (x2,y2)) Line

< ( x , y ) , r > Circle

CreateUser

• Name :

createuser - Create a new postgres user• Synopsis :

createuser [ options] [username]• Inputs :

- h : host. Host specifies the hostname of machine on which postmaster is running.

CreateUser ( Cont. )

- e : echo . Echo the queries that createdb generates and sends to the backend.

username : Specifies the name of the postgres user to be created .This name

must be unique among all postgres users.

CreateUser ( Cont .)

• Outputs :

CREATE USER

All is well

createuser : creation of user “username” failed.

Something went wrong . The user was not created.

CreateUser ( Cont. )

• Description :

• createuser creates a new PostGres user. Only users with usesuper set in the pg_shadow class can create new PostGres users.

CreateUser ( Cont. )

• Usage :

To create user joe on the default database

server.

$ createuser joe

Is the new user allowed to create databases? n

CREATE USER

DropUser

• Name :

dropuser - Drops (removes) a postgres user.

• Synopsis :

dropuser [ options] [username]

• Inputs :

- h : host .Host specifies the host name of machine on which postmaster is running.

DropUser ( Cont. )

• - e : echo . Echo the queries that createdb generates and sends to the backend.

• Username : Specifies the name of the postgres user to be removed. This name must exist in the postgres installation.

DropUser ( Cont. )

• Output :

DROP USER

All is well.

Dropuser : deletion of user “username” failed.

Something went wrong. The user was not removed.

DropUser ( Cont. )

• Description :

• dropuser removes an existing user and the databases which that user owned . Only users with usesuper set in the pg_shadow class can destroy postgres users.

DropUser ( Cont. )

• Usage :

to remove user joe from the default database server.

$ dropuser joe

DROP USER

Applications

• Createdb - Create a new PostGres database.

• Destroydb - Remove an existing PostGres database .

• Createlang : Add a new programming language to a postgres database.

• Droplang : Remove a programming language from a postgres database.

Pg_Dump

• Name :

pg_dump - Extract a PostGres database into a script file.

• Synopsis :

pg_dump [options] [dbname]

Cont.

• Inputs :

• dbname : Specifies the name of the database to be extracted. Dbname defaults to the value of the USER environment variable.

• -a : Dump out only the data, no schema.

• -c : Clean schema prior to create

Cont.

• Output

pg_dump will create a file or write to stdout

• Description

pg_dump is a utility for dumping out a postgres database into a script file containing query commands.

Cont.

• The script files are in text format and can be used to reconstruct database ,even on other machine and other architecture.pg_dump will produce the queries necessary to regenerate all user defined types ,functions,tables,indices,aggregates and operators.

Cont.

• Usage

To dump a database of the same name as the user:

$pg_dump > db.out

To reload this database:

$psql -e database < db.out