2 # $Id: dbd-pg.pod,v 1.1 2004-04-29 09:21:28 ivan Exp $
6 DBD::Pg - PostgreSQL database driver for the DBI module
10 DBD::Pg is a Perl module which works with the DBI module to provide
11 access to PostgreSQL databases.
16 <!-- The following blank =head1 is to allow us to use purely =head2 headings -->
17 <!-- This keeps the POD fairly simple with regards to Pod::DocBook -->
27 =head2 Author and Contact Details
29 The driver author is Edmund Mergl. He can be contacted via the
30 I<dbi-users> mailing list.
33 =head2 Supported Database Versions and Options
35 The DBD-Pg-0.92 module supports Postgresql 6.5.
40 The C<DBI-E<gt>connect()> Data Source Name, or I<DSN>, can be one of the
44 dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options;tty=$tty
46 All parameters, including the userid and password parameter of the
47 connect command, have a hard-coded default which can be overridden
48 by setting appropriate environment variables:
50 Parameter Environment Variable Default
51 --------- -------------------- --------------
52 dbname PGDATABASE current userid
57 username PGUSER current userid
58 password PGPASSWORD ""
60 There are no driver specific attributes for the C<DBI->connect()> method.
63 =head2 Numeric Data Handling
65 Postgresql supports the following numeric types:
68 ---------- --------------------------
70 int4 -2147483648 to +2147483647
71 float4 6 decimal places
72 float8 15 decimal places
74 Some platforms also support the int8 type.
75 C<DBD::Pg> always returns all numbers as strings.
78 =head2 String Data Handling
80 Postgresql supports the following string data types:
83 CHAR(size) fixed length blank-padded
84 VARCHAR(size) variable length with limit
87 All string data types have a limit of 4096 bytes.
88 The CHAR type is fixed length and blank padded.
90 There is no special handling for data with the 8th bit set. They
91 are stored unchanged in the database.
92 None of the character types can store embedded nulls and Unicode is
93 not formally supported.
95 Strings can be concatenated using the C<||> operator.
98 =head2 Date Data Handling
100 Postgresql supports the following date time data types:
102 Type Storage Recommendation Description
103 --------- -------- -------------------------- ----------------------------
104 abstime 4 bytes original date and time limited range
105 date 4 bytes SQL92 type wide range
106 datetime 8 bytes best general date and time wide range, high precision
107 interval 12 bytes SQL92 type equivalent to timespan
108 reltime 4 bytes original time interval limited range, low precision
109 time 4 bytes SQL92 type wide range
110 timespan 12 bytes best general time interval wide range, high precision
111 timestamp 4 bytes SQL92 type limited range
113 Data Type Range Resolution
114 ---------- ---------------------------------- -----------
115 abstime 1901-12-14 2038-01-19 1 sec
116 timestamp 1901-12-14 2038-01-19 1 sec
117 reltime -68 years +68 years 1 sec
118 tinterval -178000000 years +178000000 years 1 microsec
119 timespan -178000000 years 178000000 years 1 microsec
120 date 4713 BC 32767 AD 1 day
121 datetime 4713 BC 1465001 AD 1 microsec
122 time 00:00:00:00 23:59:59:99 1 microsec
124 Postgresql supports a range of date formats:
127 ----------- ----------------------
128 ISO 1997-12-17 0:37:16-08
129 SQL 12/17/1997 07:37:16.00 PST
130 Postgres Wed Dec 17 07:37:16 1997 PST
131 European 17/12/1997 15:37:16.00 MET
132 NonEuropean 12/17/1997 15:37:16.00 MET
133 US 12/17/1997 07:37:16.00 MET
135 The default output format does not depend on the client/server locale.
136 It depends on, in increasing priority: the PGDATESTYLE environment
137 variable at the server, the PGDATESTYLE environment variable at the client, and
138 the C<SET DATESTYLE> SQL command.
140 All of the formats described above can be used for input. A great many
141 others can also be used. There is no specific default input format.
142 If the format of a date input is ambiguous then the current DATESTYLE
143 is used to help disambiguate.
145 If you specify a date/time value without a time component, the default
146 time is 00:00:00 (midnight). To specify a date/time value without a date
148 If a date with a two digit year is input then if the year was less than
149 70, add 2000; otherwise, add 1900.
151 The currect date/time is returned by the keyword C<'now'> or C<'current'>,
152 which has to be casted to a valid data type. For example:
154 SELECT 'now'::datetime
156 Postgresql supports a range of date time functions for converting
157 between types, extracting parts of a date time value, truncating to a
158 given unit, etc. The usual arithmetic can be performed on date and
159 interval values, e.g., date-date=interval, etc.
161 The following SQL expression can be used to convert an integer "seconds
162 since 1-jan-1970 GMT" value to the corresponding database date time:
164 DATETIME(unixtime_field)
166 and to do the reverse:
168 DATE_PART('epoch', datetime_field)
170 The server stores all dates internally in GMT. Times are converted to
171 local time on the database server before being sent to the client
172 frontend, hence by default are in the server time zone.
174 The TZ environment variable is used by the server as default time
175 zone. The PGTZ environment variable on the client side is used to send
176 the time zone information to the backend upon connection. The SQL C<SET
177 TIME ZONE> command can set the time zone for the current session.
180 =head2 LONG/BLOB Data Handling
182 Postgresql handles BLOBS using a so called "large objects" type. The
183 handling of this type differs from all other data types. The data are
184 broken into chunks, which are stored in tuples in the database. Access
185 to large objects is given by an interface which is modelled closely
186 after the UNIX file system. The maximum size is limited by the file
187 size of the operating system.
190 If you just select the field, you get a "large object identifier" and
191 not the data itself. The I<LongReadLen> and I<LongTruncOk> attributes are
192 not implemented because they don't make sense in this case. The only
193 method implemented by the driver is the undocumented DBI method
197 =head2 Other Data Handling issues
199 The C<DBD::Pg> driver supports the C<type_info()> method.
201 Postgresql supports automatic conversions between data types wherever
204 =head2 Transactions, Isolation and Locking
206 Postgresql supports transactions.
207 The current default isolation transaction level is "Serializable" and
208 is currently implemented using table level locks. Both may change.
209 No other isolation levels for transactions are supported.
211 With AutoCommit on, a query never places a lock on a table. Readers
212 never block writers and writers never block readers. This behavior
213 changes whenever a transaction is started (AutoCommit off). Then a
214 query induces a shared lock on a table and blocks anyone else
215 until the transaction has been finished.
217 The C<LOCK TABLE table_name> statement can be used to apply an explicit
218 lock on a table. This only works inside a transaction (AutoCommit off).
220 To ensure that a table being selected does not change before you make
221 an update later in the transaction, you must explicitly lock it with a
222 C<LOCK TABLE> statement before executing the select.
225 =head2 No-Table Expression Select Syntax
227 To select a constant expression, that is, an expression that doesn't involve
228 data from a database table or view, just omit the "from" clause.
229 Here's an example that selects the current time as a datetime:
231 SELECT 'now'::datetime;
233 =head2 Table Join Syntax
235 Outer joins are not supported. Inner joins use the traditional syntax.
237 =head2 Table and Column Names
239 The max size of table and column names cannot exceed 31 charaters in
241 Only alphanumeric characters can be used; the first character must
244 If an identifier is enclosed by double quotation marks (C<">), it can
245 contain any combination of characters except double quotation marks.
247 Postgresql converts all identifiers to lower-case unless enclosed in
248 double quotation marks.
249 National character set characters can be used, if enclosed in quotation
253 =head2 Case Sensitivity of LIKE Operator
255 Postgresql has the following string matching operators:
257 Glyph Description Example
258 ----- ---------------------------------------- -----------------------------
259 ~~ Same as SQL "LIKE" operator 'scrappy,marc' ~~ '%scrappy%'
260 !~~ Same as SQL "NOT LIKE" operator 'bruce' !~~ '%al%'
261 ~ Match (regex), case sensitive 'thomas' ~ '.*thomas.*'
262 ~* Match (regex), case insensitive 'thomas' ~* '.*Thomas.*'
263 !~ Does not match (regex), case sensitive 'thomas' !~ '.*Thomas.*'
264 !~* Does not match (regex), case insensitive 'thomas' !~ '.*vadim.*'
269 The Postgresql "row id" pseudocolumn is called I<oid>, object identifier.
270 It can be treated as a string and used to rapidly (re)select rows.
273 =head2 Automatic Key or Sequence Generation
275 Postgresql does not support automatic key generation such as "auto
276 increment" or "system generated" keys.
278 However, Postgresql does support "sequence generators". Any number of
279 named sequence generators can be created in a database. Sequences
280 are used via functions called C<NEXTVAL> and C<CURRVAL>. Typical usage:
282 INSERT INTO table (k, v) VALUES (nextval('seq_name'), ?);
284 To get the value just inserted, you can use the corresponding C<currval()>
285 SQL function in the same session, or
287 SELECT last_value FROM seq_name
290 =head2 Automatic Row Numbering and Row Count Limiting
292 Postgresql does not support any way of automatically numbering returned rows.
295 =head2 Parameter Binding
297 Parameter binding is emulated by the driver.
298 Both the C<?> and C<:1> style of placeholders are supported.
300 The TYPE attribute of the C<bind_param()> method may be used to
301 influence how parameters are treated. These SQL types are bound as
302 VARCHAR: SQL_NUMERIC, SQL_DECIMAL, SQL_INTEGER, SQL_SMALLINT,
303 SQL_FLOAT, SQL_REAL, SQL_DOUBLE, SQL_VARCHAR.
305 The SQL_CHAR type is bound as a CHAR thus enabling fixed-width blank
306 padded comparison semantics.
308 Unsupported values of the TYPE attribute generate a warning.
311 =head2 Stored Procedures
313 C<DBD::Pg> does not support stored procedures.
316 =head2 Table Metadata
318 C<DBD::Pg> supports the C<table_info()> method.
320 The I<pg_attribute> table contains detailed information about all columns
321 of all the tables in the database, one row per table.
323 The I<pg_index> table contains detailed information about all indexes in
324 the database, one row per index.
326 Primary keys are implemented as unique indexes. See I<pg_index> above.
329 =head2 Driver-specific Attributes and Methods
331 There are no significant C<DBD::Pg> driver-specific database handle attributes.
333 C<DBD::Pg> has the following driver-specific statement handle attributes:
339 Returns a reference to an array of integer values for each column. The
340 integer shows the storage (not display) size of the column in bytes.
341 Variable length columns are indicated by -1.
345 Returns a reference to an array of strings for each column. The string
346 shows the name of the data type.
348 =item I<pg_oid_status>
350 Returns the OID of the last INSERT command.
352 =item I<pg_cmd_status>
354 Returns the name of the last command type. Possible types are: INSERT,
355 DELETE, UPDATE, SELECT.
360 C<DBD::Pg> has no private methods.
363 =head2 Positioned updates and deletes
365 Postgresql does not support positioned updates or deletes.
368 =head2 Differences from the DBI Specification
370 C<DBD::Pg> has no significant differences in behavior from the
371 current DBI specification.
373 Note that C<DBD::Pg> does not fully parse the statement until
374 it's executed. Thus attributes like I<$sth-E<gt>{NUM_OF_FIELDS}> are not
375 available until after C<$sth-E<gt>execute> has been called. This is valid
376 behaviour but is important to note when porting applications
377 originally written for other drivers.
380 =head2 URLs to More Database/Driver Specific Information
382 http://www.postgresql.org
385 =head2 Concurrent use of Multiple Handles
387 C<DBD::Pg> supports an unlimited number of concurrent database
388 connections to one or more databases.
390 It also supports the preparation and execution of a new statement
391 handle while still fetching data from another statement handle,
393 associated with the same database handle.
396 =head2 Other Significant Database or Driver Features
398 Postgres offers substantial additional power by incorporating the
399 following four additional basic concepts in such a way that users can
400 easily extend the system: classes, inheritance, types, and functions.
402 Other features provide additional power and flexibility: constraints,
403 triggers, rules, transaction integrity, procedural languages, and large objects.
405 It's also free Open Source Software with an active community of developers.
409 # This driver summary for DBD::Pg is Copyright (c) 1999 Tim Bunce
411 # $Id: dbd-pg.pod,v 1.1 2004-04-29 09:21:28 ivan Exp $