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.

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:

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.

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().

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).

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.

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:

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:

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

Parameter 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 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.

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.

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.