The most recent version of this document can be viewed at http://www.PostgreSQL.org/docs/faq-english.html.
Platform-specific questions are answered at http://www.PostgreSQL.org/users-lounge/docs/faq.html.
The most recent version of this document can be viewed at http://www.PostgreSQL.org/docs/faq-english.html.
Platform-specific questions are answered at http://www.PostgreSQL.org/users-lounge/docs/faq.html.
PostgreSQL is pronounced Post-Gres-Q-L.
--- 160,166 ----PostgreSQL is pronounced Post-Gres-Q-L.
*************** *** 172,183 **** extended subset of SQL. PostgreSQL is free and the complete source is available. !PostgreSQL development is performed by a team of Internet developers who all subscribe to the PostgreSQL development mailing list. The current coordinator is Marc G. Fournier (scrappy@PostgreSQL.org). (See ! below on how to join). This team is now responsible for all ! development of PostgreSQL.
The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen. Many others have contributed to the porting, testing, debugging, --- 171,182 ---- extended subset of SQL. PostgreSQL is free and the complete source is available.
!PostgreSQL development is performed by a team of developers who all subscribe to the PostgreSQL development mailing list. The current coordinator is Marc G. Fournier (scrappy@PostgreSQL.org). (See ! section 1.6 on how to join). This team is now ! responsible for all development of PostgreSQL.
The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen. Many others have contributed to the porting, testing, debugging, *************** *** 239,248 ****
Client
It is possible to compile the libpq C library, psql, and ! other interfaces and binaries to run on MS Windows platforms. In ! this case, the client is running on MS Windows, and communicates via TCP/IP to a server running on one of our supported Unix ! platforms. A file win31.mak is included in the distribution for making a Win32 libpq library and psql. PostgreSQL also communicates with ODBC clients.
--- 238,247 ----Client
It is possible to compile the libpq C library, psql, and ! other interfaces and client applications to run on MS Windows platforms. ! In this case, the client is running on MS Windows, and communicates via TCP/IP to a server running on one of our supported Unix ! platforms. A file win32.mak is included in the distribution for making a Win32 libpq library and psql. PostgreSQL also communicates with ODBC clients.
*************** *** 250,260 ****The database server can run on Windows NT and Win2k using Cygwin, the Cygnus Unix/NT porting library. See ! pgsql/doc/FAQ_MSWIN in the distribution or the MS Windows FAQ ! on our web site. We have no plan to do a native port to any ! Microsoft platform.
The primary anonymous ftp site for PostgreSQL is The database server can run on Windows NT and Win2k using Cygwin, the Cygnus Unix/NT porting library. See ! pgsql/doc/FAQ_MSWIN in the distribution or the MS Windows FAQ ! at http://www.PostgreSQL.org/docs/faq-mswin.html.
+A native port to some Microsoft platforms is currently being worked + upon.
+The primary anonymous ftp site for PostgreSQL is
A list of commercial support companies is available at http://www.postgresql.org/users-lounge/commercial-support.html.
A list of commercial support companies is available at http://www.PostgreSQL.org/users-lounge/commercial-support.html.
psql has some nice \d commands to show information about types, operators, functions, aggregates, etc.
--- 339,348 ---- "http://www.commandprompt.com/ppbook/">http://www.commandprompt.com/ppbook/. There is a list of PostgreSQL books available for purchase at http://www.ca.PostgreSQL.org/books/. There is also a collection of PostgreSQL technical articles at http://techdocs.PostgreSQL.org/.psql has some nice \d commands to show information about types, operators, functions, aggregates, etc.
*************** *** 402,411 ****Please visit the PostgreSQL BugTool ! page, which gives guidelines and directions on how to submit a ! bug.
Also check out our ftp site ftp://ftp.PostgreSQL.org/pub to --- 402,411 ----
Please visit the PostgreSQL BugTool page at http://www.PostgreSQL.org/bugs/bugs.php, ! which gives guidelines and directions on how to submit a ! bug report.
Also check out our ftp site ftp://ftp.PostgreSQL.org/pub to *************** *** 460,473 ****
PostgreSQL has had a first-class infrastructure since we started ! six years ago. This is all thanks to Marc Fournier, who has created and managed this infrastructure over the years.
Quality infrastructure is very important to an open-source --- 484,490 ---- PostgreSQL?
PostgreSQL has had a first-class infrastructure since we started ! in 1994. This is all thanks to Marc Fournier, who has created and managed this infrastructure over the years.
Quality infrastructure is very important to an open-source *************** *** 494,502 ****
Of course, this infrastructure is not cheap. There are a variety of monthly and one-time expenses that are required to keep it going. If you or your company has money it can donate to help fund ! this effort, please go to ! https://store.pgsql.com/shopping/index.php?id=1 and make a donation.
Although the web page mentions PostgreSQL, Inc, the --- 494,500 ----
Of course, this infrastructure is not cheap. There are a variety of monthly and one-time expenses that are required to keep it going. If you or your company has money it can donate to help fund ! this effort, please go to http://store.pgsql.com/shopping/ and make a donation.
Although the web page mentions PostgreSQL, Inc, the *************** *** 538,565 ****
A nice introduction to Database-backed Web pages can be seen at: http://www.webreview.com
-There is also one at http://www.phone.net/home/mwm/hotlist/.
-For Web integration, PHP is an excellent interface. It is at http://www.php.net.
!For complex cases, many use the Perl interface and CGI.pm.
We have a nice graphical user interface called pgaccess, ! which is shipped as part of the distribution. pgaccess also ! has a report generator. The Web page is http://www.flex.ro/pgaccess
We also include ecpg, which is an embedded SQL query language interface for C.
!We have:
--- 536,558 ----A nice introduction to Database-backed Web pages can be seen at: http://www.webreview.com
For Web integration, PHP is an excellent interface. It is at http://www.php.net.
!For complex cases, many use the Perl interface and CGI.pm or mod_perl.
We have a nice graphical user interface called PgAccess, which is ! shipped as part of the distribution. PgAccess also has a report ! generator. The Web page is http://www.pgaccess.org/.
We also include ecpg, which is an embedded SQL query language interface for C.
!We have:
*************** *** 572,578 ****Additional interfaces are available at ! http://www.postgresql.org/interfaces.html.
Additional interfaces are available at ! http://www.PostgreSQL.org/interfaces.html.
They are temporary files generated by the query executor. For --- 765,771 ---- the MaxBackendId constant in include/storage/sinvaladt.h.
!They are temporary files generated by the query executor. For *************** *** 793,808 ****
The PostgreSQL team makes only small changes between minor releases, so upgrading from 7.2 to 7.2.1 does not require a dump and restore. ! However, major releases often change the internal format of system ! tables and data files. These changes are often complex, so we don't ! maintain backward compatability for data files. A dump outputs data ! in a generic format that can then be loaded in using the new internal ! format. !
In releases where the on-disk format does not change, the pg_upgrade script can be used to upgrade without a dump/restore. The release notes mention whether pg_upgrade is available for the ! release.
The PostgreSQL team makes only small changes between minor releases, so upgrading from 7.2 to 7.2.1 does not require a dump and restore. ! However, major releases (e.g. from 7.2 to 7.3) often change the internal ! format of system tables and data files. These changes are often complex, ! so we don't maintain backward compatability for data files. A dump outputs ! data in a generic format that can then be loaded in using the new internal ! format.
!In releases where the on-disk format does not change, the pg_upgrade script can be used to upgrade without a dump/restore. The release notes mention whether pg_upgrade is available for the ! release.
The entire query may have to be evaluated, even if you only want ! the first few rows. Consider a query that has an ORDER BY. If there is an index that matches the ORDER BY, PostgreSQL may be able to evaluate only the first few records requested, or the entire query may have to be evaluated --- 812,818 ---- SELECT ... LIMIT....
The entire query may have to be evaluated, even if you only want ! the first few rows. Consider using a query that has an ORDER BY. If there is an index that matches the ORDER BY, PostgreSQL may be able to evaluate only the first few records requested, or the entire query may have to be evaluated *************** *** 840,847 ****
We do not support ALTER TABLE DROP COLUMN, but do ! this:
BEGIN;
LOCK TABLE old_table;
--- 831,838 ----
4.4) How do you remove a column from a
table?
! Prior to version 7.3, ALTER TABLE DROP COLUMN is not supported.
! You can do this instead:
BEGIN;
LOCK TABLE old_table;
***************
*** 892,898 ****
be estimated as 6.4 MB:
36 bytes: each row header (approximate)
! 24 bytes: one int field and one text filed
+ 4 bytes: pointer on page to tuple
----------------------------------------
64 bytes per row
--- 883,889 ----
be estimated as 6.4 MB:
36 bytes: each row header (approximate)
! 24 bytes: one int field and one text field
+ 4 bytes: pointer on page to tuple
----------------------------------------
64 bytes per row
***************
*** 957,963 ****
SELECT col
FROM tab
ORDER BY col [ DESC ]
! LIMIT 1
When using wild-card operators such as LIKE or
--- 948,954 ----
SELECT col
FROM tab
ORDER BY col [ DESC ]
! LIMIT 1;
When using wild-card operators such as LIKE or
***************
*** 972,981 ****
--- 963,972 ----
*************** *** 1032,1044 ****
SELECT *
FROM tab
! WHERE lower(col) = 'abc'
This will not use an standard index. However, if you create a
functional index, it will be used:
! CREATE INDEX tabindex on tab (lower(col));
SELECT *
FROM tab
! WHERE LOWER(col) = 'abc';
This will not use an standard index. However, if you create a
functional index, it will be used:
! CREATE INDEX tabindex ON tab (LOWER(col));
CHAR() is best when storing strings that are ! usually the same length. VARCHAR() is best when storing variable-length strings but it limits how long a string can be. TEXT is for strings of unlimited length, maximum 1 gigabyte. BYTEA is for storing binary data, --- 1060,1067 ---- stored out-of-line by TOAST, so the space on disk might also be less than expected.
!CHAR(n) is best when storing strings that are ! usually the same length. VARCHAR(n) is best when storing variable-length strings but it limits how long a string can be. TEXT is for strings of unlimited length, maximum 1 gigabyte. BYTEA is for storing binary data, *************** *** 1111,1121 ****
One approach is to retrieve the next SERIAL value from the sequence object with the nextval() function before inserting and then insert it explicitly. Using the ! example table in 4.15.1, that might look like ! this in Perl:
! new_id = output of "SELECT nextval('person_id_seq')"
! INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal');
You would then also have the new value stored in
--- 1102,1112 ----
One approach is to retrieve the next SERIAL value from the sequence object with the nextval() function before inserting and then insert it explicitly. Using the ! example table in 4.15.1, an example in a ! pseudo-language would look like this:
! new_id = execute("SELECT nextval('person_id_seq')");
! execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");
You would then also have the new value stored in
***************
*** 1124,1137 ****
automatically created SEQUENCE object will be named
<table>_<serialcolumn>_seq, where
table and serialcolumn are the names of your table
! and your SERIAL column, respectively.
Alternatively, you could retrieve the assigned SERIAL value with the currval() function after it was inserted by default, e.g.,
! INSERT INTO person (name) VALUES ('Blaise Pascal');
! new_id = output of "SELECT currval('person_id_seq')";
Finally, you could use the OID
--- 1115,1128 ----
automatically created SEQUENCE object will be named
<table>_<serialcolumn>_seq, where
table and serialcolumn are the names of your table
! and your SERIAL column, respectively.
Alternatively, you could retrieve the assigned SERIAL value with the currval() function after it was inserted by default, e.g.,
! execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
! new_id = execute("SELECT currval('person_id_seq')");
Finally, you could use the OID
***************
*** 1139,1150 ****
default value, though this is probably the least portable approach.
In Perl, using DBI with Edmund Mergl's DBD::Pg module, the oid
value is made available via $sth->{pg_oid_status} after
! $sth->execute().
No. Currval() returns the current value assigned by your backend, not by all users.
No. currval() returns the current value assigned by your backend, not by all users.
OIDs are stored as 4-byte integers, and will overflow at 4 billion. No one has reported this ever happening, and we plan to have the limit removed before anyone does.
--- 1177,1187 ---- COPY new TO '/tmp/pgtable'; DELETE FROM new; COPY new WITH OIDS FROM '/tmp/pgtable'; +OIDs are stored as 4-byte integers, and will overflow at 4 billion. No one has reported this ever happening, and we plan to have the limit removed before anyone does.
*************** *** 1228,1234 ****A list of general database terms can be found at: http://www.comptechnews.com/~reaster/dbdesign.html
A list of general database terms can be found at: http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary/glossary.html
From psql, type select version();
From psql, type SELECT version();
IN is fastest. To
speed up other queries, replace IN with
EXISTS:
!
! SELECT *
FROM tab
! WHERE col IN (SELECT subcol FROM subtab)
!
! to:
!
! SELECT *
FROM tab
! WHERE EXISTS (SELECT subcol FROM subtab WHERE subcol = col)
!
For this to be fast, subcol should be an indexed column.
! We hope to fix this limitation in a future release.
IN is fastest. To
speed up other queries, replace IN with
EXISTS:
! SELECT *
FROM tab
! WHERE col IN (SELECT subcol FROM subtab);
! to:
! SELECT *
FROM tab
! WHERE EXISTS (SELECT subcol FROM subtab WHERE subcol = col);
For this to be fast, subcol should be an indexed column.
! We hope to fix this limitation in a future release.
You can return result sets from PL/pgSQL functions using refcursors. See ! http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html, section 23.7.3.3.
You can return result sets from PL/pgSQL functions using refcursors. See ! http://www.PostgreSQL.org/idocs/index.php?plpgsql-cursors.html, section 23.7.3.3.
There are several master/slave replication options available. These allow only the master to make database changes and the slave can only do database reads. The bottom of ! http://gborg.postgresql.org/genpage?replication_research lists them. A multi-master replication solution is being worked on at http://gborg.postgresql.org/project/pgreplication/projdisplay.php.
!There are several master/slave replication options available. These allow only the master to make database changes and the slave can only do database reads. The bottom of ! http://gborg.PostgreSQL.org/genpage?replication_research lists them. A multi-master replication solution is being worked on at http://gborg.PostgreSQL.org/project/pgreplication/projdisplay.php.
!