Search Results for

    Show / Hide Table of Contents

    Overview

    The following are notes by Emil Lenngren on PostgreSQL wire representation of types:

    bool:
        text: t or f
        binary: a byte: 1 or 0
    
    bytea:
        text:
            either \x followed by hex-characters (lowercase by default),
            or plain characters, where non-printable characters (between 0x20 and 0x7e, inclusive) are written as \nnn (octal) and \ is written as \\
        binary: the bytes as they are
    
    char:
        This type holds a single char/byte. (Not to be confused with bpchar (blank-padded char) which is PostgreSQL's alias to the SQL standard's char).
        The char may be the null-character
        text: the char as a byte, encoding seems to be ignored
        binary: the char as a byte
    
    name:
        A null-padded string of NAMEDATALEN (currently 64) bytes (the last byte must be a null-character). Used in pg catalog.
        text: the name as a string
        binary: the name as a string
    
    int2/int4/int8:
        text: text representation in base 10
        binary: binary version of the integer
    
    int2vector:
        non-null elements, 0-indexed, 1-dim
        text: 1 2 3 4
        binary: same as int2[]
    
    oidvector:
        non-null elements, 0-indexed, 1-dim
        text: 1 2 3 4
        binary: same as oid[]
    
    regproc:
        internally just an OID (UInt32)
        text: -, name of procedure, or numeric if not found
        binary: only the OID in binary
    
    regprocedure/regoper/regoperator/regclass/regconfig/regdictionary:
        similar to regproc
    
    text:
        text: the string as it is
        binary: the string as it is
    
    oid:
        A 32-bit unsigned integer used for internal object identification.
        text: the text-representation of this integer in base 10
        binary: the UInt32
    
    tid:
        tuple id
        Internally a tuple of a BlockNumber (UInt32) and an OffsetNumber (UInt16)
        text: (blockNumber,offsetNumber)
        binary: the block number in binary followed by offset number in binary
    
    xid:
        transaction id
        Internally just a TransactionId (UInt32)
        text: the number
        binary: the number in binary
    
    cid:
        command id
        Internally just a CommandId (UInt32)
        text: the number
        binary: the number in binary
    
    json:
        json
        text: the json an text
        binary: the json as text
    
    jsonb:
        json internally stored in an efficient binary format
        text: the json as text
        binary: An Int32 (version number, currently 1), followed by data (currently just json as text)
    
    xml:
        Xml. It is probably most efficient to use the text format, especially when receiving from client.
        text: the xml as text (when sent from the server: encoding removed, when receiving: assuming database encoding)
        binary: the xml as text (when sent from the server: in the client's specified encoding, when receiving: figures out itself)
    
    pg_node_tree:
        used as type for the column typdefaultbin in pg_type
        does not accept input
        text: text
        binary: text
    
    smgr:
        storage manager
        can only have the value "magnetic disk"
        text: magnetic disk
        binary: not available
    
    point:
        A tuple of two float8
        text:
            (x,y)
            The floats are interpreted with the C strtod function.
            The floats are written with the snprintf function, with %.*g format. NaN/-Inf/+Inf can be written, but not interpretability depends on platform. The extra_float_digits setting is honored.
            For linux, NaN, [+-]Infinity, [+-]Inf works, but not on Windows. Windows also have other output syntax for these special numbers. (1.#QNAN for example)
        binary: the two floats
    
    lseg:
        A tuple of two points
        text: [(x1,y1),(x2,y2)] see point for details
        binary: the four floats in the order x1, y1, x2, y2
    
    path:
        A boolean whether the path is opened or closed + a vector of points.
        text: [(x1,y1),...] for open path and ((x1,y1),...) for closed paths. See point for details.
        binary: first a byte indicating open (0) or close (1), then the number of points (Int32), then a vector of points
    
    box:
        A tuple of two points. The coordinates will be reordered so that the first is the upper right and the second is the lower left.
        text: (x1,y1),(x2,y2) see point for details
        binary: the four floats in the order x1, y1, x2, y2 (doesn't really matter since they will be reordered)
    
    polygon:
        Same as path but with two differences: is always closed and internally stores the bounding box.
        text: same as closed path
        binary: the number of points (Int32), then a vector of points
    
    line (version 9.4):
        Ax + By + C = 0. Stored with three float8.
        Constraint: A and B must not both be zero (only checked on text input, not binary).
        text: {A,B,C} see point for details about the string representation of floats. Can also use the same input format as a path with two different points, representing the line between those.
        binary: the three floats
    
    circle:
        <(x,y),r> (center point and radius), stored with three float8.
        text: <(x,y),r> see point for details about the string representation of floats.
        binary: the three floats x, y, r in that order
    
    float4/float8:
        text:
            (leading/trailing whitespace is skipped) interpreted with the C strtod function, but since it has problems with NaN, [+-]Infinity, [+-]Inf, those strings are identified (case-insensitively) separately.
            when outputting: NaN, [+-]Infinity is treated separately, otherwise the string is printed with snprintf %.*g and the extra_float_digits setting is honored.
        binary: the float
    
    abstime:
        A unix timestamp stored as a 32-bit signed integer with seconds-precision (seconds since 1970-01-01 00:00:00), in UTC
        Has three special values: Invalid (2^31-1), infinity (2^31-3), -infinity (-2^31)
        text: same format as timestamptz, or "invalid", "infinity", "-infinity"
        binary: Int32
    
    reltime:
        A time interval with seconds-precision (stored as an 32-bit signed integer)
        text: same as interval
        binary: Int32
    
    tinterval:
        Consists of a status (Int32) and two abstimes. Status is valid (1) iff both abstimes are valid, else 0.
        Note that the docs incorrectly states that ' is used as quote instead of "
        text: ["<abstime>" "<abstime>"]
        binary: Int32 (status), Int32 (abstime 1), Int32 (abstime 2)
    
    unknown:
        text: text
        binary: text
    
    money:
        A 64-bit signed integer. For example, $123.45 is stored as the integer 12345. Number of fraction digits is locale-dependent.
        text: a locale-depedent string
        binary: the raw 64-bit integer
    
    macaddr:
        6 bytes
        text: the 6 bytes in hex (always two characters per byte) separated by :
        binary: the 6 bytes appearing in the same order as when written in text
    
    inet/cidr:
        Struct of Family (byte: ipv4=2, ipv6=3), Netmask (byte with number of bits in the netmask), Ipaddr bytes (16)
        Text: The IP-address in text format and /netmask. /netmask is omitted in inet if the netmask is the whole address.
        Binary: family byte, netmask byte, byte (cidr=1, inet=0), number of bytes in address, bytes of the address
    
    aclitem:
        Access list item used in pg_class
        Text: Something like postgres=arwdDxt/postgres
        Binary: not available
    
    bpchar:
        Blank-padded char. The type modifier is used to blank-pad the input.
        text: text
        binary: text
    
    varchar:
        Variable-length char. The type modifier is used to check the input's length.
        text: text
        binary: text
    
    date:
        A signed 32-bit integer of a date. 0 = 2000-01-01.
        Infinity: INT_MAX, -Infinity: INT_MIN
        Text: Date only using the specified date style
        Binary: Int32
    
    time:
        A signed 64-bit integer representing microseconds from 00:00:00.000000. (Legacy uses 64-bit float). Negative values are not allowed.
        Max value is 24:00:00.000000.
        text: hh:mm:ss or hh:mm:ss.ffffff where the fraction part is between 1 and 6 digits (trailing zeros are not written)
        binary: the 64-bit integer
    
    timetz:
        A struct of
        Time: A signed 64-bit integer representing microseconds from 00:00:00.000000. (Legacy uses 64-bit float). Negative values are not allowed.
            Max value is 24:00:00.000000.
        Zone: A signed 32-bit integer representing the zone (in seconds). Note that the sign is inverted. So GMT+1h is stored as -1h.
        text: hh:mm:ss or hh:mm:ss.ffffff where the fraction part is between 1 and 6 digits (trailing zeros are not written)
        binary: the 64-bit integer followed by the 32-bit integer
    
    timestamp:
        A signed 64-bit integer representing microseconds from 2000-01-01 00:00:00.000000
        Infinity is LONG_MAX and -Infinity is LONG_MIN
        (Infinity would be 294277-01-09 04:00:54.775807)
        Earliest possible timestamp is 4714-11-24 00:00:00 BC. Even earlier would be possible, but due to internal calculations those are forbidden.
        text: dependent on date style
        binary: the 64-bit integer
    
    timestamptz:
        A signed 64-bit integer representing microseconds from 2000-01-01 00:00:00.000000 UTC. (Time zone is not stored).
        Infinity is LONG_MAX and -Infinity is LONG_MIN
        text: first converted to the time zone in the db settings, then printed according to the date style
        binary: the 64-bit integer
    
    interval:
        A struct of
        Time (Int64): all time units other than days, months and years (microseconds)
        Day (Int32): days, after time for alignment
        Month (Int32): months and years, after time for alignment
        text: Style dependent, but for example: "-11 mons +15435 days -11111111:53:00"
        binary: all fields in the struct
    
    bit/varbit:
        First a signed 32-bit integer containing the number of bits (negative length not allowed). Then all the bits in big end first.
        So a varbit of length 1 has the first (and only) byte set to either 0x80 or 0x00. Last byte is assumed (and is automatically zero-padded in recv) to be zero-padded.
        text:
            when sending from backend: all the bits, written with 1s and 0s.
            when receiving from client: (optionally b or B followed by) all the bits as 1s and 0s, or a x or X followed by hexadecimal digits (upper- or lowercase), big endian first.
        binary: the 32-bit length followed by the bytes containing the bits
    
    numeric:
        A variable-length numeric value, can be negative.
        text: NaN or first - if it is negative, then the digits with . as decimal separator
        binary:
            first a header of 4 16-bit integers:
                number of digits in the digits array that follows (unsigned integer),
                weight of the first digit (10000^weight), can be both negative, positive or 0,
                sign: negative=0x4000, positive=0x0000, NaN=0xC000
                dscale: number of digits (in base 10) to print after the decimal separator
            then the array of digits:
                The digits are stored in base 10000, where each digit is a 16-bit integer.
                Trailing zeros are not stored in this array, to save space.
                The digits are stored such that, if written as base 10000, the decimal separator can be inserted between two digits in base 10000,
                    i.e. when this is to be printed in base 10, only the first digit in base 10000 can (possibly) be printed with less than 4 characters.
                    Note that this does not apply for the digits after the decimal separator; the digits should be printed out in chunks of 4
                    characters and then truncated with the given dscale.
    
    refcursor:
        uses the same routines as text
    
    record:
        Describes a tuple. Is also the "base class" for composite types (i.e. it uses the same i/o functions).
        text:
            ( followed by a list of comma-separated text-encoded values followed by ).
            Empty element means null.
            Quoted with " and " if necessary. " is escaped with "" and \ is escaped with \\ (this differs from arrays where " is escaped with \").
            Must be quoted if it is an empty string or contains one of "\,() or a space.
        binary:
            First a 32-bit integer with the number of columns, then for each column:
                An OID indicating the type of the column
                The length of the column (32-bit integer), or -1 if null
                The column data encoded as binary
    
    cstring:
        text/binary: all characters are sent without the trailing null-character
    
    void:
        Used for example as return value in SELECT * FROM func_returning_void()
        text: an empty string
        binary: zero bytes
    
    uuid:
        A 16-byte uuid.
        text: group of 8, 4, 4, 4, 12 hexadecimal lower-case characters, separated by -. The first byte is written first. It is allowed to surround it with {}.
        binary: the 16 bytes
    
    txid_snapshot:
        (txid is a UInt64)
        A struct of
        UInt32 nxip (size of the xip array)
        txid xmin (no values in xip is smaller than this)
        txid xmax (no values in xip is larger than or equal this)
        txid[] xip (is ordered in ascending order)
        text: xmin:xmax:1,2,3,4
        binary: all fields in the structure
    
    tsvector:
        Used for text searching. Example of tsvector: 'a':1,6,10 'on':5 'and':8 'ate':9A 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4
        Max length for each lexeme string is 2046 bytes (excluding the trailing null-char)
        The words are sorted when parsed, and only written once. Positions are also sorted and only written once.
        For some reason, the unique check does not seem to be made for binary input, only text input...
        text: As seen above. ' is escaped with '' and \ is escaped with \\.
        binary:
            UInt32 number of lexemes
            for each lexeme:
                lexeme text in client encoding, null-terminated
                UInt16 number of positions
                for each position:
                    UInt16 WordEntryPos, where the most significant 2 bits is weight, and the 14 least significant bits is pos (can't be 0). Weights 3,2,1,0 represent A,B,C,D
    
    tsquery:
        A tree with operands and operators (&, |, !). Operands are strings, with optional weight (bitmask of ABCD) and prefix search (yes/no, written with *).
        text: the tree written in infix notation. Example: ( 'abc':*B | 'def' ) & !'ghi'
        binary: the tree written in prefix notation:
            First the number of tokens (a token is an operand or an operator).
            For each token:
                UInt8 type (1 = val, 2 = oper) followed by
                For val: UInt8 weight + UInt8 prefix (1 = yes / 0 = no) + null-terminated string,
                For oper: UInt8 oper (1 = not, 2 = and, 3 = or, 4 = phrase).
                In case of phrase oper code, an additional UInt16 field is sent (distance value of operator). Default is 1 for <->, otherwise the n value in '<n>'.
    
    enum:
        Simple text
    
    gtsvector:
        GiST for tsvector. Probably internal type.
    
    int4range/numrange/tsrange/tstzrange/daterange/int8range and user-defined range types:
        /* A range's flags byte contains these bits: */
        #define RANGE_EMPTY         0x01    /* range is empty */
        #define RANGE_LB_INC        0x02    /* lower bound is inclusive */
        #define RANGE_UB_INC        0x04    /* upper bound is inclusive */
        #define RANGE_LB_INF        0x08    /* lower bound is -infinity */
        #define RANGE_UB_INF        0x10    /* upper bound is +infinity */
        #define RANGE_LB_NULL       0x20    /* lower bound is null (NOT USED) */
        #define RANGE_UB_NULL       0x40    /* upper bound is null (NOT USED) */
        #define RANGE_CONTAIN_EMPTY 0x80/* marks a GiST internal-page entry whose
                                         * subtree contains some empty ranges */
        A range has no lower bound if any of RANGE_EMPTY, RANGE_LB_INF (or RANGE_LB_NULL, not used anymore) is set. The same applies for upper bounds.
        text:
            A range with RANGE_EMPTY is just written as the string "empty".
            Inclusive bounds are written with [ and ], else ( and ) is used.
            The two values are comma-separated.
            Missing bounds are written as an empty string (without quotes).
            Each value is quoted with " if necessary. Quotes are necessary if the string is either the empty string or contains "\,()[] or spaces. " is escaped with "" and \ is escaped with \\.
            Example: [18,21]
        binary: First the flag byte. Then, if has lower bound: 32-bit length + binary-encoded data. Then, if has upper bound: 32-bit length + binary-encoded data.
    
    hstore:
        Key/value-store. Both keys and values are strings.
        text:
            Comma-space separated string, where each item is written as "key"=>"value" or "key"=>NULL. " and \ are escaped as \" and \\.
            Example: "a"=>"b", "c"=>NULL, "d"=>"q"
        binary:
            Int32 count
            for each item:
                Int32 keylen
                string of the key (not null-terminated)
                Int32 length of item (or -1 if null)
                the item as a string
    
    ghstore:
        internal type for indexing hstore
    
    domain types:
        mapped types used in information_schema:
            cardinal_number: int4 (must be nonnegative or null)
            character_data: varchar
            sql_identifier: varchar
            time_stamp: timestamptz
            yes_or_no: varchar(3) (must be "YES" or "NO" or null)
        intnotnull: when an int4 is cast to this type, it is checked that the int4 is not null, but it still returns an int4 and not intnotnull...
    
    • Edit this page
    In this article
    Back to top © Copyright 2023 The Npgsql Development Team