# $Id: dbd-pg.pod,v 1.1 2004-04-29 09:21:28 ivan Exp $ =head1 NAME DBD::Pg - PostgreSQL database driver for the DBI module =head1 DESCRIPTION DBD::Pg is a Perl module which works with the DBI module to provide access to PostgreSQL databases. =head1 DBD::Pg =begin docbook =end docbook =head1 =head2 Version Version 0.91. =head2 Author and Contact Details The driver author is Edmund Mergl. He can be contacted via the I mailing list. =head2 Supported Database Versions and Options The DBD-Pg-0.92 module supports Postgresql 6.5. =head2 Connect Syntax The Cconnect()> Data Source Name, or I, can be one of the following: dbi:Pg:dbname=$dbname dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options;tty=$tty All parameters, including the userid and password parameter of the connect command, have a hard-coded default which can be overridden by setting appropriate environment variables: Parameter Environment Variable Default --------- -------------------- -------------- dbname PGDATABASE current userid host PGHOST localhost port PGPORT 5432 options PGOPTIONS "" tty PGTTY "" username PGUSER current userid password PGPASSWORD "" There are no driver specific attributes for the Cconnect()> method. =head2 Numeric Data Handling Postgresql supports the following numeric types: Postgresql Range ---------- -------------------------- int2 -32768 to +32767 int4 -2147483648 to +2147483647 float4 6 decimal places float8 15 decimal places Some platforms also support the int8 type. C always returns all numbers as strings. =head2 String Data Handling Postgresql supports the following string data types: CHAR single character CHAR(size) fixed length blank-padded VARCHAR(size) variable length with limit TEXT variable length All string data types have a limit of 4096 bytes. The CHAR type is fixed length and blank padded. There is no special handling for data with the 8th bit set. They are stored unchanged in the database. None of the character types can store embedded nulls and Unicode is not formally supported. Strings can be concatenated using the C<||> operator. =head2 Date Data Handling Postgresql supports the following date time data types: Type Storage Recommendation Description --------- -------- -------------------------- ---------------------------- abstime 4 bytes original date and time limited range date 4 bytes SQL92 type wide range datetime 8 bytes best general date and time wide range, high precision interval 12 bytes SQL92 type equivalent to timespan reltime 4 bytes original time interval limited range, low precision time 4 bytes SQL92 type wide range timespan 12 bytes best general time interval wide range, high precision timestamp 4 bytes SQL92 type limited range Data Type Range Resolution ---------- ---------------------------------- ----------- abstime 1901-12-14 2038-01-19 1 sec timestamp 1901-12-14 2038-01-19 1 sec reltime -68 years +68 years 1 sec tinterval -178000000 years +178000000 years 1 microsec timespan -178000000 years 178000000 years 1 microsec date 4713 BC 32767 AD 1 day datetime 4713 BC 1465001 AD 1 microsec time 00:00:00:00 23:59:59:99 1 microsec Postgresql supports a range of date formats: Name Example ----------- ---------------------- ISO 1997-12-17 0:37:16-08 SQL 12/17/1997 07:37:16.00 PST Postgres Wed Dec 17 07:37:16 1997 PST European 17/12/1997 15:37:16.00 MET NonEuropean 12/17/1997 15:37:16.00 MET US 12/17/1997 07:37:16.00 MET The default output format does not depend on the client/server locale. It depends on, in increasing priority: the PGDATESTYLE environment variable at the server, the PGDATESTYLE environment variable at the client, and the C SQL command. All of the formats described above can be used for input. A great many others can also be used. There is no specific default input format. If the format of a date input is ambiguous then the current DATESTYLE is used to help disambiguate. If you specify a date/time value without a time component, the default time is 00:00:00 (midnight). To specify a date/time value without a date is not allowed. If a date with a two digit year is input then if the year was less than 70, add 2000; otherwise, add 1900. The currect date/time is returned by the keyword C<'now'> or C<'current'>, which has to be casted to a valid data type. For example: SELECT 'now'::datetime Postgresql supports a range of date time functions for converting between types, extracting parts of a date time value, truncating to a given unit, etc. The usual arithmetic can be performed on date and interval values, e.g., date-date=interval, etc. The following SQL expression can be used to convert an integer "seconds since 1-jan-1970 GMT" value to the corresponding database date time: DATETIME(unixtime_field) and to do the reverse: DATE_PART('epoch', datetime_field) The server stores all dates internally in GMT. Times are converted to local time on the database server before being sent to the client frontend, hence by default are in the server time zone. The TZ environment variable is used by the server as default time zone. The PGTZ environment variable on the client side is used to send the time zone information to the backend upon connection. The SQL C command can set the time zone for the current session. =head2 LONG/BLOB Data Handling Postgresql handles BLOBS using a so called "large objects" type. The handling of this type differs from all other data types. The data are broken into chunks, which are stored in tuples in the database. Access to large objects is given by an interface which is modelled closely after the UNIX file system. The maximum size is limited by the file size of the operating system. If you just select the field, you get a "large object identifier" and not the data itself. The I and I attributes are not implemented because they don't make sense in this case. The only method implemented by the driver is the undocumented DBI method C. =head2 Other Data Handling issues The C driver supports the C method. Postgresql supports automatic conversions between data types wherever it's reasonable. =head2 Transactions, Isolation and Locking Postgresql supports transactions. The current default isolation transaction level is "Serializable" and is currently implemented using table level locks. Both may change. No other isolation levels for transactions are supported. With AutoCommit on, a query never places a lock on a table. Readers never block writers and writers never block readers. This behavior changes whenever a transaction is started (AutoCommit off). Then a query induces a shared lock on a table and blocks anyone else until the transaction has been finished. The C statement can be used to apply an explicit lock on a table. This only works inside a transaction (AutoCommit off). To ensure that a table being selected does not change before you make an update later in the transaction, you must explicitly lock it with a C statement before executing the select. =head2 No-Table Expression Select Syntax To select a constant expression, that is, an expression that doesn't involve data from a database table or view, just omit the "from" clause. Here's an example that selects the current time as a datetime: SELECT 'now'::datetime; =head2 Table Join Syntax Outer joins are not supported. Inner joins use the traditional syntax. =head2 Table and Column Names The max size of table and column names cannot exceed 31 charaters in length. Only alphanumeric characters can be used; the first character must be a letter. If an identifier is enclosed by double quotation marks (C<">), it can contain any combination of characters except double quotation marks. Postgresql converts all identifiers to lower-case unless enclosed in double quotation marks. National character set characters can be used, if enclosed in quotation marks. =head2 Case Sensitivity of LIKE Operator Postgresql has the following string matching operators: Glyph Description Example ----- ---------------------------------------- ----------------------------- ~~ Same as SQL "LIKE" operator 'scrappy,marc' ~~ '%scrappy%' !~~ Same as SQL "NOT LIKE" operator 'bruce' !~~ '%al%' ~ Match (regex), case sensitive 'thomas' ~ '.*thomas.*' ~* Match (regex), case insensitive 'thomas' ~* '.*Thomas.*' !~ Does not match (regex), case sensitive 'thomas' !~ '.*Thomas.*' !~* Does not match (regex), case insensitive 'thomas' !~ '.*vadim.*' =head2 Row ID The Postgresql "row id" pseudocolumn is called I, object identifier. It can be treated as a string and used to rapidly (re)select rows. =head2 Automatic Key or Sequence Generation Postgresql does not support automatic key generation such as "auto increment" or "system generated" keys. However, Postgresql does support "sequence generators". Any number of named sequence generators can be created in a database. Sequences are used via functions called C and C. Typical usage: INSERT INTO table (k, v) VALUES (nextval('seq_name'), ?); To get the value just inserted, you can use the corresponding C SQL function in the same session, or SELECT last_value FROM seq_name =head2 Automatic Row Numbering and Row Count Limiting Postgresql does not support any way of automatically numbering returned rows. =head2 Parameter Binding Parameter binding is emulated by the driver. Both the C and C<:1> style of placeholders are supported. The TYPE attribute of the C method may be used to influence how parameters are treated. These SQL types are bound as VARCHAR: SQL_NUMERIC, SQL_DECIMAL, SQL_INTEGER, SQL_SMALLINT, SQL_FLOAT, SQL_REAL, SQL_DOUBLE, SQL_VARCHAR. The SQL_CHAR type is bound as a CHAR thus enabling fixed-width blank padded comparison semantics. Unsupported values of the TYPE attribute generate a warning. =head2 Stored Procedures C does not support stored procedures. =head2 Table Metadata C supports the C method. The I table contains detailed information about all columns of all the tables in the database, one row per table. The I table contains detailed information about all indexes in the database, one row per index. Primary keys are implemented as unique indexes. See I above. =head2 Driver-specific Attributes and Methods There are no significant C driver-specific database handle attributes. C has the following driver-specific statement handle attributes: =over 8 =item I Returns a reference to an array of integer values for each column. The integer shows the storage (not display) size of the column in bytes. Variable length columns are indicated by -1. =item I Returns a reference to an array of strings for each column. The string shows the name of the data type. =item I Returns the OID of the last INSERT command. =item I Returns the name of the last command type. Possible types are: INSERT, DELETE, UPDATE, SELECT. =back C has no private methods. =head2 Positioned updates and deletes Postgresql does not support positioned updates or deletes. =head2 Differences from the DBI Specification C has no significant differences in behavior from the current DBI specification. Note that C does not fully parse the statement until it's executed. Thus attributes like I<$sth-E{NUM_OF_FIELDS}> are not available until after C<$sth-Eexecute> has been called. This is valid behaviour but is important to note when porting applications originally written for other drivers. =head2 URLs to More Database/Driver Specific Information http://www.postgresql.org =head2 Concurrent use of Multiple Handles C supports an unlimited number of concurrent database connections to one or more databases. It also supports the preparation and execution of a new statement handle while still fetching data from another statement handle, provided it is associated with the same database handle. =head2 Other Significant Database or Driver Features Postgres offers substantial additional power by incorporating the following four additional basic concepts in such a way that users can easily extend the system: classes, inheritance, types, and functions. Other features provide additional power and flexibility: constraints, triggers, rules, transaction integrity, procedural languages, and large objects. It's also free Open Source Software with an active community of developers. =cut # This driver summary for DBD::Pg is Copyright (c) 1999 Tim Bunce # and Edmund Mergl. # $Id: dbd-pg.pod,v 1.1 2004-04-29 09:21:28 ivan Exp $