GNU/Linux |
CentOS 2.1AS(Slurm) |
|
 |
DBD::dbd-pg(3pm) |
 |
DBD-PG
DBD-PG
NAME
DESCRIPTION
DBD::Pg
VersionVersion 0.91.Author and Contact DetailsThe driver author is Edmund Mergl. He can be contacted via thedbi-users mailing list.Supported Database Versions and OptionsThe DBD-Pg-0.92 module supports Postgresql 6.5.Connect SyntaxThe "DBI−>connect()" Data Source Name, or DSN, can be one of thefollowing:dbi:Pg:dbname=$dbnamedbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options;tty=$ttyAll parameters, including the userid and password parameter of theconnect command, have a hard-coded default which can be overridden bysetting appropriate environment variables:Parameter Environment Variable Default--------- -------------------- --------------dbname PGDATABASE current useridhost PGHOST localhostport PGPORT 5432options PGOPTIONS ""tty PGTTY ""username PGUSER current useridpassword PGPASSWORD ""There are no driver specific attributes for the "DBI−"connect()>method.Numeric Data HandlingPostgresql supports the following numeric types:Postgresql Range---------- --------------------------int2 -32768 to +32767int4 -2147483648 to +2147483647float4 6 decimal placesfloat8 15 decimal placesSome platforms also support the int8 type. "DBD::Pg" always returnsall numbers as strings.String Data HandlingPostgresql supports the following string data types:CHAR single characterCHAR(size) fixed length blank-paddedVARCHAR(size) variable length with limitTEXT variable lengthAll string data types have a limit of 4096 bytes. The CHAR type isfixed length and blank padded.There is no special handling for data with the 8th bit set. They arestored unchanged in the database. None of the character types canstore embedded nulls and Unicode is not formally supported.Strings can be concatenated using the "⎪⎪" operator.Date Data HandlingPostgresql supports the following date time data types:Type Storage Recommendation Description--------- -------- -------------------------- ----------------------------abstime 4 bytes original date and time limited rangedate 4 bytes SQL92 type wide rangedatetime 8 bytes best general date and time wide range, high precisioninterval 12 bytes SQL92 type equivalent to timespanreltime 4 bytes original time interval limited range, low precisiontime 4 bytes SQL92 type wide rangetimespan 12 bytes best general time interval wide range, high precisiontimestamp 4 bytes SQL92 type limited rangeData Type Range Resolution---------- ---------------------------------- -----------abstime 1901-12-14 2038-01-19 1 sectimestamp 1901-12-14 2038-01-19 1 secreltime -68 years +68 years 1 sectinterval -178000000 years +178000000 years 1 microsectimespan -178000000 years 178000000 years 1 microsecdate 4713 BC 32767 AD 1 daydatetime 4713 BC 1465001 AD 1 microsectime 00:00:00:00 23:59:59:99 1 microsecPostgresql supports a range of date formats:Name Example----------- ----------------------ISO 1997-12-17 0:37:16-08SQL 12/17/1997 07:37:16.00 PSTPostgres Wed Dec 17 07:37:16 1997 PSTEuropean 17/12/1997 15:37:16.00 METNonEuropean 12/17/1997 15:37:16.00 METUS 12/17/1997 07:37:16.00 METThe default output format does not depend on the client/server locale.It depends on, in increasing priority: the PGDATESTYLE environmentvariable at the server, the PGDATESTYLE environment variable at theclient, and the "SET DATESTYLE" SQL command.All of the formats described above can be used for input. A great manyothers can also be used. There is no specific default input format. Ifthe format of a date input is ambiguous then the current DATESTYLE isused to help disambiguate.If you specify a date/time value without a time component, the defaulttime is 00:00:00 (midnight). To specify a date/time value without adate is not allowed. If a date with a two digit year is input then ifthe 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:SELECT ’now’::datetimePostgresql supports a range of date time functions for convertingbetween types, extracting parts of a date time value, truncating to agiven unit, etc. The usual arithmetic can be performed on date andinterval values, e.g., date-date=interval, etc.The following SQL expression can be used to convert an integer "secondssince 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 tolocal time on the database server before being sent to the clientfrontend, 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 thetime zone information to the backend upon connection. The SQL "SET TIMEZONE" command can set the time zone for the current session.LONG/BLOB Data HandlingPostgresql handles BLOBS using a so called "large objects" type. Thehandling of this type differs from all other data types. The data arebroken into chunks, which are stored in tuples in the database. Accessto large objects is given by an interface which is modelled closelyafter the UNIX file system. The maximum size is limited by the filesize of the operating system.If you just select the field, you get a "large object identifier" andnot the data itself. The LongReadLen and LongTruncOk attributes are notimplemented because they don’t make sense in this case. The only methodimplemented by the driver is the undocumented DBI method "blob_read()".Other Data Handling issuesThe "DBD::Pg" driver supports the "type_info()" method.Postgresql supports automatic conversions between data types whereverit’s reasonable.Transactions, Isolation and LockingPostgresql supports transactions. The current default isolationtransaction level is "Serializable" and is currently implemented usingtable level locks. Both may change. No other isolation levels fortransactions are supported.With AutoCommit on, a query never places a lock on a table. Readersnever block writers and writers never block readers. This behaviorchanges whenever a transaction is started (AutoCommit off). Then aquery induces a shared lock on a table and blocks anyone else until thetransaction has been finished.The "LOCK TABLE table_name" statement can be used to apply an explicitlock on a table. This only works inside a transaction (AutoCommit off).To ensure that a table being selected does not change before you makean update later in the transaction, you must explicitly lock it with a"LOCK TABLE" statement before executing the select.No-Table Expression Select SyntaxTo select a constant expression, that is, an expression that doesn’tinvolve 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;Table Join SyntaxOuter joins are not supported. Inner joins use the traditional syntax.Table and Column NamesThe max size of table and column names cannot exceed 31 charaters inlength. Only alphanumeric characters can be used; the first charactermust be a letter.If an identifier is enclosed by double quotation marks ("""), it cancontain any combination of characters except double quotation marks.Postgresql converts all identifiers to lower-case unless enclosed indouble quotation marks. National character set characters can be used,if enclosed in quotation marks.Case Sensitivity of LIKE OperatorPostgresql 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.*’Row IDThe Postgresql "row id" pseudocolumn is called oid, object identifier.It can be treated as a string and used to rapidly (re)select rows.Automatic Key or Sequence GenerationPostgresql does not support automatic key generation such as "autoincrement" or "system generated" keys.However, Postgresql does support "sequence generators". Any number ofnamed sequence generators can be created in a database. Sequences areused via functions called "NEXTVAL" and "CURRVAL". Typical usage: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, orSELECT last_value FROM seq_nameAutomatic Row Numbering and Row Count LimitingPostgresql does not support any way of automatically numbering returnedrows.Parameter BindingParameter binding is emulated by the driver. Both the "?" and ":1"style of placeholders are supported.The TYPE attribute of the "bind_param()" method may be used toinfluence how parameters are treated. These SQL types are bound asVARCHAR: 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 blankpadded comparison semantics.Unsupported values of the TYPE attribute generate a warning.Stored Procedures"DBD::Pg" does not support stored procedures.Table Metadata"DBD::Pg" supports the "table_info()" method.The pg_attribute table contains detailed information about all columnsof all the tables in the database, one row per table.The pg_index table contains detailed information about all indexes inthe database, one row per index.Primary keys are implemented as unique indexes. See pg_index above.Driver-specific Attributes and MethodsThere are no significant "DBD::Pg" driver-specific database handleattributes."DBD::Pg" has the following driver-specific statement handleattributes:pg_size Returns a reference to an array of integer values for eachcolumn. The integer shows the storage (not display) size of thecolumn in bytes. Variable length columns are indicated by −1.pg_type Returns a reference to an array of strings for each column. Thestring shows the name of the data type.pg_oid_statusReturns the OID of the last INSERT command.pg_cmd_statusReturns the name of the last command type. Possible types are:INSERT, DELETE, UPDATE, SELECT."DBD::Pg" has no private methods.Positioned updates and deletesPostgresql does not support positioned updates or deletes.Differences from the DBI Specification"DBD::Pg" has no significant differences in behavior from the currentDBI specification.Note that "DBD::Pg" does not fully parse the statement until it’sexecuted. Thus attributes like $sth->{NUM_OF_FIELDS} are not availableuntil after "$sth−>execute" has been called. This is valid behaviourbut is important to note when porting applications originally writtenfor other drivers.URLs to More Database/Driver Specific Informationhttp://www.postgresql.orgConcurrent use of Multiple Handles"DBD::Pg" supports an unlimited number of concurrent databaseconnections to one or more databases.It also supports the preparation and execution of a new statementhandle while still fetching data from another statement handle,provided it is associated with the same database handle.Other Significant Database or Driver FeaturesPostgres offers substantial additional power by incorporating thefollowing four additional basic concepts in such a way that users caneasily extend the system: classes, inheritance, types, and functions.Other features provide additional power and flexibility: constraints,triggers, rules, transaction integrity, procedural languages, and largeobjects.It’s also free Open Source Software with an active community ofdevelopers.
NAME
DBD: :Pg
− PostgreSQL database driver for the DBI
module
DESCRIPTION
DBD: :Pg
is a Perl module which works with the DBI module
to provide access to PostgreSQL databases.
DBD::Pg
VersionVersion 0.91.Author and Contact DetailsThe driver author is Edmund Mergl. He can be contacted via thedbi-users mailing list.Supported Database Versions and OptionsThe DBD-Pg-0.92 module supports Postgresql 6.5.Connect SyntaxThe "DBI−>connect()" Data Source Name, or DSN, can be one of thefollowing:dbi:Pg:dbname=$dbnamedbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options;tty=$ttyAll parameters, including the userid and password parameter of theconnect command, have a hard-coded default which can be overridden bysetting appropriate environment variables:Parameter Environment Variable Default--------- -------------------- --------------dbname PGDATABASE current useridhost PGHOST localhostport PGPORT 5432options PGOPTIONS ""tty PGTTY ""username PGUSER current useridpassword PGPASSWORD ""There are no driver specific attributes for the "DBI−"connect()>method.Numeric Data HandlingPostgresql supports the following numeric types:Postgresql Range---------- --------------------------int2 -32768 to +32767int4 -2147483648 to +2147483647float4 6 decimal placesfloat8 15 decimal placesSome platforms also support the int8 type. "DBD::Pg" always returnsall numbers as strings.String Data HandlingPostgresql supports the following string data types:CHAR single characterCHAR(size) fixed length blank-paddedVARCHAR(size) variable length with limitTEXT variable lengthAll string data types have a limit of 4096 bytes. The CHAR type isfixed length and blank padded.There is no special handling for data with the 8th bit set. They arestored unchanged in the database. None of the character types canstore embedded nulls and Unicode is not formally supported.Strings can be concatenated using the "⎪⎪" operator.Date Data HandlingPostgresql supports the following date time data types:Type Storage Recommendation Description--------- -------- -------------------------- ----------------------------abstime 4 bytes original date and time limited rangedate 4 bytes SQL92 type wide rangedatetime 8 bytes best general date and time wide range, high precisioninterval 12 bytes SQL92 type equivalent to timespanreltime 4 bytes original time interval limited range, low precisiontime 4 bytes SQL92 type wide rangetimespan 12 bytes best general time interval wide range, high precisiontimestamp 4 bytes SQL92 type limited rangeData Type Range Resolution---------- ---------------------------------- -----------abstime 1901-12-14 2038-01-19 1 sectimestamp 1901-12-14 2038-01-19 1 secreltime -68 years +68 years 1 sectinterval -178000000 years +178000000 years 1 microsectimespan -178000000 years 178000000 years 1 microsecdate 4713 BC 32767 AD 1 daydatetime 4713 BC 1465001 AD 1 microsectime 00:00:00:00 23:59:59:99 1 microsecPostgresql supports a range of date formats:Name Example----------- ----------------------ISO 1997-12-17 0:37:16-08SQL 12/17/1997 07:37:16.00 PSTPostgres Wed Dec 17 07:37:16 1997 PSTEuropean 17/12/1997 15:37:16.00 METNonEuropean 12/17/1997 15:37:16.00 METUS 12/17/1997 07:37:16.00 METThe default output format does not depend on the client/server locale.It depends on, in increasing priority: the PGDATESTYLE environmentvariable at the server, the PGDATESTYLE environment variable at theclient, and the "SET DATESTYLE" SQL command.All of the formats described above can be used for input. A great manyothers can also be used. There is no specific default input format. Ifthe format of a date input is ambiguous then the current DATESTYLE isused to help disambiguate.If you specify a date/time value without a time component, the defaulttime is 00:00:00 (midnight). To specify a date/time value without adate is not allowed. If a date with a two digit year is input then ifthe 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:SELECT ’now’::datetimePostgresql supports a range of date time functions for convertingbetween types, extracting parts of a date time value, truncating to agiven unit, etc. The usual arithmetic can be performed on date andinterval values, e.g., date-date=interval, etc.The following SQL expression can be used to convert an integer "secondssince 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 tolocal time on the database server before being sent to the clientfrontend, 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 thetime zone information to the backend upon connection. The SQL "SET TIMEZONE" command can set the time zone for the current session.LONG/BLOB Data HandlingPostgresql handles BLOBS using a so called "large objects" type. Thehandling of this type differs from all other data types. The data arebroken into chunks, which are stored in tuples in the database. Accessto large objects is given by an interface which is modelled closelyafter the UNIX file system. The maximum size is limited by the filesize of the operating system.If you just select the field, you get a "large object identifier" andnot the data itself. The LongReadLen and LongTruncOk attributes are notimplemented because they don’t make sense in this case. The only methodimplemented by the driver is the undocumented DBI method "blob_read()".Other Data Handling issuesThe "DBD::Pg" driver supports the "type_info()" method.Postgresql supports automatic conversions between data types whereverit’s reasonable.Transactions, Isolation and LockingPostgresql supports transactions. The current default isolationtransaction level is "Serializable" and is currently implemented usingtable level locks. Both may change. No other isolation levels fortransactions are supported.With AutoCommit on, a query never places a lock on a table. Readersnever block writers and writers never block readers. This behaviorchanges whenever a transaction is started (AutoCommit off). Then aquery induces a shared lock on a table and blocks anyone else until thetransaction has been finished.The "LOCK TABLE table_name" statement can be used to apply an explicitlock on a table. This only works inside a transaction (AutoCommit off).To ensure that a table being selected does not change before you makean update later in the transaction, you must explicitly lock it with a"LOCK TABLE" statement before executing the select.No-Table Expression Select SyntaxTo select a constant expression, that is, an expression that doesn’tinvolve 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;Table Join SyntaxOuter joins are not supported. Inner joins use the traditional syntax.Table and Column NamesThe max size of table and column names cannot exceed 31 charaters inlength. Only alphanumeric characters can be used; the first charactermust be a letter.If an identifier is enclosed by double quotation marks ("""), it cancontain any combination of characters except double quotation marks.Postgresql converts all identifiers to lower-case unless enclosed indouble quotation marks. National character set characters can be used,if enclosed in quotation marks.Case Sensitivity of LIKE OperatorPostgresql 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.*’Row IDThe Postgresql "row id" pseudocolumn is called oid, object identifier.It can be treated as a string and used to rapidly (re)select rows.Automatic Key or Sequence GenerationPostgresql does not support automatic key generation such as "autoincrement" or "system generated" keys.However, Postgresql does support "sequence generators". Any number ofnamed sequence generators can be created in a database. Sequences areused via functions called "NEXTVAL" and "CURRVAL". Typical usage: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, orSELECT last_value FROM seq_nameAutomatic Row Numbering and Row Count LimitingPostgresql does not support any way of automatically numbering returnedrows.Parameter BindingParameter binding is emulated by the driver. Both the "?" and ":1"style of placeholders are supported.The TYPE attribute of the "bind_param()" method may be used toinfluence how parameters are treated. These SQL types are bound asVARCHAR: 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 blankpadded comparison semantics.Unsupported values of the TYPE attribute generate a warning.Stored Procedures"DBD::Pg" does not support stored procedures.Table Metadata"DBD::Pg" supports the "table_info()" method.The pg_attribute table contains detailed information about all columnsof all the tables in the database, one row per table.The pg_index table contains detailed information about all indexes inthe database, one row per index.Primary keys are implemented as unique indexes. See pg_index above.Driver-specific Attributes and MethodsThere are no significant "DBD::Pg" driver-specific database handleattributes."DBD::Pg" has the following driver-specific statement handleattributes:pg_size Returns a reference to an array of integer values for eachcolumn. The integer shows the storage (not display) size of thecolumn in bytes. Variable length columns are indicated by −1.pg_type Returns a reference to an array of strings for each column. Thestring shows the name of the data type.pg_oid_statusReturns the OID of the last INSERT command.pg_cmd_statusReturns the name of the last command type. Possible types are:INSERT, DELETE, UPDATE, SELECT."DBD::Pg" has no private methods.Positioned updates and deletesPostgresql does not support positioned updates or deletes.Differences from the DBI Specification"DBD::Pg" has no significant differences in behavior from the currentDBI specification.Note that "DBD::Pg" does not fully parse the statement until it’sexecuted. Thus attributes like $sth->{NUM_OF_FIELDS} are not availableuntil after "$sth−>execute" has been called. This is valid behaviourbut is important to note when porting applications originally writtenfor other drivers.URLs to More Database/Driver Specific Informationhttp://www.postgresql.orgConcurrent use of Multiple Handles"DBD::Pg" supports an unlimited number of concurrent databaseconnections to one or more databases.It also supports the preparation and execution of a new statementhandle while still fetching data from another statement handle,provided it is associated with the same database handle.Other Significant Database or Driver FeaturesPostgres offers substantial additional power by incorporating thefollowing four additional basic concepts in such a way that users caneasily extend the system: classes, inheritance, types, and functions.Other features provide additional power and flexibility: constraints,triggers, rules, transaction integrity, procedural languages, and largeobjects.It’s also free Open Source Software with an active community ofdevelopers.
|
DBD::dbd-pg(3pm) |
|
