DBD::Pg - PostgreSQL database driver for the DBI module
DBD::Pg is a Perl module which works with the DBI module to provide access to PostgreSQL databases.
Version 0.91.
The driver author is Edmund Mergl. He can be contacted via the dbi-users mailing list.
The DBD-Pg-0.92 module supports Postgresql 6.5.
The DBI->connect() Data Source Name, or DSN, can be one of the following:
DBI->connect()
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 DBI-connect()> method.
DBI-
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. DBD::Pg always returns all numbers as strings.
DBD::Pg
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 || operator.
||
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 SET DATESTYLE SQL command.
SET DATESTYLE
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 'now' or 'current', which has to be casted to a valid data type. For example:
'now'
'current'
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 SET TIME ZONE command can set the time zone for the current session.
SET TIME ZONE
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 LongReadLen and LongTruncOk 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 blob_read().
blob_read()
The DBD::Pg driver supports the type_info() method.
type_info()
Postgresql supports automatic conversions between data types wherever it's reasonable.
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 LOCK TABLE table_name statement can be used to apply an explicit lock on a table. This only works inside a transaction (AutoCommit off).
LOCK TABLE table_name
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 LOCK TABLE statement before executing the select.
LOCK TABLE
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;
Outer joins are not supported. Inner joins use the traditional syntax.
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 ("), 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.
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.*'
The Postgresql "row id" pseudocolumn is called oid, object identifier. It can be treated as a string and used to rapidly (re)select rows.
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 NEXTVAL and CURRVAL. Typical usage:
NEXTVAL
CURRVAL
INSERT INTO table (k, v) VALUES (nextval('seq_name'), ?);
To get the value just inserted, you can use the corresponding currval() SQL function in the same session, or
currval()
SELECT last_value FROM seq_name
Postgresql does not support any way of automatically numbering returned rows.
Parameter binding is emulated by the driver. Both the ? and :1 style of placeholders are supported.
?
:1
The TYPE attribute of the bind_param() 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.
bind_param()
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.
DBD::Pg does not support stored procedures.
DBD::Pg supports the table_info() method.
table_info()
The pg_attribute table contains detailed information about all columns of all the tables in the database, one row per table.
The pg_index table contains detailed information about all indexes in the database, one row per index.
Primary keys are implemented as unique indexes. See pg_index above.
There are no significant DBD::Pg driver-specific database handle attributes.
DBD::Pg has the following driver-specific statement handle attributes:
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.
Returns a reference to an array of strings for each column. The string shows the name of the data type.
Returns the OID of the last INSERT command.
Returns the name of the last command type. Possible types are: INSERT, DELETE, UPDATE, SELECT.
DBD::Pg has no private methods.
Postgresql does not support positioned updates or deletes.
DBD::Pg has no significant differences in behavior from the current DBI specification.
Note that DBD::Pg does not fully parse the statement until it's executed. Thus attributes like $sth->{NUM_OF_FIELDS} are not available until after $sth->execute has been called. This is valid behaviour but is important to note when porting applications originally written for other drivers.
$sth->execute
http://www.postgresql.org
DBD::Pg 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.
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.
To install DBD::Pg, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBD::Pg
CPAN shell
perl -MCPAN -e shell install DBD::Pg
For more information on module installation, please visit the detailed CPAN module installation guide.