1 / 22

PostGres SQL

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.

otis
Download Presentation

PostGres SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 array[1] and ends with array[n]. • 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 < ( x , y ) , r > 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,aggregates and operators.

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

More Related