1 / 12

Understanding PostgreSQL: The Advanced Object-Relational Database System

PostgreSQL is a powerful object-relational database management system (ORDBMS) originally based on POSTGRES, developed at the University of California, Berkeley. Established in 1986, it grew to be a fully featured database with extensive support for SQL92/99 dialects. PostgreSQL enables the use of various programming languages such as C, Python, and Perl for writing triggers and functions. It supports complex queries, transactions, and referential integrity, making it an excellent choice for developers and enterprises alike. Discover its features and commands for effective database management.

hamal
Télécharger la présentation

Understanding PostgreSQL: The Advanced Object-Relational Database System

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. What is PostgreSQL? • Object-relational database management system (ORDBMS) • Based on POSTGRES at Berkeley Computer Science Department. • Sponsored by the Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc. • Implementation of POSTGRES began in 1986. • It used a query language based on QUEL, called POST-QUEL

  2. Aimed first to be a fast database Uses SQL92 as its foundation Has simple mechanism for server-side libraries with C function and rudimentary support for triggers PostgreSQL MySQL • Aimed to be a fully-featured database • Understands a good subset of SQL92/99 dialects • Rules, triggers, server-side functions can be written in C, PgSQL, Python, Perl and TCL

  3. Does not support subqueries, stored procedures, subqueries, cursors or views Has basic provisions for referential integrity and transactions/rollbacks PostgreSQL MySQL • Supports subqueries, stored procedures, subqueries, cursors or views • Supports referential integrity, has transactions and rollbacks, foreign keys ON DELETE CASCADE and ON UPDATE CASCADE

  4. Works better on Windows Fast on both simple and complex SELECTs PostgreSQL MySQL • Doesn't have binary distribution for all the support platforms • Slower on low-end but has some options for improving

  5. Roles and Databases

  6. #!/bin/bash for student in $@ do echo "CREATE ROLE $student WITH LOGIN;" | psql echo "CREATE DATABASE $student with OWNER $student;" | psql done Roles and Databases

  7. Creation Alternatives • createdb • psql --command //can't mix of psql and sql • psql --file • psql < filename

  8. Other Command Line Options • --hostname • --username • --password //force password prompt • psql database username //default

  9. Environment Variables • PGDATABASE • PGHOST • PGPORT • PGUSER

  10. Useful Commands • \connect • \d [ pattern ] //list table, view, index, or sequence • \dn [ pattern ] //list schemas • \du [ pattern ] //list all roles • \i filename //excute from file • \l //list databases

  11. Useful Commands • \o [ {filename | |command} ] //Saves future query results to the file filename, standard output or pipes result to the Unix command • \s [ filename ] //Saves the history to file or to standard output • \set [ name [ value [ ... ] ] ] //Defines a variable • \! [ command ] //Executes command on a Unix shell • \? //Help • the “+”

  12. Sources • http://www.postgresql.org/docs/8.1 • psql man page • Google "Postgres versus MySQL"

More Related