Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 90 ยท Data Types

Binary, Geometric, Network and Bit-String Types

Tour PostgreSQL's specialist types: bytea for binary data, point/box/circle geometry, inet/cidr/macaddr network types, bit strings and xml, with operators and index tips.

Most columns hold numbers, text and dates. PostgreSQL also ships a group of specialist types that you meet less often but that save a lot of work when you need them: raw bytes (bytea), simple geometry (point, box, circle), network addresses (inet, cidr, macaddr), bit strings (bit, bit varying) and XML (xml). Each one comes with its own operators, validation and sometimes its own index support, which a plain text column cannot offer.

These types exist only in PostgreSQL, so the server-side commands in this lesson are read-only blocks that you run in psql. The live Python programs reproduce the same logic (address containment, bit masks, distance) with the standard library, so you can see what the operators compute without a server.

bytea: binary data

bytea stores a sequence of bytes of any value, including zero bytes, with no character encoding. Typical uses are hashes, small thumbnails, encrypted tokens and file fingerprints.

SQL
-- PostgreSQL
CREATE TABLE attachments (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  filename  text NOT NULL,
  sha256    bytea NOT NULL,
  content   bytea
);

INSERT INTO attachments (filename, sha256, content)
VALUES ('hello.txt',
        sha256('hello'::bytea),            -- built-in function, PostgreSQL 11+
        '\x68656c6c6f');                   -- hex input format

SELECT filename,
       encode(sha256, 'hex')  AS sha256_hex,
       length(content)        AS bytes,
       convert_from(content, 'UTF8') AS as_text
FROM attachments;

Two input formats exist. The hex format ('\xDEADBEEF') is what PostgreSQL prints by default (bytea_output = 'hex') and what you should write. The older escape format mixes printable characters with octal escapes and is harder to read. Useful functions are encode(bytes, 'hex' | 'base64' | 'escape'), decode(text, format), length, substring, get_byte, set_byte, md5, sha256 and the || concatenation operator.

Large bytea values are stored out of line (TOAST) and compressed when worthwhile, up to 1 GB per value. That does not mean storing big files in the database is wise: every read pulls the whole value into memory, backups grow and replication traffic increases. Many teams keep files in object storage and store only the key and a hash in PostgreSQL. A good rule is to keep bytea for values up to a few megabytes, and consider large objects (lo_* functions) only for special streaming needs.

Geometric types

PostgreSQL has built-in two-dimensional shapes. They are simple, not a full GIS: for real maps use PostGIS (see pgvector and PostGIS).

Type Literal Meaning
point '(2,3)' A position (x, y)
line '{1,-1,0}' An infinite line Ax + By + C = 0
lseg '[(0,0),(4,3)]' A line segment
box '((0,0),(4,3))' A rectangle from two corners
path '[(0,0),(1,1),(2,0)]' An open [] or closed () polyline
polygon '((0,0),(4,0),(2,3))' A closed shape
circle '<(2,2),5>' Centre and radius

Each type has operators for distance, containment and overlap:

SQL
-- PostgreSQL
SELECT point '(0,0)' <-> point '(3,4)';            -- distance: 5
SELECT box '((0,0),(4,4))' @> point '(1,2)';       -- box contains point: true
SELECT circle '<(0,0),6>' && box '((4,4),(9,9))';  -- overlap: true
SELECT area(box '((0,0),(4,3))');                  -- 12
SELECT center(circle '<(2,2),5>');                 -- (2,2)

The <-> operator also drives nearest-neighbour searches. A GiST index on a point column makes ORDER BY location <-> point '(2,3)' LIMIT 5 fast, because the index can walk outwards from the target instead of sorting every row (see GIN and GiST Indexes):

SQL
-- PostgreSQL
CREATE TABLE shops (id int PRIMARY KEY, name text, location point);
CREATE INDEX shops_location_idx ON shops USING gist (location);

SELECT name
FROM shops
ORDER BY location <-> point '(2,3)'
LIMIT 3;

One caution: these types use plain Cartesian geometry. A point of longitude and latitude would give wrong distances, because the Earth is not flat. Use PostGIS or the cube and earthdistance extensions for real-world distances.

This program computes the same results as three of the operators above, so you can check the numbers:

Python Distance, containment and overlap

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Network address types

Storing IP addresses as text allows typos and prevents any useful query. PostgreSQL has types that validate and understand them:

Type Stores Example
inet A host address, optionally with a netmask 192.168.1.10/24, 2001:db8::1
cidr A network (host bits must be zero) 192.168.1.0/24
macaddr A 6-byte MAC address 08:00:2b:01:02:03
macaddr8 An 8-byte MAC address 08:00:2b:ff:fe:01:02:03

Both IPv4 and IPv6 work. inet accepts 10.1.2.3/8 (a host inside a network); cidr rejects that because the bits to the right of the mask are not zero. Invalid values, such as 300.1.1.1, raise an error at insert time.

The operators that make these types worth using are the containment tests:

SQL
-- PostgreSQL
CREATE TABLE allowed_networks (id serial PRIMARY KEY, net cidr NOT NULL);
INSERT INTO allowed_networks (net) VALUES ('10.0.0.0/8'), ('192.168.1.0/24');

-- Is this client address inside any allowed network?
SELECT EXISTS (
  SELECT 1 FROM allowed_networks WHERE inet '192.168.1.77' <<= net
);                                             -- true

SELECT host(inet '192.168.1.77/24'),           -- 192.168.1.77
       masklen(inet '192.168.1.77/24'),        -- 24
       network(inet '192.168.1.77/24'),        -- 192.168.1.0/24
       broadcast(inet '192.168.1.77/24'),      -- 192.168.1.255/24
       inet '192.168.1.5' + 10;                -- 192.168.1.15

<< means "is contained within", <<= adds "or equals", and >> and >>= reverse the direction. A GiST index with the inet_ops operator class (or SP-GiST) speeds up containment queries on large tables; a plain B-tree index on inet or cidr supports equality and sorting. The types also sort numerically, which text cannot do: '10.0.0.9' comes before '10.0.0.10' as an inet, but after it as text.

Python Which network does an address belong to?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Bit strings

bit(n) stores exactly n bits and bit varying(n) (varbit) stores up to n. They are useful for compact flag sets, bloom-filter-style signatures and fingerprints compared by Hamming distance.

SQL
-- PostgreSQL
SELECT B'10110'::bit(5);                         -- 10110
SELECT B'1100' & B'1010',                        -- 1000  (AND)
       B'1100' | B'1010',                        -- 1110  (OR)
       B'1100' # B'1010',                        -- 0110  (XOR)
       ~ B'1100',                                -- 0011  (NOT)
       B'0001' << 2;                             -- 0100  (shift)

SELECT get_bit(B'10110', 1),                     -- 0  (bits count from the left, starting at 0)
       set_bit(B'10110', 0, 0),                  -- 00110
       bit_count(B'10110'),                      -- 3 (PostgreSQL 14+)
       length(B'10110'),                         -- 5
       get_byte('\x80'::bytea, 0)::bit(8);       -- 10000000

Casting an integer to bit gives its binary form: 5::bit(8) is 00000101. The operators &, | and # need operands of the same length. A bit string column holding permissions (read = bit 0, write = bit 1 and so on) is compact, but a set of boolean columns or a small enum array is usually easier to read and constrain, so choose bit strings when you really need bitwise operations over many flags.

Python Bit masks and Hamming distance

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

xml and a word on JSON

The xml type checks that the value is well-formed XML and comes with functions such as xmlelement, xmlagg, xpath and xmltable:

SQL
-- PostgreSQL
SELECT xpath('/order/item/@sku', '<order><item sku="A1"/><item sku="B2"/></order>'::xml);
-- {A1,B2}

SELECT xmlelement(name greeting, 'Hello & welcome');
-- <greeting>Hello &amp; welcome</greeting>

The server must be built with libxml support (the packages from the PostgreSQL project are). New projects almost always choose jsonb instead (see JSONB Basics); use xml when you must keep and query documents exactly as an external system sends them.

When a specialist type fits, use it instead of text. You get validation for free, correct sorting, purpose-built operators and often a better index. Casting back to text is always possible with ::text or host().

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which type should hold the value 192.168.1.0/24, a network whose host bits must be zero?

    cidr stores network specifications and rejects values with non-zero host bits. inet accepts both hosts and networks.
  2. 2. What does inet '10.1.2.3' <<= cidr '10.0.0.0/8' return?

    <<= means is contained within or equal to. 10.1.2.3 is inside 10.0.0.0/8.
  3. 3. Why can a GiST index on a point column speed up ORDER BY location <-> point '(2,3)' LIMIT 3?

    GiST supports ordering operators such as <->, so the index can produce rows nearest-first.
  4. 4. A teammate stores user avatars of 40 MB each in a bytea column. What is the main concern?

    A bytea can be up to 1 GB, but large values bloat memory use, backups and replication traffic. Store big files elsewhere and keep a key and hash.

Summary

  • bytea holds raw bytes: write values in hex ('\x68656c6c6f'), convert with encode/decode, and keep it to small payloads.
  • point, box, circle, polygon and friends give plain 2-D geometry with distance, containment and overlap operators; a GiST index speeds up nearest-neighbour queries. Use PostGIS for real maps.
  • inet, cidr, macaddr validate addresses and give containment operators (<<, <<=, >>, >>=) and correct numeric sorting.
  • bit(n) and bit varying support bitwise AND, OR, XOR, NOT and shifts, plus bit_count for Hamming distance.
  • xml validates documents and offers XPath, but jsonb is usually the better choice for new designs.

Next: Generated and Virtual Columns.