PostgreSQL is an object-relational database management system (ORDBMS)
In database jargon, PostgreSQL uses a client/server model. A PostgreSQL session consists of the following cooperating processes (programs):
A server process, which manages the database files, accepts connections to the database from client applications, and performs database actions on behalf of the clients. The database server program is called postgres.
The user's client (frontend) application that wants to perform database operations. Client applications can be very diverse in nature: a client could be a text-oriented tool, a graphical application, a web server that accesses the database to display web pages, or a specialized database maintenance tool. Some client applications are supplied with the PostgreSQL distribution; most are developed by users.
To create a new database, in this example named mydb, you use the following command:
$ createdb mydb
you do not want to use your database anymore you can remove it. For example, if you are the owner (creator) of the database mydb, you can destroy it using the following command:$ dropdb mydbOn
ce you have created a database, you can access it by:
Running the PostgreSQL interactive terminal program, called psql, which allows you to interactively enter, edit, and execute SQL commands.
Using an existing graphical frontend tool like pgAdmin or an office suite with ODBC or JDBC support to create and manipulate a database
You probably want to start up psql to try the examples in this tutorial. It can be activated for the mydb database by typing the command:$ psql mydb
The psql program has a number of internal commands that are not SQL commands. They begin with the backslash character, "\". Some of these commands were listed in the welcome message. For example, you can get help on the syntax of various PostgreSQL SQL commands by typing:mydb=> \h
To get out of psql, type:mydb=> \qPostgreSQL is a relational database management system (RDBMS). That means it is a system for managing da
ta stored in relations. Relation is essentially a mathematical term for table. The notion of storing da ta in tables is so commonplace today that it might seem inherently obvious, but there are a number of other ways of organizing databases. Files and directories on Unix-like operating systems form an example of a hierarchical database. A more modern development is the object-oriented database.
You can create a new table by specifying the table name, along with all column names and their types:CREATE TABLE weather ( city varchar(80), temp_lo int, -- low temperature temp_hi int, -- high temperature prcp real, -- precipitation date date );
You can enter this into psql with the line breaks. psql will recognize that the command is not terminated until the semicolon.
varchar(80) specifies a da
ta type that can store arbitrary character strings up to 80 characters in length,int is the normal integer type. real is a type for storing single precision floating-point numbers. date should be self-explanatory. (Yes, the column of type date is also named date. This might be convenient or confusing — you choose.)
The second example will store cities and their associated geographical location:CREATE TABLE cities ( name varchar(80), location pointDROP TABLE tablename; );Constants that are not simple numeric values usually must be surrounded by single quotes (')