# Type – Type objects and constructors

## Type constructors

For binding to an operation’s input parameters, PostgreSQL needs to have the input in a particular format. However, from the parameters to the `Cursor.execute()` and `Cursor.executemany()` methods it is not always obvious as which PostgreSQL data types they shall be bound. For instance, a Python string could be bound as a simple `char` value, or also as a `date` or a `time`. Or a list could be bound as a `array` or a `json` object. To make the intention clear in such cases, you can wrap the parameters in type helper objects. PyGreSQL provides the constructors defined below to create such objects that can hold special values. When passed to the cursor methods, PyGreSQL can then detect the proper type of the input parameter and bind it accordingly.

The `pgdb` module exports the following type constructors as part of the DB-API 2 standard:

pgdb.Date(year, month, day)

  Construct an object holding a date value

pgdb.Time(hour[, minute][, second][, microsecond][, tzinfo])

  Construct an object holding a time value

pgdb.Timestamp(year, month, day[, hour][, minute][, second][, microsecond][, tzinfo])

  Construct an object holding a time stamp value

pgdb.DateFromTicks(ticks)

  Construct an object holding a date value from the given ticks value

pgdb.TimeFromTicks(ticks)

  Construct an object holding a time value from the given ticks value

pgdb.TimestampFromTicks(ticks)

  Construct an object holding a time stamp from the given ticks value

pgdb.Binary(bytes)

  Construct an object capable of holding a (long) binary string value

Additionally, PyGreSQL provides the following constructors for PostgreSQL specific data types:

pgdb.Interval(days, hours=0, minutes=0, seconds=0, microseconds=0)

  Construct an object holding a time interval value

Added in version 5.0.

pgdb.Uuid([hex][, bytes][, bytes_le][, fields][, int][, version])

  Construct an object holding a UUID value

Added in version 5.0.

pgdb.Hstore(dict)

  Construct a wrapper for holding an hstore dictionary

Added in version 5.0.

pgdb.Json(obj[, encode])

  Construct a wrapper for holding an object serializable to JSON

  You can pass an optional serialization function as a parameter. By default, PyGreSQL uses `json.dumps()` to serialize it.

pgdb.Literal(sql)

  Construct a wrapper for holding a literal SQL string

Added in version 5.0.

Example for using a type constructor:

    >>> cursor.execute("create table jsondata (data jsonb)")
    >>> data = {'id': 1, 'name': 'John Doe', 'kids': ['Johnnie', 'Janie']}
    >>> cursor.execute("insert into jsondata values (%s)", [Json(data)])

Note

SQL `NULL` values are always represented by the Python None singleton on input and output.

## Type objects

class pgdb.DbType

The `Cursor.description` attribute returns information about each of the result columns of a query. The type_code must compare equal to one of the `DbType` objects defined below. Type objects can be equal to more than one type code (e.g. `DATETIME` is equal to the type codes for `date`, `time` and `timestamp` columns).

The pgdb module exports the following `DbType` objects as part of the DB-API 2 standard:

STRING

  Used to describe columns that are string-based (e.g. `char`, `varchar`, `text`)

BINARY

  Used to describe (long) binary columns (`bytea`)

NUMBER

  Used to describe numeric columns (e.g. `int`, `float`, `numeric`, `money`)

DATETIME

  Used to describe date/time columns (e.g. `date`, `time`, `timestamp`, `interval`)

ROWID

  Used to describe the `oid` column of PostgreSQL database tables

Note

The following more specific type objects are not part of the DB-API 2 standard.

BOOL

  Used to describe `boolean` columns

SMALLINT

  Used to describe `smallint` columns

INTEGER

  Used to describe `integer` columns

LONG

  Used to describe `bigint` columns

FLOAT

  Used to describe `float` columns

NUMERIC

  Used to describe `numeric` columns

MONEY

  Used to describe `money` columns

DATE

  Used to describe `date` columns

TIME

  Used to describe `time` columns

TIMESTAMP

  Used to describe `timestamp` columns

INTERVAL

  Used to describe date and time `interval` columns

UUID

  Used to describe `uuid` columns

HSTORE

  Used to describe `hstore` columns

Added in version 5.0.

JSON

  Used to describe `json` and `jsonb` columns

Added in version 5.0.

ARRAY

  Used to describe columns containing PostgreSQL arrays

Added in version 5.0.

RECORD

  Used to describe columns containing PostgreSQL records

Added in version 5.0.

Example for using some type objects:

    >>> cursor = con.cursor()
    >>> cursor.execute("create table jsondata (created date, data jsonb)")
    >>> cursor.execute("select * from jsondata")
    >>> (created, data) = (d.type_code for d in cursor.description)
    >>> created == DATE
    True
    >>> created == DATETIME
    True
    >>> created == TIME
    False
    >>> data == JSON
    True
    >>> data == STRING
    False