Transcript PostGres SQL - CSE, IIT Bombay

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