PostGres SQL

1
PostGres SQL

• Presented by
• Chandan , Markandey
• Amit , Kiran
• Harshada , Prachi

2
Introduction

• What is PostGres ?
• An overview on
• Classes
• Inferitance
• Arrays
• Geometric Types

3
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.

4
Inheritance ( Cont. )

• CREATE TABLE cities ( name text ,
• population int,
• altitude int )
• CREATE TABLE capitals ( state char10 )
• INHERITS (
  cities )

5
Arrays

• Valid for variable-length fixed-length
  multi-dimensional arrays.
• Array with n elements start by array1 and ends
  with arrayn.
• CREATE TABLE sal_emp ( name text,
• pay int4 ,
• schedule
  text )

6
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
• lt ( x , y ) , r gt Circle

7
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.

8
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.

9
CreateUser ( Cont .)

• Outputs
• CREATE USER
• All is well
• createuser creation of user username
  failed.
• Something went wrong . The user was not
  created.

10
CreateUser ( Cont. )

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

11
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

12
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.

13
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.

14
DropUser ( Cont. )

• Output
• DROP USER
• All is well.
• Dropuser deletion of user username failed.
• Something went wrong. The user was not removed.

15
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.

16
DropUser ( Cont. )

• Usage
• to remove user joe from the default database
  server.
• dropuser joe
• DROP USER

17
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.

18
Pg_Dump

• Name
• pg_dump - Extract a PostGres database into a
  script file.
• Synopsis
• pg_dump options dbname

19
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

20
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.

21
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,aggre
  gates and operators.

22
Cont.

• Usage
• To dump a database of the same name as the user
• pg_dump gt db.out
• To reload this database
• psql -e database lt db.out