PostgreSQL7.2Tutorial
The PostgreSQL Global Development Group
PostgreSQL7.2Tutorial
by The PostgreSQL Global Development Group
Copyright?1996-2001by The PostgreSQL Global Development Group
Legal Notice
PostgreSQL is Copyright?1996-2001by the PostgreSQL Global Development Group and is distributed under the terms of the license of the University of California below.
Postgres95is Copyright?1994-5by the Regents of the University of California.
Permission to use,copy,modify,and distribute this software and its documentation for any purpose,without fee,and without a written agreement is hereby granted,provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT,INDIRECT,SPECIAL, INCIDENTAL,OR CONSEQUENTIAL DAMAGES,INCLUDING LOST PROFITS,ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION,EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,INCLUDING,BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.THE SOFTWARE PRO-VIDED HEREUNDER IS ON AN“AS-IS”BASIS,AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,SUPPORT,UPDATES,ENHANCEMENTS,OR MODIFICATIONS.
Table of Contents
Welcome (iv)
Preface (v)
1.What is PostgreSQL? (v)
2.A Short History of PostgreSQL (v)
2.1.The Berkeley POSTGRES Project (vi)
2.2.Postgres95 (vi)
2.3.PostgreSQL (vii)
3.Documentation Resources (vii)
4.Terminology and Notation (viii)
5.Bug Reporting Guidelines (ix)
5.1.Identifying Bugs (ix)
5.2.What to report (x)
5.3.Where to report bugs (xi)
6.Y2K Statement (xii)
1.Getting Started (1)
1.1.Installation (1)
1.2.Architectural Fundamentals (1)
1.3.Creating a Database (2)
1.4.Accessing a Database (3)
2.The SQL Language (5)
2.1.Introduction (5)
2.2.Concepts (5)
2.3.Creating a New Table (5)
2.4.Populating a Table With Rows (6)
2.5.Querying a Table (7)
2.6.Joins Between Tables (8)
2.7.Aggregate Functions (10)
2.8.Updates (12)
2.9.Deletions (12)
3.Advanced Features (14)
3.1.Introduction (14)
3.2.Views (14)
3.3.Foreign Keys (14)
3.4.Transactions (15)
3.5.Inheritance (16)
3.6.Conclusion (18)
Bibliography (19)
Index (21)
Welcome
Welcome to PostgreSQL and the PostgreSQL Tutorial.The following few chapters are intended to
give a simple introduction to PostgreSQL,relational database concepts,and the SQL language to
those who are new to any one of these aspects.We only assume some general knowledge about how
to use computers.No particular Unix or programming experience is required.
After you have worked through this tutorial you might want to move on to reading the User’s Guide
to gain a more formal knowledge of the SQL language,or the Programmer’s Guide for information
about developing applications for PostgreSQL.
We hope you have a pleasant experience with PostgreSQL.
Preface
1.What is PostgreSQL?
PostgreSQL is an object-relational database management system(ORDBMS)based on POSTGRES,
Version4.21,developed at the University of California at Berkeley Computer Science Department.
The POSTGRES project,led by Professor Michael Stonebraker,was sponsored by the Defense Ad-
vanced Research Projects Agency(DARPA),the Army Research Of?ce(ARO),the National Science
Foundation(NSF),and ESL,Inc.
PostgreSQL is an open-source descendant of this original Berkeley code.It provides SQL92/SQL99
language support and other modern features.
POSTGRES pioneered many of the object-relational concepts now becoming available in some com-
mercial databases.Traditional relational database management systems(RDBMS)support a data
model consisting of a collection of named relations,containing attributes of a speci?c type.In current
commercial systems,possible types include?oating point numbers,integers,character strings,money,
and dates.It is commonly recognized that this model is inadequate for future data-processing appli-
cations.The relational model successfully replaced previous models in part because of its“Spartan
simplicity”.However,this simplicity makes the implementation of certain applications very dif?cult.
PostgreSQL offers substantial additional power by incorporating the following additional concepts in
such a way that users can easily extend the system:
?inheritance
?data types
?functions
Other features provide additional power and?exibility:
?constraints
?triggers
?rules
?transactional integrity
These features put PostgreSQL into the category of databases referred to as object-relational.Note
that this is distinct from those referred to as object-oriented,which in general are not as well suited
to supporting traditional relational database languages.So,although PostgreSQL has some object-
oriented features,it is?rmly in the relational database world.In fact,some commercial databases
have recently incorporated features pioneered by PostgreSQL.
2.A Short History of PostgreSQL
The object-relational database management system now known as PostgreSQL(and brie?y called
Postgres95)is derived from the POSTGRES package written at the University of California at Berke-
ley.With over a decade of development behind it,PostgreSQL is the most advanced open-source
database available anywhere,offering multiversion concurrency control,supporting almost all SQL 1.https://www.doczj.com/doc/9911946115.html,:8000/postgres/postgres.html
constructs(including subselects,transactions,and user-de?ned types and functions),and having a
wide range of language bindings available(including C,C++,Java,Perl,Tcl,and Python).
2.1.The Berkeley POSTGRES Project
Implementation of the POSTGRES DBMS began in1986.The initial concepts for the system were
presented in The design of POSTGRES and the de?nition of the initial data model appeared in The
POSTGRES data model.The design of the rule system at that time was described in The design of the
POSTGRES rules system.The rationale and architecture of the storage manager were detailed in The
design of the POSTGRES storage system.
Postgres has undergone several major releases since then.The?rst“demoware”system became op-
erational in1987and was shown at the1988ACM-SIGMOD Conference.Version1,described in
The implementation of POSTGRES,was released to a few external users in June1989.In response
to a critique of the?rst rule system(A commentary on the POSTGRES rules system),the rule system
was redesigned(On Rules,Procedures,Caching and Views in Database Systems)and Version2was
released in June1990with the new rule system.Version3appeared in1991and added support for
multiple storage managers,an improved query executor,and a rewritten rewrite rule system.For the
most part,subsequent releases until Postgres95(see below)focused on portability and reliability.
POSTGRES has been used to implement many different research and production applications.These
include:a?nancial data analysis system,a jet engine performance monitoring package,an aster-
oid tracking database,a medical information database,and several geographic information systems.
POSTGRES has also been used as an educational tool at several universities.Finally,Illustra Infor-
mation Technologies(later merged into Informix2,which is now owned by IBM3.)picked up the code
and commercialized it.POSTGRES became the primary data manager for the Sequoia20004scienti?c
computing project in late1992.
The size of the external user community nearly doubled during1993.It became increasingly obvious
that maintenance of the prototype code and support was taking up large amounts of time that should
have been devoted to database research.In an effort to reduce this support burden,the Berkeley
POSTGRES project of?cially ended with Version4.2.
2.2.Postgres95
In1994,Andrew Yu and Jolly Chen added a SQL language interpreter to POSTGRES.Postgres95
was subsequently released to the Web to?nd its own way in the world as an open-source descendant
of the original POSTGRES Berkeley code.
Postgres95code was completely ANSI C and trimmed in size by25%.Many internal changes im-
proved performance and maintainability.Postgres95release1.0.x ran about30-50%faster on the
Wisconsin Benchmark compared to POSTGRES,Version4.2.Apart from bug?xes,the following
were the major enhancements:
?The query language PostQUEL was replaced with SQL(implemented in the server).Subqueries
were not supported until PostgreSQL(see below),but they could be imitated in Postgres95with
user-de?ned SQL functions.Aggregates were re-implemented.Support for the GROUP BY query
clause was also added.The libpq interface remained available for C programs.
?In addition to the monitor program,a new program(psql)was provided for interactive SQL queries
using GNU Readline.
2.https://www.doczj.com/doc/9911946115.html,/
3.https://www.doczj.com/doc/9911946115.html,/
4.https://www.doczj.com/doc/9911946115.html,/s2k/s2k_home.html
?A new front-end library,libpgtcl,supported Tcl-based clients.A sample shell,pgtclsh,provided
new Tcl commands to interface Tcl programs with the Postgres95backend.
?The large-object interface was overhauled.The Inversion large objects were the only mechanism
for storing large objects.(The Inversion?le system was removed.)
?The instance-level rule system was removed.Rules were still available as rewrite rules.
?A short tutorial introducing regular SQL features as well as those of Postgres95was distributed
with the source code
?GNU make(instead of BSD make)was used for the build.Also,Postgres95could be compiled
with an unpatched GCC(data alignment of doubles was?xed).
2.3.PostgreSQL
By1996,it became clear that the name“Postgres95”would not stand the test of time.We chose a new
name,PostgreSQL,to re?ect the relationship between the original POSTGRES and the more recent
versions with SQL capability.At the same time,we set the version numbering to start at6.0,putting
the numbers back into the sequence originally begun by the Berkeley POSTGRES project.
The emphasis during development of Postgres95was on identifying and understanding existing prob-
lems in the backend code.With PostgreSQL,the emphasis has shifted to augmenting features and
capabilities,although work continues in all areas.
Major enhancements in PostgreSQL include:
?Table-level locking has been replaced by multiversion concurrency control,which allows readers
to continue reading consistent data during writer activity and enables hot backups from pg_dump
while the database stays available for queries.
?Important backend features,including subselects,defaults,constraints,and triggers,have been im-
plemented.
?Additional SQL92-compliant language features have been added,including primary keys,quoted
identi?ers,literal string type coercion,type casting,and binary and hexadecimal integer input.
?Built-in types have been improved,including new wide-range date/time types and additional geo-
metric type support.
?Overall backend code speed has been increased by approximately20-40%,and backend start-up
time has decreased by80%since version6.0was released.
3.Documentation Resources
This manual set is organized into several parts:
Tutorial
An informal introduction for new users
User’s Guide
Documents the SQL query language environment,including data types and functions.
Programmer’s Guide
Advanced information for application programmers.Topics include type and function extensi-
bility,library interfaces,and application design issues.
Administrator’s Guide
Installation and server management information
Reference Manual
Reference pages for SQL command syntax and client and server programs
Developer’s Guide
Information for PostgreSQL developers.This is intended for those who are contributing to the
PostgreSQL project;application development information appears in the Programmer’s Guide.
In addition to this manual set,there are other resources to help you with PostgreSQL installation and
use:
man pages
The Reference Manual’s pages in the traditional Unix man format.
FAQs
Frequently Asked Questions(FAQ)lists document both general issues and some
platform-speci?c issues.
READMEs
README?les are available for some contributed packages.
Web Site
The PostgreSQL web site5carries details on the latest release,upcoming features,and other
information to make your work or play with PostgreSQL more productive.
Mailing Lists
The mailing lists are a good place to have your questions answered,to share experiences with
other users,and to contact the developers.Consult the User’s Lounge6section of the PostgreSQL
web site for details.
Yourself!
PostgreSQL is an open-source effort.As such,it depends on the user community for ongoing
support.As you begin to use PostgreSQL,you will rely on others for help,either through the
documentation or through the mailing lists.Consider contributing your knowledge back.If you
learn something which is not in the documentation,write it up and contribute it.If you add
features to the code,contribute them.
Even those without a lot of experience can provide corrections and minor changes in the docu-
mentation,and that is a good way to start.The
is the place to get going.
5.https://www.doczj.com/doc/9911946115.html,
6.https://www.doczj.com/doc/9911946115.html,/users-lounge/
4.Terminology and Notation
The terms“PostgreSQL”and“Postgres”will be used interchangeably to refer to the software that
accompanies this documentation.
An administrator is generally a person who is in charge of installing and running the server.A user
could be anyone who is using,or wants to use,any part of the PostgreSQL system.These terms should
not be interpreted too narrowly;this documentation set does not have?xed presumptions about system
administration procedures.
We use/usr/local/pgsql/as the root directory of the installation and
/usr/local/pgsql/data as the directory with the database?les.These directories may vary on
your site,details can be derived in the Administrator’s Guide.
In a command synopsis,brackets([and])indicate an optional phrase or keyword.Anything in braces
({and})and containing vertical bars(|)indicates that you must choose one alternative.
Examples will show commands executed from various accounts and https://www.doczj.com/doc/9911946115.html,mands executed
from a Unix shell may be preceded with a dollar sign(“$”).Commands executed from particular
user accounts such as root or postgres are specially?agged and explained.SQL commands may be
preceded with“=>”or will have no leading prompt,depending on the context.
Note:The notation for?agging commands is not universally consistent throughout
the documentation set.Please report problems to the documentation mailing list
5.Bug Reporting Guidelines
When you?nd a bug in PostgreSQL we want to hear about it.Your bug reports play an important part
in making PostgreSQL more reliable because even the utmost care cannot guarantee that every part
of PostgreSQL will work on every platform under every circumstance.
The following suggestions are intended to assist you in forming bug reports that can be handled in an
effective fashion.No one is required to follow them but it tends to be to everyone’s advantage.
We cannot promise to?x every bug right away.If the bug is obvious,critical,or affects a lot of users,
chances are good that someone will look into it.It could also happen that we tell you to update to a
newer version to see if the bug happens there.Or we might decide that the bug cannot be?xed before
some major rewrite we might be planning is done.Or perhaps it is simply too hard and there are
more important things on the agenda.If you need help immediately,consider obtaining a commercial
support contract.
5.1.Identifying Bugs
Before you report a bug,please read and re-read the documentation to verify that you can really do
whatever it is you are trying.If it is not clear from the documentation whether you can do something
or not,please report that too;it is a bug in the documentation.If it turns out that the program does
something different from what the documentation says,that is a bug.That might include,but is not
limited to,the following circumstances:
?A program terminates with a fatal signal or an operating system error message that would point to
a problem in the program.(A counterexample might be a“disk full”message,since you have to?x
that yourself.)
?A program produces the wrong output for any given input.
?A program refuses to accept valid input(as de?ned in the documentation).
?A program accepts invalid input without a notice or error message.But keep in mind that your idea of invalid input might be our idea of an extension or compatibility with traditional practice.?PostgreSQL fails to compile,build,or install according to the instructions on supported platforms. Here“program”refers to any executable,not only the backend server.
Being slow or resource-hogging is not necessarily a bug.Read the documentation or ask on one of the mailing lists for help in tuning your applications.Failing to comply to the SQL standard is not necessarily a bug either,unless compliance for the speci?c feature is explicitly claimed.
Before you continue,check on the TODO list and in the FAQ to see if your bug is already known. If you cannot decode the information on the TODO list,report your problem.The least we can do is make the TODO list clearer.
5.2.What to report
The most important thing to remember about bug reporting is to state all the facts and only facts.Do not speculate what you think went wrong,what“it seemed to do”,or which part of the program has a fault.If you are not familiar with the implementation you would probably guess wrong and not help us a bit.And even if you are,educated explanations are a great supplement to but no substitute for facts.If we are going to?x the bug we still have to see it happen for ourselves?rst.Reporting the bare facts is relatively straightforward(you can probably copy and paste them from the screen)but all too often important details are left out because someone thought it does not matter or the report would be understood anyway.
The following items should be contained in every bug report:
?The exact sequence of steps from program start-up necessary to reproduce the problem.This should be self-contained;it is not enough to send in a bare select statement without the preceding create table and insert statements,if the output should depend on the data in the tables.We do not have the time to reverse-engineer your database schema,and if we are supposed to make up our own data we would probably miss the problem.The best format for a test case for query-language related problems is a?le that can be run through the psql frontend that shows the problem.(Be sure to not have anything in your~/.psqlrc start-up?le.)An easy start at this?le is to use pg_dump to dump out the table declarations and data needed to set the scene,then add the problem query.You are encouraged to minimize the size of your example,but this is not absolutely necessary.If the bug is reproducible,we will?nd it either way.
If your application uses some other client interface,such as PHP,then please try to isolate the offending queries.We will probably not set up a web server to reproduce your problem.In any case remember to provide the exact input?les,do not guess that the problem happens for“large?les”or“mid-size databases”,etc.since this information is too inexact to be of use.
?The output you got.Please do not say that it“didn’t work”or“crashed”.If there is an error message, show it,even if you do not understand it.If the program terminates with an operating system error, say which.If nothing at all happens,say so.Even if the result of your test case is a program crash or otherwise obvious it might not happen on our platform.The easiest thing is to copy the output from the terminal,if possible.
Note:In case of fatal errors,the error message reported by the client might not contain all the information available.Please also look at the log output of the database server.If you do not keep your server’s log output,this would be a good time to start doing so.
?The output you expected is very important to state.If you just write“This command gives me that output.”or“This is not what I expected.”,we might run it ourselves,scan the output,and think it looks OK and is exactly what we expected.We should not have to spend the time to decode the exact semantics behind your commands.Especially refrain from merely saying that“This is not what SQL says/Oracle does.”Digging out the correct behavior from SQL is not a fun undertaking, nor do we all know how all the other relational databases out there behave.(If your problem is a program crash,you can obviously omit this item.)
?Any command line options and other start-up options,including concerned environment variables or con?guration?les that you changed from the default.Again,be exact.If you are using a prepack-aged distribution that starts the database server at boot time,you should try to?nd out how that is done.
?Anything you did at all differently from the installation instructions.
?The PostgreSQL version.You can run the command SELECT version();to?nd out the version of the server you are connected to.Most executable programs also support a--version option;at least postmaster--version and psql--version should work.If the function or the options do not exist then your version is more than old enough to warrant an upgrade.You can also look into the README?le in the source directory or at the name of your distribution?le or package name. If you run a prepackaged version,such as RPMs,say so,including any subversion the package may have.If you are talking about a CVS snapshot,mention that,including its date and time.
If your version is older than7.2we will almost certainly tell you to upgrade.There are tons of bug ?xes in each new release,that is why we make new releases.
?Platform information.This includes the kernel name and version,C library,processor,memory information.In most cases it is suf?cient to report the vendor and version,but do not assume everyone knows what exactly“Debian”contains or that everyone runs on Pentiums.If you have installation problems then information about compilers,make,etc.is also necessary.
Do not be afraid if your bug report becomes rather lengthy.That is a fact of life.It is better to report everything the?rst time than us having to squeeze the facts out of you.On the other hand,if your input?les are huge,it is fair to ask?rst whether somebody is interested in looking into it.
Do not spend all your time to?gure out which changes in the input make the problem go away.This will probably not help solving it.If it turns out that the bug cannot be?xed right away,you will still have time to?nd and share your work-around.Also,once again,do not waste your time guessing why the bug exists.We will?nd that out soon enough.
When writing a bug report,please choose non-confusing terminology.The software package in to-tal is called“PostgreSQL”,sometimes“Postgres”for short.If you are speci?cally talking about the backend server,mention that,do not just say“PostgreSQL crashes”.A crash of a single backend server process is quite different from crash of the parent“postmaster”process;please don’t say“the postmaster crashed”when you mean a single backend went down,nor vice versa.Also,client pro-grams such as the interactive frontend“psql”are completely separate from the backend.Please try to be speci?c about whether the problem is on the client or server side.
5.3.Where to report bugs
In general,send bug reports to the bug report mailing list at
are requested to use a descriptive subject for your email message,perhaps parts of the error message.
Another method is to?ll in the bug report web-form available at the project’s web site
https://www.doczj.com/doc/9911946115.html,/.Entering a bug report this way causes it to be mailed to the
Do not send bug reports to any of the user mailing lists,such as
or
their subscribers normally do not wish to receive bug reports.More importantly,they are unlikely to
?x them.
Also,please do not send reports to the developers’mailing list hackers@https://www.doczj.com/doc/9911946115.html,>.This list is for discussing the development of PostgreSQL and it would be nice if we could keep the bug reports separate.We might choose to take up a discussion about your bug report on pgsql-hackers,if the problem needs more review. If you have a problem with the documentation,the best place to report it is the documentation mailing list are unhappy with. If your bug is a portability problem on a non-supported platform,send mail to platform. Note:Due to the unfortunate amount of spam going around,all of the above email addresses are closed mailing lists.That is,you need to be subscribed to a list to be allowed to post on it. (Y ou need not be subscribed to use the bug report web-form,however.)If you would like to send mail but do not want to receive list traf?c,you can subscribe and set your subscription option to nomail.For more information send mail to help in the body of the message. 6.Y2K Statement Author:Written by Thomas Lockhart( 2000-03-31. The PostgreSQL Global Development Group provides the PostgreSQL software code tree as a public service,without warranty and without liability for its behavior or performance.However,at the time of writing: ?The author of this statement,a volunteer on the PostgreSQL support team since November,1996, is not aware of any problems in the PostgreSQL code base related to time transitions around Jan1, 2000(Y2K). ?The author of this statement is not aware of any reports of Y2K problems uncovered in regression testing or in other?eld use of recent or current versions of PostgreSQL.We might have expected to hear about problems if they existed,given the installed base and the active participation of users on the support mailing lists. ?To the best of the author’s knowledge,the assumptions PostgreSQL makes about dates speci?ed with a two-digit year are documented in the current User’s Guide in the chapter on data types.For two-digit years,the signi?cant transition year is1970,not2000;e.g.70-01-01is interpreted as 1970-01-01,whereas69-01-01is interpreted as2069-01-01. ?Any Y2K problems in the underlying OS related to obtaining the“current time”may propagate into apparent Y2K problems in PostgreSQL. Refer to The GNU Project8and The Perl Institute9for further discussion of Y2K issues,particularly as it relates to open source,no fee software. 8.https://www.doczj.com/doc/9911946115.html,/software/year2000.html 9.https://www.doczj.com/doc/9911946115.html,/news/y2k.html Chapter1.Getting Started 1.1.Installation Before you can use PostgreSQL you need to install it,of course.It is possible that PostgreSQL is already installed at your site,either because it was included in your operating system distribution or because the system administrator already installed it.If that is the case,you should obtain infor- mation from the operating system documentation or your system administrator about how to access PostgreSQL. If you are not sure whether PostgreSQL is already available or whether you can use it for your ex- perimentation then you can install it yourself.Doing so is not hard and it can be a good exercise. PostgreSQL can be installed by any unprivileged user,no superuser(root)access is required. If you are installing PostgreSQL yourself,then refer to the Administrator’s Guide for instructions on installation,and return to this guide when the installation is complete.Be sure to follow closely the section about setting up the appropriate environment variables. If your site administrator has not set things up in the default way,you may have some more work to do.For example,if the database server machine is a remote machine,you will need to set the PGHOST environment variable to the name of the database server machine.The environment variable PGPORT may also have to be set.The bottom line is this:if you try to start an application program and it complains that it cannot connect to the database,you should consult your site administrator or,if that is you,the documentation to make sure that your environment is properly set up.If you did not understand the preceding paragraph then read the next section. 1.2.Architectural Fundamentals Before we proceed,you should understand the basic PostgreSQL system architecture.Understanding how the parts of PostgreSQL interact will make this chapter somewhat clearer. 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?les,accepts connections to the database from client applications,and performs actions on the database on behalf of the clients.The database server program is called postmaster. ?The user’s client(frontend)application that wants to perform database operations.Client applica- tions can be very diverse in nature:They 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. As is typical of client/server applications,the client and the server can be on different hosts.In that case they communicate over a TCP/IP network connection.You should keep this in mind,because the?les that can be accessed on a client machine might not be accessible(or might only be accessible using a different?le name)on the database server machine. The PostgreSQL server can handle multiple concurrent connections from clients.For that purpose it starts(“forks”)a new process for each connection.From that point on,the client and the new server process communicate without intervention by the original postmaster process.Thus,the postmaster is always running,waiting for client connections,whereas client and associated server processes come and go.(All of this is of course invisible to the user.We only mention it here for completeness.) 1.3.Creating a Database The?rst test to see whether you can access the database server is to try to create a database.A running PostgreSQL server can manage many databases.Typically,a separate database is used for each project or for each user. Possibly,your site administrator has already created a database for your use.He should have told you what the name of your database is.In this case you can omit this step and skip ahead to the next section. To create a new database,in this example named mydb,you use the following command: $createdb mydb This should produce as response: CREATE DATABASE If so,this step was successful and you can skip over the remainder of this section. If you see a message similar to createdb:command not found then PostgreSQL was not installed properly.Either it was not installed at all or the search path was not set correctly.Try calling the command with an absolute path instead: $/usr/local/pgsql/bin/createdb mydb The path at your site might be different.Contact your site administrator or check back in the installa- tion instructions to correct the situation. Another response could be this: psql:could not connect to server:Connection refused Is the server running locally and accepting connections on Unix domain socket"/tmp/.s.PGSQL.5432"? createdb:database creation failed This means that the server was not started,or it was not started where createdb expected it.Again, check the installation instructions or consult the administrator. If you do not have the privileges required to create a database,you will see the following: ERROR:CREATE DATABASE:permission denied createdb:database creation failed Not every user has authorization to create new databases.If PostgreSQL refuses to create databases for you then the site administrator needs to grant you permission to create databases.Consult your site administrator if this occurs.If you installed PostgreSQL yourself then you should log in for the purposes of this tutorial under the user account that you started the server as.1 1.As an explanation for why this works:PostgreSQL user names are separate from operating system user accounts.If you connect to a database,you can choose what PostgreSQL user name to connect as;if you don’t,it will default to the same name You can also create databases with other names.PostgreSQL allows you to create any number of databases at a given site.Database names must have an alphabetic?rst character and are limited to31 characters in length.A convenient choice is to create a database with the same name as your current user name.Many tools assume that database name as the default,so it can save you some typing.To create that database,simply type $createdb If you don’t 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 mydb (For this command,the database name does not default to the user account name.You always need to specify it.)This action physically removes all?les associated with the database and cannot be undone, so this should only be done with a great deal of forethought. 1.4.Accessing a Database Once you have created a database,you can access it by: ?Running the PostgreSQL interactive terminal program,called psql,which allows you to interac- tively enter,edit,and execute SQL commands. ?Using an existing graphical frontend tool like PgAccess or ApplixWare(via ODBC)to create and manipulate a database.These possibilities are not covered in this tutorial. ?Writing a custom application,using one of the several available language bindings.These possibil- ities are discussed further in The PostgreSQL Programmer’s Guide. You probably want to start up psql,to try out the examples in this tutorial.It can be activated for the mydb database by typing the command: $psql mydb If you leave off the database name then it will default to your user account name.You already discov- ered this scheme in the previous section. In psql,you will be greeted with the following message: Welcome to psql,the PostgreSQL interactive terminal. Type:\copyright for distribution terms \h for help with SQL commands \?for help on internal slash commands \g or terminate with semicolon to execute query \q to quit mydb=> The last line could also be as your current operating system account.As it happens,there will always be a PostgreSQL user account that has the same name as the operating system user that started the server,and it also happens that that user always has permission to create databases.Instead of logging in as that user you can also specify the-U option everywhere to select a PostgreSQL user name to connect as. mydb=# That would mean you are a database superuser,which is most likely the case if you installed Post-greSQL yourself.Being a superuser means that you are not subject to access controls.For the purpose of this tutorial this is not of importance. If you have encountered problems starting psql then go back to the previous section.The diagnostics of psql and createdb are similar,and if the latter worked the former should work as well. The last line printed out by psql is the prompt,and it indicates that psql is listening to you and that you can type SQL queries into a work space maintained by psql.Try out these commands: mydb=>SELECT version(); version ---------------------------------------------------------------- PostgreSQL7.2devel on i586-pc-linux-gnu,compiled by GCC 2.96 (1row) mydb=>SELECT current_date; date ------------ 2001-08-31 (1row) mydb=>SELECT2+2; ?column? ---------- 4 (1row) 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=>\q and psql will quit and return you to your command shell.(For more internal commands,type\?at the psql prompt.)The full capabilities of psql are documented in the Reference Manual.If PostgreSQL is installed correctly you can also type man psql at the operating system shell prompt to see the documentation.In this tutorial we will not use these features explicitly,but you can use them yourself when you see?t. Chapter2.The SQL Language 2.1.Introduction This chapter provides an overview of how to use SQL to perform simple operations.This tutorial is only intended to give you an introduction and is in no way a complete tutorial on SQL.Numer- ous books have been written on SQL92,including Understanding the New SQL and A Guide to the SQL Standard.You should be aware that some PostgreSQL language features are extensions to the standard. In the examples that follow,we assume that you have created a database named mydb,as described in the previous chapter,and have started psql. Examples in this manual can also be found in the PostgreSQL source distribution in the directory src/tutorial/.Refer to the README?le in that directory for how to use them.To start the tutorial, do the following: $cd..../src/tutorial $psql-s mydb ... mydb=>\i basics.sql The\i command reads in commands from the speci?ed?le.The-s option puts you in single step mode which pauses before sending each query to the server.The commands used in this section are in the?le basics.sql. 2.2.Concepts PostgreSQL is a relational database management system(RDBMS).That means it is a system for managing data stored in relations.Relation is essentially a mathematical term for table.The notion of storing data 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. Each table is a named collection of rows.Each row of a given table has the same set of named columns,and each column is of a speci?c data type.Whereas columns have a?xed order in each row, it is important to remember that SQL does not guarantee the order of the rows within the table in any way(although they can be explicitly sorted for display). Tables are grouped into databases,and a collection of databases managed by a single PostgreSQL server instance constitutes a database cluster. 2.3.Creating a New Table 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 termi- nated until the semicolon. White space(i.e.,spaces,tabs,and newlines)may be used freely in SQL commands.That means you can type the command aligned differently than above,or even all on one line.Two dashes(“- -”)introduce comments.Whatever follows them is ignored up to the end of the line.SQL is case insensitive about key words and identi?ers,except when identi?ers are double-quoted to preserve the case(not done above). varchar(80)speci?es a data type that can store arbitrary character strings up to80characters in length.int is the normal integer type.real is a type for storing single precision?oating-point num- bers.date should be self-explanatory.(Yes,the column of type date is also named date.This may be convenient or confusing--you choose.) PostgreSQL supports the usual SQL types int,smallint,real,double precision,char(N), varchar(N),date,time,timestamp,and interval,as well as other types of general utility and a rich set of geometric types.PostgreSQL can be customized with an arbitrary number of user-de?ned data types.Consequently,type names are not syntactical keywords,except where required to support special cases in the SQL standard. The second example will store cities and their associated geographical location: CREATE TABLE cities( name varchar(80), location point ); The point type is an example of a PostgreSQL-speci?c data type. Finally,it should be mentioned that if you don’t need a table any longer or want to recreate it differ- ently you can remove it using the following command: DROP TABLE tablename; 2.4.Populating a Table With Rows The INSERT statement is used to populate a table with rows: INSERT INTO weather VALUES(’San Francisco’,46,50,0.25,’1994-11-27’); Note that all data types use rather obvious input formats.Constants that are not simple numeric values usually must be surrounded by single quotes(’),as in the example.The date column is actually quite ?exible in what it accepts,but for this tutorial we will stick to the unambiguous format shown here. The point type requires a coordinate pair as input,as shown here: INSERT INTO cities VALUES(’San Francisco’,’(-194.0,53.0)’); The syntax used so far requires you to remember the order of the columns.An alternative syntax allows you to list the columns explicitly: INSERT INTO weather(city,temp_lo,temp_hi,prcp,date) VALUES(’San Francisco’,43,57,0.0,’1994-11-29’); You can list the columns in a different order if you wish or even omit some columns,e.g.,if the precipitation is unknown: INSERT INTO weather(date,city,temp_hi,temp_lo) VALUES(’1994-11-29’,’Hayward’,54,37); Many developers consider explicitly listing the columns better style than relying on the order implic- itly. Please enter all the commands shown above so you have some data to work with in the following sections. You could also have used COPY to load large amounts of data from?at-text?les.This is usually faster because the COPY command is optimized for this application while allowing less?exibility than INSERT.An example would be: COPY weather FROM’/home/user/weather.txt’; where the?le name for the source?le must be available to the backend server machine,not the client, since the backend server reads the?le directly.You can read more about the COPY command in the Reference Manual. 2.5.Querying a Table To retrieve data from a table,the table is queried.An SQL SELECT statement is used to do this.The statement is divided into a select list(the part that lists the columns to be returned),a table list(the part that lists the tables from which to retrieve the data),and an optional quali?cation(the part that speci?es any restrictions).For example,to retrieve all the rows of table weather,type: SELECT*FROM weather; (here*means“all columns”)and the output should be: city|temp_lo|temp_hi|prcp|date ---------------+---------+---------+------+------------ San Francisco|46|50|0.25|1994-11-27 San Francisco|43|57|0|1994-11-29 Hayward|37|54||1994-11-29 (3rows) You may specify any arbitrary expressions in the target list.For example,you can do: SELECT city,(temp_hi+temp_lo)/2AS temp_avg,date FROM weather; This should give: city|temp_avg|date ---------------+----------+------------ San Francisco|48|1994-11-27 San Francisco|50|1994-11-29 Hayward|45|1994-11-29 (3rows) PostgreSQL安装和简单使用 PostgreSQL安装和简单使用 作者：小P 来自：https://www.doczj.com/doc/9911946115.html, 摘要：PostgreSQL是现在比较流行的数据库之一，这个起源于伯克利（BSD）的数据库研究计划目前已经衍生成一项国际开发项目，并且有非常广泛的用户。据我了解国内四大国产数据库，其中三个都是基于PostgreSQL开发的。并且，因为许可证的灵活，任何人都可以以任何目的免费使用，修改，和分发PostgreSQL，不管是私用，商用，还是学术研究使用。本文只是简单介绍一下postgresql的安装和简单的使用，语法方面涉及的比较少，以方便新手上路为目的。目录1.系统环境及安装方法；1.1 系统环境； 1.2 安装； 2.启动PostgreSQL 数据库服务器；2.1 在流行Linux发行版的启动方法； 2.2 关于PostgreSQL启动和存储目录； 3.创建用户；3.1 添加用户; 3.1.1 不带参数的创建用户; 3.1.2 为指定的主机和端口上创建用户; 3.1.3创建超级用户; 3.2 删除用户：3.2.1 删除本地的Postgres用户; 3.2.2 删除远程Postgres服务器上的用户; 4. 创建和删除数据库；4.1创建数据库 4.2 删除数据库 5.访问数据库5.1 激活数据库 5.2 帮助和退出数据库 6. Postgresql图形化管理工具pgAdmin3 ;6.1 安装;6.1.1 Ubuntu安装; 6.1.2 其它系统的安装;6.2 pgAdmin3的简单使用;6.2.1 pgAdmin3的启动; 6.2.2 连接已创建的数据库mydb ; 7. 创建和删除表;7.1 创建新表; 7.2 数据类型; 7.3 删除表;8. 向表中添加行;8.1 INSERT; 8.2 point类型输入; 8.3 COPY;9. 查询一个表;9.1 SELECT; 9.2 WHERE; 9.3 排序;10. 视图; 11. 更新行; 12. 删除行; 13. 关于本文； 14. 更新日志； 15. 参考文档； 16. 相关文档； +++++++++++++++++++++++++++++++++++++++++++ 监视并记录Apache网站服务器的运行 LogFormat 指令承诺你告诉Apache你想要记录要求的哪些方面。而你仍需附加的指令来告诉Apache在哪里记录那些信息，这在下一章中将会介绍。下面的例子显示了两种最受欢迎的格式的配置：一般日志格式和整合日志格式。当Apache收到一个要求，他将会用相应的要求属性来替代以%为前缀的每一个域。假如您正在使用一般日志格式，您的日志文件里 尽管有附件提供日志格式的详尽索引，下表描述了一些最为重要的域： # %h: 客户端（例如，扫瞄器）向服务器发出连接要求时自己的当时的IP地址或域名(需开启HostNameLookups)。 # %u: 使用方式认证用户时，记录下的用户的编号。 # %t: 服务器同意到连接要求的时刻。 # %r: 客户端发出的原始连接要求中的文本信息，包含所使用的方法。 # %>s: 服务器应答扫瞄器后的返回状态代码，200表示要求成功。. # %b: 服务器应答扫瞄器发出的单个要求的回传对象的内容大小（字节为单位），不统计数据包头部字节。 整合日志格式在一般日志格式的基础上扩展出了两个附加的域。定义为： # %{Referer}i: 连接要求数据包包头，包含指向当前页面的文档关联信息。 # %{User-agent}i: 用户代理连接要求数据包包头，包含客户扫瞄器的信息。 创建一个自定义日志文件 您可能会想创建Apache自带以外的新的日志文件。下面的例子将运用CustomLog来创建一个新的日志文件，并储存由一个之前定义好的日志格式，即前一章提到的common，所定义的信息。您还能够用格式本身的定义来替换昵称。一个附加的，更为简单的指令是Transferlog，它只同意最后一个LogFormat指令提供的定义。 OB开发手册中文版 Contents [hide]1 简介 ? 1 简介 ? 1.1 开发概述 ? 1.2 开发方法 ? 1.3 组织开发工作 ? 1.4 标识符命名标准 ? 1.4.1 数据库元素 ? 1.4.2 MVC目录 ? 1.4.3 存储过程语法 ? 1.5 目录结构 ? 1.6 风格指南 ? 1.6.1 逻辑比较 ? 1.6.2 逗号分隔列表 ? 1.6.3 圆括号中的空格 ? 1.6.4 SELECT INTO和INSERT INTO ? 1.6.5 SQL关键字 ? 1.7 编译程序 ? 1.7.1 命令行编译任务 ? 1.7.2 开发环境 ? 1.7.3 生产环境 ? 1.8 从源代码构建 ? 1.8.1 安装Subversion ? 1.8.2 从Subversion中检出源代码 ? 1.8.3 快速构建指南 ? 1.9 集成开发环境 ? 2 Openbravo数据模型 ? 2.1 存储的数据库对象 ? 2.2 实体-关系(ER)图 ? 2.3 创建存储过程 ? 2.3.1 AD_PInstance和AD_PInstance_Para表 ? 2.3.2 存储过程的输入参数 ? 2.3.2.1 从AD_PInstance表中获取有用的信息 ? 2.3.2.2 AD_Update_PInstance存储过程 ? 2.3.2.3 例外和错误管理 ? 2.4 存储过程语法的建议 ? 2.4.1 通用规则 ? 2.4.1.1 游标 ? 2.4.1.2 数组 ? 2.4.1.3 ROWNUM ? 2.4.1.4 %ROWCOUNT ? 2.4.1.5 %ISOPEN,%NOTFOUND ? 2.4.2 表 ? 2.4.3 函数 ? 2.4.4 存储过程 使用pg_dump/psql转储数据库 pg_dump/psql应用程序在pg安装目录的bin目录下。 只要在安装pg数据库的服务器上，且能够连通远程pg数据库，都可以实现数据导出。 一、pg_dump导出 pg_dump –h hostname –U name –p port –d database –f “file_name” -h: 数据库服务器地址 -U: 大写的U，用户名 -p: 端口号 -d: 数据库名 -f: 存储的文件路径和名称 pg_dump -h 110.84.129.40 -U postgres -p 3306 -d O2O -f "/home/wsxcde/database_file/O2O_bak_140430.dmp" 回城执行，会要求输入密码，正确，即可导出，没有进度条 远程导出300M的文件，大概时间10分钟。 以上命令是导出数据的全部对象，包括数据，对象（index，table，sequence，function等），但不包括blob的大对象，如果要导出大对象，要加上“-b”。 二、psql数据导入 psql –h localhost –U postgres –d new_db –f "xxx.dmp" -h: 数据库服务器地址，如果导入本机，直接使用localhost -U:大写的U，被导入数据库的用户名 -d: 数据库名，想导入的数据库，导入前请检查此数据库是否存在，不存在会报错 -f: 备份文件dmp的来源 psql–h localhost –U postgres –d O2O –f "/home/wsxcde/database_file/O2O_bak_140430.dmp" 300M的文件，导入时间不大约10多秒。 tgreSQL学习手册(五) 函数和操作符 阿里云携手开源中国众包平台发布百万悬赏项目? 一、逻辑操作符： 常用的逻辑操作符有：AND、OR和NOT。其语义与其它编程语言中的逻辑操作符完全相同。 二、比较操作符： 下面是PostgreSQL中提供的比较操作符列表： 操作符描述 <小于 >大于 <=小于或等于 >=大于或等于 =等于 !=不等于 比较操作符可以用于所有可以比较的数据类型。所有比较操作符都是双目操作符，且返回boolean类型。除了比较操作符以外，我们还可以使用BETWEEN语句，如： a BETWEEN x AND y 等效于 a >= x AND a <= y a NOT BETWEEN x AND y 等效于 a < x OR a > y 三、数学函数和操作符： 下面是PostgreSQL中提供的数学操作符列表： 操作符描述例子结果 +加 2 + 35 -减 2 - 3-1 *乘 2 * 36 /除 4 / 22 %模 5 % 41 ^幂 2.0 ^ 3.08 |/平方根|/ 25.05 ||/立方根||/ 27.03 !阶乘 5 !120 !!阶乘!! 5120 @绝对值@ -5.05 &按位AND91 & 1511 |按位OR32 | 335 #按位XOR17 # 520 ~按位NOT~1-2 <<按位左移 1 << 416 >>按位右移8 >> 22 按位操作符只能用于整数类型，而其它的操作符可以用于全部数值数据类型。按位操作符还可以用于位串类型bit和bit varying， 下面是PostgreSQL中提供的数学函数列表，需要说明的是，这些函数中有许多都存在多种形式，区别只是参数类型不同。除非特别指明，任何特定形式的函数都返回和它的参数相同的数据类型。 函数返回类 型 描述例子结果 abs(x)绝对值abs(-17.4)17.4 cbrt(double)立方根cbrt(27.0)3 ceil(double/numeric)不小于参数的最小的整 数 ceil(-42.8)-42 degrees(double) 把弧度转为角度degrees(0.5)28.6478897565412 exp(double/numeric)自然指数exp(1.0) 2.71828182845905 floor(double/numeric)不大于参数的最大整数floor(-42.8)-43 ln(double/numeric)自然对数ln(2.0)0.693147180559945 log(double/numeric)10为底的对数log(100.0)2 log(b numeric,x numeric)numeric指定底数的对 数 log(2.0, 64.0) 6.0000000000 mod(y, x)取余数mod(9,4)1 pi() double"π"常量pi() 3.14159265358979 power(a double, b double)double求a的b次幂power(9.0, 3.0)729 power(a numeric, b numeric) numeric求a的b次幂power(9.0, 3.0)729 radians(double)double把角度转为弧度radians(45.0)0.785398163397448 random()double 0.0到1.0之间的随机 数值 random() round(double/numeric)圆整为最接近的整数round(42.4)42 round(v numeric, s int)numeric圆整为s位小数数字round(42.438,2)42.44 sign(double/numeric)参数的符号(-1,0,+1) sign(-8.4)-1 sqrt(double/numeric)平方根sqrt(2.0) 1.4142135623731 trunc(double/numeric)截断(向零靠近)trunc(42.8)42 trunc(v numeric, s int)numeric 截断为s小数位置的数 字 trunc(42.438,2)42.43 三角函数列表： 函数描述 acos(x)反余弦 本章大纲 1. 如何访问命令行 2. 使用命令行下的工具 非编辑模式 进入编辑模式 3. 正则表达式、管道和I/O 重定向 4. 管理用户账户 5. 文件访问控制 6. 管理进程 1，如何访问命令行 1.1 本地命令行的访问 在图形界面中，访问命令行的方法：打开Terminal，Console。或者：Ctrl+Alt+F1 ~ F6 1.2 使用SSH 访问命令行 同上 2，使用命令行下的工具 2.1 使用硬链接 硬链接，指在同一个文件系统中，对inode的引用，只要文件上存在至少1个硬链接，就可以找到对应的inode。 [digoal@digoal01 ~]$ echo "abc" > ./a [digoal@digoal01 ~]$ stat a File: `a' Size: 4 Blocks: 8 IO Block: 4096 regular file Device: 803h/2051d Inode: 656374 Links: 1 -- 硬链接数量 Access: (0664/-rw-rw-r--) Uid: ( 500/ digoal) Gid: ( 500/ digoal) Access: 2017-04-11 13:18:14.292848716 +0800 Modify: 2017-04-11 13:18:14.292848716 +0800 Change: 2017-04-11 13:18:14.292848716 +0800 创建硬链接 [digoal@digoal01 ~]$ ln -L ./a ./b [digoal@digoal01 ~]$ stat a File: `a' Size: 4 Blocks: 8 IO Block: 4096 regular file Device: 803h/2051d Inode: 656374 Links: 2 Access: (0664/-rw-rw-r--) Uid: ( 500/ digoal) Gid: ( 500/ digoal) Access: 2017-04-11 13:18:14.292848716 +0800 Modify: 2017-04-11 13:18:14.292848716 +0800 Change: 2017-04-11 13:18:34.631855044 +0800 [digoal@digoal01 ~]$ stat b File: `b' Size: 4 Blocks: 8 IO Block: 4096 regular file Device: 803h/2051d Inode: 656374 Links: 2 Access: (0664/-rw-rw-r--) Uid: ( 500/ digoal) Gid: ( 500/ digoal) Access: 2017-04-11 13:18:14.292848716 +0800 Modify: 2017-04-11 13:18:14.292848716 +0800 Change: 2017-04-11 13:18:34.631855044 +0800 删除一个硬链接，还能通过其他硬链接找到对应的inode。 [digoal@digoal01 ~]$ rm a rm: remove regular file `a'? y [digoal@digoal01 ~]$ cat b abc 2.2 归档和解压 常用的归档命令tar 归档-c (常用压缩库-j bz2, -z gzip) [digoal@digoal01 ~]$ tar -jcvf test.tar.bz2 b 一、概述： PL/pgSQL函数在第一次被调用时，其函数内的源代码(文本)将被解析为二进制指令树，但是函数内的表达式和SQL命令只有在首次用到它们的时候，PL/pgSQL解释器才会为其创建一个准备好的执行规划，随后对该表达式或SQL命令的访问都将使用该规划。如果在一个条件语句中，有部分SQL命令或表达式没有被用到，那么PL/pgSQL解释器在本次调用中将不会为其准备执行规划，这样的好处是可以有效地减少为PL/pgSQL函数里的语句生成分析和执行规划的总时间，然而缺点是某些表达式或SQL命令中的错误只有在其被执行到的时候才能发现。 由于PL/pgSQL在函数里为一个命令制定了执行计划，那么在本次会话中该计划将会被反复使用，这样做往往可以得到更好的性能，但是如果你动态修改了相关的数据库对象，那么就有可能产生问题，如： CREATE FUNCTION populate() RETURNS integer AS $$ DECLARE -- 声明段 BEGIN PERFORM my_function(); END; $$ LANGUAGE plpgsql; 在调用以上函数时，PERFORM语句的执行计划将引用my_function对象的OID。在此之后，如果你重建了my_function函数，那么populate函数将无法再找到原有my_function函数的OID。要解决该问题，可以选择重建populate函数，https://www.doczj.com/doc/9911946115.html,或者重新登录建立新的会话，以使PostgreSQL重新编译该函数。要想规避此类问题的发生，在重建my_function时可以使用CREATE OR REPLACE FUNCTION命令。 鉴于以上规则，在PL/pgSQL里直接出现的SQL命令必须在每次执行时均引用相同的表和字段，换句话说，不能将函数的参数用作SQL命令的表名或字段名。如果想绕开该限制，可以考虑使用PL/pgSQL 中的EXECUTE语句动态地构造命令，由此换来的代价是每次执行时都要构造一个新的命令计划。 使用PL/pgSQL函数的一个非常重要的优势是可以提高程序的执行效率，由于原有的SQL调用不得不在客户端与服务器之间反复传递数据，这样不仅增加了进程间通讯所产生的开销，而且也会大大增加网络IO的开销。 二、PL/pgSQL的结构： PL/pgSQL是一种块结构语言，函数定义的所有文本都必须在一个块内，其中块中的每个声明和每条语句都是以分号结束，如果某一子块在另外一个块内，那么该子块的END关键字后面必须以分号结束，不过对于函数体的最后一个END关键字，分号可以省略，如： [ < PostgreSQL数据库 一:PostgreSQL介绍 1、PostgreSQL就是以加州大学伯克利分校计算机系开发得 POSTGRES,现在已经更名为POSTGRES,版本 4、2为基础得对象关系型数据库管理系统(ORDBMS)。PostgreSQL支持大部分 SQL标准并且提供了许多其她现代特性:复杂查询、外键、触发器、视图、事务完整性、MVCC。同样,PostgreSQL 可以用许多方法扩展,比如, 通过增加新得数据类型、函数、操作符、聚集函数、索引方法、过程语言。并且,因为许可证得灵活,任何人都可以以任何目得免费使用、修改、与分发PostgreSQL,不管就是私用、商用、还就是学术研究使用。 2、PostgreSQL图标 3.PostgreSQL优点 有目前世界上最丰富得数据类型得支持支持,其中有些数据类型可以说连商业数据库都不具备,具体类型下文会说明、 PostgreSQL拥有一支非常活跃得开发队伍,而且在许多黑客得努力下,PostgreSQL 得质量日益提高 PostgreSQL 对接口得支持也就是非常丰富得,几乎支持所有类型得数据库客户端接口。这一点也可以说就是 PostgreSQL 一大优点。 4.PostgreSQL缺点 首先,早期得 PostgreSQL 继承了几乎所有 Ingres, Postgres, Postgres95 得问题:过于学院味,因为首先它得目得就是数据库研究,因此不论在稳定性, 性能还就是使用方方面面,长期以来一直没有得到重视,直到 PostgreSQL 项目开始以后,情况才越来越好,PostgreSQL 已经完全可以胜任任何中上规模范围内得应用范围得业务 其次,PostgreSQL 得确还欠缺一些比较高端得数据库管理系统需要得特性,比如数据库集群,更优良得管理工具与更加自动化得系统优化功能等提高数据库性能得机制等。 5.目前官方最新版本:9、3、2 二、windows下安装过程 1、开始安装: PostgreSql 常见数据类型 1.CREATE TYPE命令增加新的数据类型 2.数据类型 3.数值类型 3. 整数类型 smallint, integer, bigint 类型存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值将导致一个错误。 常用的类型是 integer ，因为它提供了在范围、存储空间、性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用 smallint 。而只有在 integer 的范围不够的时候才使用 bigint ，因为前者绝对快得多。 bigint 类型可能不是在所有平台上都运转正确，因为它依赖编译器对八字节整数的支持。在那些没有八字节整数支持的机器上，bigint 的作用和 integer 一样(但是仍然占据八字节存储)。不过，我们目前还没听说过有这样的平台。 SQL 只声明了整数类型 integer(或 int) 和 smallint 。类型 bigint 和类型别名 int2, int4, int8 都是扩展，并且也在许多其它 SQL 数据库系统中使用。 4.任意精度数值 numeric 类型可以存储最多 1000 位精度的数字并且准确地进行计算。特别建议将它用于货币金额和其它要求精确计算的场合。不过，numeric 类型上的算术运算比整数类型要慢很多。 术语：一个 numeric 类型的标度(scale)是小数部分的位数，精度(precision)是全部数据位的数目，也就是小数点两边的位数总和。因此数字 23.5141 的精度为 6 而标度为 4 。你可以认为整数的标度为零。 numeric 字段的最大精度和最大标度都是可以配置的。要声明一个字段的类型为 numeric ，你可以用下面的语法： NUMERIC(precision, scale) 精度必须为正数，标度可以为零或者正数。另外， NUMERIC(precision) 选择了标度为 0 。不带任何精度与标度的声明 NUMERIC 则创建一个可以存储一个直到实现精度上限的任意精度和标度的数值，一个这样类型的字段将不会把输 PostgreSQL数据库安装教程 （仅供参考） 最新更新：2014年08月27日 深圳市恩布网络科技有限公司 （内部技术文档） 目 录 1. Linux安装PostgreSQL数据库 (3) 1.1. 概述 (3) 1.2. 新建postgres用户和目录 (3) 1.3. 配置postgres用户的环境变量： (3) 1.4. yum安装 (3) 1.5. 初始化数据库目录 (3) 1.6. 给postgres赋予权限 (4) 1.7. 配置监听地址、端口 (4) 1.8. 配置支持远程连接 (4) 1.9. 设置开机启动 (4) 1.10. 启动服务 (4) 1.11. 修改postgres帐号密码 (4) 2. Windows安装PostgreSQL数据库 (5) 2.1. 概述 (5) 2.2. 安装 (5) 2.3. 配置监听地址、端口 (5) 2.4. 配置支持远程连接 (5) 3. 注意事项 (6) 4. 附录一：PostgreSQL数据库介绍 (6) 5. 附录二：PostgreSQL与MySQL比较 (6) 1.Linux安装PostgreSQL数据库 1.1.概述 以CentOS 6.4（或以上）X64（64位）版本，PostgreSQL8.4为例子说明。 本文仅供参考，如果安装不成功，或需要更多PostgreSQL技术资料，请自行上网搜索； 1.2.新建postgres用户和目录 mkdir /var/lib/pgsql groupadd postgres useradd ‐g postgres postgres 1.3.配置postgres用户的环境变量： cat >>/var/lib/pgsql/.bash_profile< Linux/Unix下ODBC的安装 方法一： 先下载最新的unixODBC源码包（https://www.doczj.com/doc/9911946115.html,/unixODBC-2.2.1.tar.gz）放到/usr/local下，然后运行下述命令： 安装成功后，unixODBC所需的头文件都被安装到了/usr/inlucde下，编译好的库文件安装到了/usr/lib下，与unixODBC相关的可执行文件安装到了/usr/bin下，配置文件放到了/etc下。 方法二： 下载rpm包进行安装，我们这里以Red Hat 7.3为例： unixODBC-2.2.0-5 RPM for i386（安装包及源码包） （ftp://https://www.doczj.com/doc/9911946115.html,/linux/redhat/7.3/en/os/i386/RedHat/RPMS/unixODBC-2 .2.0-5.i386.rpm、 ftp://https://www.doczj.com/doc/9911946115.html,/linux/redhat/7.3/en/os/i386/SRPMS/unixODBC-2.2.0-5.src.rpm）unixODBC-devel-2.2.0-5 RPM for i386 （ftp://https://www.doczj.com/doc/9911946115.html,/linux/redhat/7.3/en/os/i386/RedHat/RPMS/unixODBC-d evel-2.2.0-5.i386.rpm） 直接将unixODBC-2.2.0-5.i386.rpm和unixODBC-devel-2.2.0-5.i386.rpm装入系统就可以了，命令如下： Linux/Unix下ODBC的配置 运行ODBCConfig程序（在/usr/bin下），如下图： 图一：ODBCConfig主窗口 和Windows下的ODBC设置窗口是不是很像？我想大家都能看懂吧。 第一步：安装数据库的ODBC驱动程序 Drivers这一栏中用来设置数据库的驱动程序，点击Add按钮，会出现下图： PostgreSQL学习手册：SQL语言函数 一、基本概念： SQL函数可以包含任意数量的查询，但是函数只返回最后一个查询(必须是SELECT)的结果。在简单情况下，返回最后一条查询结果的第一行。如果最后一个查询不返回任何行，那么该函数将返回NULL值。如果需要该函数返回最后一条SELECT语句的所有行，可以将函数的返回值定义为集合，即SETOF sometype。 SQL函数的函数体应该是用分号分隔的SQL语句列表，其中最后一条语句之后的分号是可选的。除非函数声明为返回void，否则最后一条语句必须是SELECT。事实上，在SQL函数中，不仅可以包含SELECT查询语句，也可以包含INSERT、UPDATE和DELETE等其他标准的SQL 语句，但是和事物相关的语句不能包含其中，如BEGIN、COMMIT、ROLLBACK和SAVEPOINT 等。 CREATE FUNCTION命令的语法要求函数体写成一个字符串文本。通常来说，该文本字符串常量使用美元符($$)围住，如： CREATE FUNCTION clean_emp() RETURNS void AS $$ DELETE FROM emp WHERE salary < 0; $$ LANGUAGE SQL; 最后需要说明的是SQL函数中的参数，PostgreSQL定义$1表示第一个参数，$2为第二个参数并以此类推。如果参数是复合类型，则可以使用点表示法，即$https://www.doczj.com/doc/9911946115.html,访问复合类型参数中的name字段。需要注意的是函数参数只能用作数据值，而不能用于标识符，如：INSERT INTO mytable VALUES ($1); --合法 INSERT INTO $1 VALUES (42); --不合法(表名属于标示符之一) 二、基本类型： 最简单的SQL函数可能就是没有参数且返回基本类型的函数了，如： CREATE FUNCTION one() RETURNS integer AS $$ SELECT 1 AS result; $$ LANGUAGE SQL; 下面的例子声明了基本类型作为函数的参数。 CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$ SELECT $1 + $2; $$ LANGUAGE SQL; # 通过select调用函数。 postgres=# SELECT add_em(1,2) AS answer; answer -------- 3 (1 row) 在下面的例子中，函数体内包含多个SQL语句，它们之间是用分号进行分隔的。CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$ UPDATE bank SET balance = balance - $2 WHERE accountno = $1; SELECT balance FROM bank WHERE accountno = $1; $$ LANGUAGE SQL; psql常用命令大全 \d [ table ] 列出数据库中的表，或（如果声明了）表table 的列/字段．如果表名是用统配符（“*”）声明的，列出所有表和表的列/字段信息． \da 列出所有可用聚集． \dd object 列出pg_description 里对声明的对象的描述，对象可以是一个表，表中的列/字段，类型，操作符或聚集． 小技巧：并非所有对象在pg_description 里有描述．此后期命令在快速获取Postgres 内部特性时很有用． \df 列出函数． \di 只列出索引． \do 只列出操作符． \ds 只列出序列． \dS 列出系统表和索引． \dt 只列出非系统表． \dT 列出类型． \e [ filename ] 编辑当前查询缓冲或文件filename 的内容． \E [ filename ] 编辑当前查询缓冲或文件filename 的内容并且在编辑结束后执行之． \f [ separator ] 设置域分隔符．缺省是单个空白． \g [ { filename | |command } ] 将当前查询输入缓冲送给后端并且（可选的）将输出放到filename 或通过管道将输出送给一个分离的Unix shell 用以执行command． \h [ command ] 给出声明的SQL 命令的语法帮助．如果command 不是一个定义的SQL 命令（或在psql 里没有文档），或没有声明command ，这时psql将列出可获得帮助的所有命令的列表．如果命令command 是一个通配符（“*”），则给出所有SQL 命令的语法帮助． \H 切换HTML3 输出．等效于-H 命令行选项． \i filename 从文件filename 中读取查询到输入缓冲． MySQL和PostgreSQL两数据库的对决 在这篇文章中，我们选用MySQL4.0.2-alpha与PostgreSQL7.2进行比较，因为MySQL4.0.2-alpha开始支持事务的概念，因此这样的比较对于MySQL应该较为有利。我们这样的比较不想仅仅成为一份性能测试报告，因为至少从我个人来看，对于一个数据库，稳定性和速度并不能代表一切。对于一个成熟的数据库，稳定性肯定会日益提供。而随着硬件性能的飞速提高，速度也不再是什么太大的问题。 一、前言 前一段时间，我曾经翻译过一篇将你的网站从MySQL改为PostgreSQL，其实当初我更感兴趣的是一个应用程序的后台数据库从MySQL转为PostgreSQL的具体操作，并没有关心MySQL和PostgreSQL的优劣，没想到反应出乎意料的大，因此我也就觉得有写这篇文章的必要了。 在这篇文章中，我们选用MySQL4.0.2-alpha与PostgreSQL7.2进行比较，因为MySQL4.0.2-alpha开始支持事务的概念，因此这样的比较对于MySQL应该较为有利。 我们这样的比较不想仅仅成为一份性能测试报告，因为至少从我个人来看，对于一个数据库，稳定性和速度并不能代表一切。对于一个成熟的数据库，稳定性肯定会日益提供。而随着硬件性能的飞速提高，速度也不再是什么太大的问题。 二、两者的共同优势 这两个产品都属于开放源码的一员，性能和功能都在高速地提高和增强。MySQLAB的人们和PostgreSQL的开发者们都在尽可能地把各自的数据库改得越来越好，所以对于任何商业数据库使用其中的任何一个都不能算是错误的选择。 三、两者不同的背景 MySQL的背后是一个成熟的商业公司，而PostgreSQL的背后是一个庞大的志愿开发组。这使得MySQL的开发过程更为慎重，而PostgreSQL的反应更为迅速。 这样的两种背景直接导致了各自固有的优点和缺点。 四、MySQL的主要优点 1、首先是速度，MySQL通常要比PostgreSQL快得多。MySQL自已也宣称速度是他们追求的主要目标之一，基于这个原因，MySQL在以前的文档中也曾经说过并不准备支持事务和触发器。但是在最新的文档中，我们看到MySQL4.0.2-alpha已经开始支持事务，而且在MySQL的TODO中，对触发器、约束这样的注定会降低速度的功能也列入了日程。但是，我们仍然有理由相信， Greenplum 日常简明维护手册 1.数据库启动：gpstart 常用参数：-a : 直接启动，不提示终端用户输入确认 -m:只启动master 实例，主要在故障处理时使用 访问单个数据实例： PGOPTIONS='-c gp_session_role=utility' psql template1 -p 5432 启动某个segment instance ：pg_ctl stop/start -D /datadir/ 取端口号： select * from gp_segment_configuration 启动以后会在/tmp/ 下生成一个.lock 隐藏文件，记录主进程号。 2.数据库停止：gpstop： 常用可选参数：-a：直接停止，不提示终端用户输入确认 -m：只停止master 实例，与gpstart –m 对应使用 -f：停止数据库，中断所有数据库连接，回滚正在运 行的事务 -u：不停止数据库，只加载pg_hba.conf 和postgresql.conf中 运行时参数，当改动参数配置时候使用。 连接数，重启 3.查看实例配置和状态 select * from gp_segment_configuration order by content ; select * from pg_filespace_entry ; 主要字段说明： Content：该字段相等的两个实例，是一对Ｐ（primary instance）和Ｍ（mirror Instance) Isprimary：实例是否作为primary instance 运行 Valid：实例是否有效，如处于false 状态，则说明该实例已经down 掉。 Port：实例运行的端口 Datadir:实例对应的数据目录 4.gpstate ：显示Greenplum数据库运行状态，详细配置等信息 常用可选参数：-c：primary instance 和mirror instance 的对应关系 -m：只列出mirror 实例的状态和配置信息 -f：显示standby master 的详细信息 该命令默认列出数据库运行状态汇总信息，常用于日常巡检。 5.查看用户会话和提交的查询等信息 select * from pg_stat_activity该表能查看到当前数据库连接的IP 地址，用户 Sqoop中文手册 1. 概述 本文档主要对SQOOP的使用进行了说明，参考内容主要来自于Cloudera SQOOP的官方文档。为了用中文更清楚明白地描述各参数的使用含义，本文档几乎所有参数使用说明都经过了我的实际验证而得到。 2. codegen 将关系数据库表映射为一个java文件、java class类、以及相关的jar包， 1、将数据库表映射为一个Java文件，在该Java文件中对应有表的各个字段。 2、生成的Jar和class文件在metastore功能使用时会用到。 基础语句： sqoop codegen –connect jdbc:mysql://localhost:3306/hive –username root –password 123456 –table TBLS2 3. create-hive-table 生成与关系数据库表的表结构对应的HIVE表 基础语句： sqoop create-hive-table –connect jdbc:mysql://localhost:3306/hive -username root -password 123456 –table TBLS –hive-table h_tbls2 4. eval 可以快速地使用SQL语句对关系数据库进行操作，这可以使得在使用import这种工具进行数据导入的时候，可以预先了解相关的SQL语句是否正确，并能将结果显示在控制台。 查询示例： sqoop eval –connect jdbc:mysql://localhost:3306/hive -username root -password 123456 -query ―SELECT * FROM tbls LIMIT 10″ 数据插入示例： sqoop eval –connect jdbc:mysql://localhost:3306/hive -username root -password 123456 -e ―INSERT INTO TBLS2 VALUES(100,1375170308,1,0,‘hadoop‘,0,1,‘guest‘,‘MANAGED_TABLE‘,‘abc‘,‘ddd‘)‖ -e、-query这两个参数经过测试，比如后面分别接查询和插入SQL语句，皆可运行无误，如上。 5. export 从hdfs中导数据到关系数据库中 sqoop export –connect jdbc:mysql://localhost:3306/hive –username root –password 123456 –table TBLS2 –export-dir sqoop/test 用PL/pgSQL写postgreSQL的存储过程 一、存储过程结构： Create or replace function 过程名(参数名参数类型,…..) returns 返回值类型as $body$ //声明变量 Declare 变量名变量类型； 如： flag Boolean; 变量赋值方式（变量名类型：=值；） 如： Str text :=值; / str text; str :=值； Begin 函数体； End; $body$ Language plpgsql; 二、变量类型： 除了postgresql内置的变量类型外，常用的还有 RECORD ，表示一条记录 三、连接字符： Postgresql存储过程中的连接字符不再是“+”,而是使用“||”。 四、控制结构： 1、if 条件（五种形式） IF ... THEN IF ... THEN ... ELSE IF ... THEN ... ELSE IF IF ... THEN ... ELSIF ... THEN ... ELSE IF ... THEN ... ELSEIF ... THEN ... ELSE（注：ELSEIF 是 ELSIF 的别名） 2、循环 使用LOOP，EXIT，CONTINUE，WHILE，和 FOR 语句，可以控制PL/pgSQL 函数重复一系列命令。 1）、LOOP [ < GP服务启停 su - gpadmin gpstart #正常启动 gpstop #正常关闭 gpstop -M fast #快速关闭 gpstop –r #重启 gpstop –u #重新加载配置文件 登陆与退出Greenplum #正常登陆 psql gpdb psql -d gpdb -h gphostm -p 5432 -U gpadmin #使用utility方式 PGOPTIONS="-c gp_session_role=utility" psql -h -d dbname hostname -p port #退出 在psql命令行执行\q 参数查询 psql -c 'SHOW ALL;' -d gpdb gpconfig --show max_connections 创建数据库 createdb -h localhost -p 5432 dhdw 创建GP文件系统 # 文件系统名 gpfsdw # 子节点，视segment数创建目录 mkdir -p /gpfsdw/seg1 mkdir -p /gpfsdw/seg2 chown -R gpadmin:gpadmin /gpfsdw # 主节点 mkdir -p /gpfsdw/master chown -R gpadmin:gpadmin /gpfsdw gpfilespace -o gpfilespace_config gpfilespace -c gpfilespace_config 创建GP表空间 psql gpdb create tablespace TBS_DW_DATA filespace gpfsdw; SET default_tablespace = TBS_DW_DATA; 删除GP数据库 gpdeletesystem -d /gpmaster/gpseg-1 -f 查看segment配置 select * from gp_segment_configuration; 文件系统 select * from pg_filespace_entry; 磁盘、数据库空间 SELECT * FROM gp_toolkit.gp_disk_free ORDER BY dfsegment; SELECT * FROM gp_toolkit.gp_size_of_database ORDER BY sodddatname;日志 SELECT * FROM gp_toolkit.__gp_log_master_ext; SELECT * FROM gp_toolkit.__gp_log_segment_ext; 表描述 /d+ PostgreSQL安装和简单使用
监视并记录Apache网站服务器的运行
OB开发手册中文版
PostgreSQL数据库使用pg_dump—psql 转储数据库
PostgreSQL学习手册
PostgreSQL+Linux 从入门到精通培训文档 2命令
PostgreSQL学习手册(PLpgSQL过程语言)
PostgreSQL详解
PostgreSql 基础知识
PostgreSQL数据库安装教程
odbc安装文档
PostgreSQL学习手册：SQL语言函数
psql常用命令大全
MySQL和PostgreSQL两数据库的对决
GP 常用数据库命令
Sqoop官方中文手册
Postgresql存储过程
GP简明使用手册
相关主题
文本预览