Check your locale configuration. POSTGRESQL uses the locale setting of the user that ran the postmaster process. There are postgres and psql SET commands to control the date format. Set those accordingly for your operating environment.
See the DECLARE manual page for a description.
See the FETCH manual page, or use SELECT...LIMIT....
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 until the desired rows have been generated.
You can read the source code for psql in file pgsql/src/bin/psql/psql.c. It contains SQL commands that generate the output for psql's backslash commands. You can also start psql with the -E option so it will print out the queries it uses to execute the commands you give.
We do not support ALTER TABLE DROP COLUMN, but do this:
SELECT ... -- select all columns but the one you want to remove
INTO TABLE new_table
FROM old_table;
DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;
These are the limits:
To change the maximum row size, edit include/config.h and change BLCKSZ. To use attributes larger than 8K, you can also use the large object interface.
The row length limit will be removed in 7.1.
A POSTGRESQL database may need six-and-a-half times the disk space required to store the data in a flat file.
Consider a file of 300,000 lines with two integers on each line. The flat file is 2.4MB. The size of the POSTGRESQL database file containing this data can be estimated at 14MB:
36 bytes: each row header (approximate)
+ 8 bytes: two int fields @ 4 bytes each
+ 4 bytes: pointer on page to tuple
----------------------------------------
48 bytes per row
The data page size in PostgreSQL is 8192 bytes (8 KB), so:
8192 bytes per page
------------------- = 171 rows per database page (rounded up)
48 bytes per row
300000 data rows
-------------------- = 1755 database pages
171 rows per page
1755 database pages * 8192 bytes per page = 14,376,960 bytes (14MB)
Indexes do not require as much overhead, but do contain the data that is being
indexed, so they can be large also.
psql has a variety of backslash commands to show such information. Use \? to see them.
Also try the file pgsql/src/tutorial/syscat.source. It illustrates many of the SELECTs needed to get information from the database system tables.
POSTGRESQL does not automatically maintain statistics. VACUUM must be run to update the statistics. After statistics are updated, the optimizer knows how many rows in the table, and can better decide if it should use indices. Note that the optimizer does not use indices in cases when the table is small because a sequential scan would be faster.
For column-specific optimization statistics, use VACUUM ANALYZE . VACUUM ANALYZE is important for complex multijoin queries, so the optimizer can estimate the number of rows returned from each table, and choose the proper join order. The backend does not keep track of column statistics on its own, so VACUUM ANALYZE must be run to collect them periodically.
Indexes are usually not used for ORDER BY operations: a sequential scan followed by an explicit sort is faster than an indexscan of all tuples of a large table, because it takes fewer disk accesses.
When using wild-card operators such as LIKE or ~, indices can only be used if the beginning of the search is anchored to the start of the string. So, to use indices, LIKE searches should not begin with %, and ~(regular expression searches) should start with ^.
An R-tree index is used for indexing spatial data. A hash index can't handle range searches. A B-tree index only handles range searches in a single dimension. R-tree's can handle multi-dimensional data. For example, if an R-tree index can be built on an attribute of type point, the system can more efficiently answer queries such as ``select all points within a bounding rectangle.''
The canonical paper that describes the original R-tree design is:
Guttman, A. ``R-trees: A Dynamic Index Structure for Spatial Searching.'' Proc of the 1984 ACM SIGMOD Int'l Conf on Mgmt of Data, 45-57.
You can also find this paper in Stonebraker's ``Readings in Database Systems''
Built-in R-trees can handle polygons and boxes. In theory, R-trees can be extended to handle higher number of dimensions. In practice, extending R-trees requires a bit of work and we don't currently have any documentation on how to do it.
The GEQO module speeds query optimization when joining many tables by means of a Genetic Algorithm (GA). It allows the handling of large join queries through nonexhaustive search.
The ~ operator does regular expression matching, and ~* does case-insensitive regular expression matching. There is no case-insensitive variant of the LIKE operator, but you can get the effect of case-insensitive LIKE with this:
WHERE lower(textfield) LIKE lower(pattern)
You test the column with IS NULL and IS NOT NULL.
You will see the internal name when examining system catalogs and in some error messages.
The last four types above are VARLENA types (i.e., the first four bytes are the length, followed by the data). CHAR (#) allocates the maximum number of bytes no matter how much data is stored in the field. TEXT, VARCHAR (#), and BYTEA all have variable length on the disk, and because of this, there is a small performance penalty for using them. Specifically, the penalty is for access to all columns after the first column of this type.
POSTGRESQL supports a SERIAL data type. It auto-creates a sequence and index on the column. For example, this:
CREATE TABLE person ( id SERIAL, name TEXT );
is automatically translated into this:
CREATE SEQUENCE person_id_seq;
CREATE TABLE person ( id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
name TEXT );
CREATE UNIQUE INDEX person_id_key ON person ( id );
See the CREATE_SEQUENCE manual page for more information about sequences.
You can also use each row's OID field as a unique value. However, if
you need to dump and reload the database, you need to use pg_dump's
-o option or COPY WITH OIDS option to preserve the OIDs.
One approach is to 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.16.1, that might look like this:
$newSerialID = nextval('person_id_seq');
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
You would then also have the new value stored in $newSerialID for use in other
queries (e.g., as a foreign key to the person table). Note that the name of
the 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');
$newID = currval('person_id_seq');
Finally, you could use the OID returned from the INSERT statement
to look up the 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. This is handled by the backends.
OIDs are POSTGRESQL'S answer to unique row ids. Every row that is created in POSTGRESQL gets a unique OID. All OIDs generated during initdb are less than 16384 (from backend/access/transam.h). All user-created OIDs are equal to or greater than this. By default, all these OIDs are unique not only within a table or database, but unique within the entire POSTGRESQL installation.
POSTGRESQL uses OIDs in its internal system tables to link rows between tables. These OIDs can be used to identify specific user rows and used in joins. It is recommended you use column type OID to store OID values. You can create an index on the OID field for faster access.
OIDs are assigned to all new rows from a central area that is used by all databases. If you want to change the OID to something else, or if you want to make a copy of the table, with the original OID's, there is no reason you can't do it:
CREATE TABLE new_table(old_oid oid, mycol int);
SELECT old_oid, mycol INTO new FROM old;
COPY new TO '/tmp/pgtable';
DELETE FROM new;
COPY new WITH OIDS FROM '/tmp/pgtable';
TIDs are used to identify specific physical rows with block and offset
values. TIDs change after rows are modified or reloaded. They are used
by index entries to point to physical rows.
Some of the source code and older documentation use terms that have more common usage. Here are some:
It is possible you have run out of virtual memory on your system, or your kernel has a low limit for certain resources. Try this before starting the postmaster:
ulimit -d 65536
limit datasize 64m
Depending on your shell, only one of these may succeed, but it will set your
process data segment limit much higher and perhaps allow the query to complete.
This command applies to the current process, and all subprocesses created after
the command is run. If you are having a problem with the SQL client
because the backend is returning too much data, try it before starting the client.
From psql, type select version();
You need to put BEGIN WORK and COMMIT around any use of a large object handle, that is, surrounding lo_open ... lo_close. CurrentLY POSTGRESQL enforces the rule by closing large object handles at transaction commit. So the first attempt to do anything with the handle will draw invalid large obj descriptor. So code that used to work (at least most of the time) will now generate that error message if you fail to use a transaction.
If you are using a client interface like ODBC you may need to set auto-commit off.
Use now():
CREATE TABLE test (x int, modtime timestamp DEFAULT now() );
Currently, we join subqueries to outer queries by sequentially scanning the result of the subquery for each row of the outer query. A workaround is to replace IN with EXISTS:
SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2)
to:
SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2)
We hope to fix this limitation in a future release.
POSTGRESQL does not support outer joins in the current release. They can be simulated using UNION and NOT IN. For example, when joining tab1 and tab2, the following query does an outer join of the two tables:
SELECT tab1.col1, tab2.col2
FROM tab1, tab2
WHERE tab1.col1 = tab2.col1
UNION ALL
SELECT tab1.col1, NULL
FROM tab1
WHERE tab1.col1 NOT IN (SELECT tab2.col1 FROM tab2)
ORDER BY tab1.col1