logo

Thông báo

Icon
Error

Chia sẻ
Tùy chọn
Xem
Xem bài viết cuối
Offline admin  
#1 Đã gửi : 16/11/2013 lúc 01:33:53(UTC)
admin

Danh hiệu: Administration

Chức danh:

Nhóm: Administrators
Gia nhập: 23-07-2013(UTC)
Bài viết: 6,117
Man
Viet Nam
Đến từ: Vietnam

Cảm ơn: 10 lần
Được cảm ơn: 2 lần trong 2 bài viết
PostgreSQL

Databases are indispensable when it comes to contemporary web applications. True - there are many personal static web pages on the Internet working without databases, but in today's dynamic information stream static pages are archaic. Dynamic content is starting to replace static content even on personal pages. When it comes to corporate business sites the presence of databases is a mandatory element.

Building a website which uses databases has many advantages. The whole content is structured, easily accessible and editable, and can be transferred or exported to another web application or a newer version of the latter. And while most of the amateur, non-professional, or open source projects use the light, quick and free MySQL databases, the professional web environments count on expensive powerful databases offering a higher level of security such as Oracle, MsSQL, Sybase. And as a free alternative to powerful corporate applications comes PostgreSQL.

Table of contents :

PostgreSQL's history

PostgreSQL features

Objects defined by the user

MVCC

Database triggers

Indexes

Data types

Cross platform

Extensible

Enterprises using PosgreSQL

PostgreSQL with NTC Hosting

PostgreSQL's history

PostgreSQL's origins lead us back to the 1970s. Back then, at the University of Berkeley, California, a new database software was created - Ingres. This software became the foundation of other popular database solutions, such as Sybase, MsSQL and NonStop SQL. However, in 1985, the leader of the Ingres project started a new, post-Ingres project, with which he aimed to address the database problems at the time. The new project was based on the ideas used in Ingres, but not on its source code. In the following years the project was completed and several versions of the Postgre database were released. Back then, it was not based on SQL, but on the QUEL query language. The last version using the QUEL interpreter was released in 1993.

In 1994, two Berkeley graduates converted Postgre to use the SQL interpreter instead of the QUEL one and labeled their version Postgre95. Since then, the SQL version gained huge popularity and in 1996 the project was officially renamed PostgreSQL.

PostgreSQL was licensed under the BSD license, which allows for the product to be modified and then distributed commercially. Due to this fact, there are a lot of companies, which offer commercial distributions of the PostgreSQL software, alongside with the free version.

PostgreSQL features

Objects defined by the user

The user can create new types of almost all the objects inside the database:

Conversions

Data types

Domains

Functions, including aggregate functions

Indexes

Operators

MVCC

MVCC stands for Multi-Version Concurrency Control - it allows the user to make changes to the database, which will remain invisible to others until the transaction is commuted. This way, the need for read locks is eliminated. Usually, the reader of the database must wait for the writer to finish and vice versa, since most database software applications rely on table-level, page-level, column-level, or row-level locking.

In PostgreSQL, each row in a table has two transaction IDs - a creation and an expiration transaction ID for the transactions that created and expired the row. When there is an UPDATE, PostgreSQL creates a new row and expires the old one. The new row is the same as the old one, just a different version.

MVCC also gives another edge to PostgreSQL - the ability to create live backups of the database, i.e. it can create backups for tables which are used at that moment - no matter if data is inserted, deleted or updated at the moment of the backup. With a database software application lacking MVCC, the table will have to be locked before a backup is created.

Database triggers

Database triggers are events, executed when a specific command is entered in a table or a database. They are most often used to verify input data or to restrict access. There are several classes of triggers - "BEFORE" and "AFTER" triggers, depending on the time of the trigger execution. There is also the "INSTEAD OF" trigger, which will execute in place of the triggering statement.

Mostly, triggers are used with the UPDATE, DELETE or INSERT statements.

An example of How to Create Table in PostgreSQL

Trích dẫn:
CREATE TABLE phonebook(phone VARCHAR(32), firstname VARCHAR(32), lastname VARCHAR(32), address VARCHAR(64));

An example of how to Insert data into PostgreSQL table

Trích dẫn:
INSERT INTO phonebook(phone, firstname, lastname, address) VALUES('+1 123 456 7890', 'John', 'Doe', 'North America');

An example of how to Select data from PostgreSQL table

Trích dẫn:
SELECT * FROM phonebook ORDER BY lastname;

Trích dẫn:
SELECT * FROM phonebook WHERE lastname = 'Doe';

Trích dẫn:
SELECT * FROM phonebook WHERE firstname = 'John' AND lastname = 'Doe';

An example of how to Update data in PostgreSQL database

Trích dẫn:
UPDATE phonebook SET address = 'North America', phone = '+1 123 456 7890' WHERE firstname = 'John' AND lastname = 'Doe'; (updates the address and the telephone number of all persons named John Doe)

An example of how to Delete data from PostgreSQL database

Trích dẫn:
DELETE FROM phonebook WHERE firstname = 'John' AND lastname = 'Doe';

Indexes

In PostgreSQL, the developers can enjoy the built-in support for several types of indexes: B+, hish, GiST and GiN. Additionally, everybody can create their own indexing method. In PostgreSQL, the indexes support several additional features:

backward index scanning

PostgreSQL can go through multiple indexes at the same time to satisfy a query

the user can create partial indexes using the WHERE statement when creating a new table. This significantly reduces the index size.

PostgreSQL allows the creation of expression indexes with an index of the result of an expression or function. The creation of expression indexes is not limited to the value of the columns.

Data types

PostgreSQL supports several native data types:

arrays

variable length text

IPv4 and IPv6 addresses

XML with support for Xpath queries

Besides the native data types, PostgreSQL allows the users to create their own data types, which are later indexed using the GiST indexing type.

PosgreSQL Data types

Name Aliases Description

bigint int8 signed eight-byte integer

bigserial serial8 autoincrementing eight-byte integer

bit [ (n) ] fixed-length bit string

bit varying [ (n) ] varbit variable-length bit string

boolean bool logical Boolean (true/false)

box rectangular box in the plane

bytea binary data (“byte array”)

character varying [ (n) ] varchar [ (n) ] variable-length character string

character [ (n) ] char [ (n) ] fixed-length character string

cidr IPv4 or IPv6 network address

circle circle in the plane

date calendar date (year, month, day)

double precision float8 double precision floating-point number

inet IPv4 or IPv6 host address

integer int, int4 signed four-byte integer

interval [ (p) ] time span

line infinite line in the plane

lseg line segment in the plane

macaddr MAC address

money currency amount

numeric [ (p, s) ] decimal [ (p, s) ] exact numeric of selectable precision

path geometric path in the plane

point geometric point in the plane

polygon closed geometric path in the plane

real float4 single precision floating-point number

smallint int2 signed two-byte integer

serial serial4 autoincrementing four-byte integer

text variable-length character string

time [ (p) ] [ without time zone ] time of day

time [ (p) ] with time zone timetz time of day, including time zone

timestamp [ (p) ] [ without time zone ] date and time

timestamp [ (p) ] with time zone timestamptz date and time, including time zone

Cross platform

Another great advantage of the PostgreSQL platform is that it is available on almost any operating system in use. It has a native support for all UNIX environments and in the latest version native support for Microsoft's Windows has been included. This allows for an easy migration between systems, no matter how they are set up.

Extensible

PostgreSQL is a completely free, open source-based database. Its license allows everyone to make modifications to the core functionality, adding new or improving the existing features. Thanks to this, PostgreSQL has a very big community of users, who modify it on a daily basis. PostgreSQL also allows commercial versions to be released, and there are several companies that have made a modified commercial version of the PostgreSQL software.

Ai đang xem chủ đề này?
OceanSpiders 2.0
Chủ đề tương tự
Display only table names and schema in PostgreSQL (PostgreSQL)
Bởi admin 18-09-2019 lúc 09:22:00(UTC)
Connect To a PostgreSQL Database Server (PostgreSQL)
Bởi admin 04-09-2019 lúc 12:11:22(UTC)
Install PostgreSQL (PostgreSQL)
Bởi admin 04-09-2019 lúc 12:07:51(UTC)
PostgreSQL - hệ quản trị cơ sở dữ liệu được SEITO POS lựa chọn (PostgreSQL)
Bởi admin 16-11-2013 lúc 02:12:39(UTC)
PostgreSql SELECT (PostgreSQL)
Bởi admin 16-11-2013 lúc 02:09:43(UTC)
PostgreSql DELETE (PostgreSQL)
Bởi admin 16-11-2013 lúc 01:50:29(UTC)
PostgreSql UPDATE (PostgreSQL)
Bởi admin 16-11-2013 lúc 01:48:04(UTC)
PostgreSQL INSERT (PostgreSQL)
Bởi admin 16-11-2013 lúc 01:43:13(UTC)
PostgreSQL configure database connection (PostgreSQL)
Bởi admin 16-11-2013 lúc 10:54:39(UTC)
PostgreSQL installer exits with a runtime installation error? or Can't find script engine "VBScript" (PostgreSQL)
Bởi admin 16-11-2013 lúc 10:45:26(UTC)
OpenERP all in one windows : can’t re-install PostgreSQL (PostgreSQL)
Bởi admin 16-11-2013 lúc 10:35:57(UTC)
PostgreSQL Windows Installation Problem. Database Cluster Initialisation Failed (PostgreSQL)
Bởi admin 16-11-2013 lúc 10:30:07(UTC)
Di chuyển  
Bạn không thể tạo chủ đề mới trong diễn đàn này.
Bạn không thể trả lời chủ đề trong diễn đàn này.
Bạn không thể xóa bài của bạn trong diễn đàn này.
Bạn không thể sửa bài của bạn trong diễn đàn này.
Bạn không thể tạo bình chọn trong diễn đàn này.
Bạn không thể bỏ phiếu bình chọn trong diễn đàn này.

| Cung cấp bởi YAF.NET 2.2.4.14 | YAF.NET © 2003-2021, Yet Another Forum.NET
Thời gian xử lý trang này hết 0.420 giây.