Skip to content
Wiki
CS
Data
Postgres
Types

Types

type ‘string’

  • Type specification

‘string’::type CAST ( ‘string’ AS type )

  • Above can be used for specifying or casting

  • Type coercion typename ( ‘string’ )

Enum

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood
------+--------------
 Moe  | happy
(1 row)

Geometric

  • point
    • (x,y)(x, y)
  • line
    • Ax+By+C=0Ax+By+C=0
  • lseg
    • (x1,y1)(x_1,y_1) to (x2,y2)(x_2,y_2)
  • box
    • Opposite corners
    • (x1,y1)(x_1,y_1), (x2,y2)(x_2,y_2)
  • path
    • Open or Closed
    • (x1,y1),(x2,y2),...,(xn,yn)(x_1,y_1),(x_2,y_2),...,(x_n,y_n)
  • polygon
    • Similar to closed path, includes area within
    • (x1,y1),(x2,y2),...,(xn,yn)(x_1,y_1),(x_2,y_2),...,(x_n,y_n)
  • circle
    • (x1,y1,r)(x_1,y_1,r)

Bit String

  • Bit mask

Text Search

tsvector

un-normalised

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
                      tsvector
----------------------------------------------------
 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
  • With quotes:
SELECT $$the lexeme '    ' contains spaces$$::tsvector;
                 tsvector
-------------------------------------------
 '    ' 'contains' 'lexeme' 'spaces' 'the'
  • Quotes need doubling:
SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector;
                    tsvector
------------------------------------------------
 'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the'
  • Weighting (titles vs bodies):
    • A through D
SELECT 'a:1A fat:2B,4C cat:5D'::tsvector;
          tsvector
----------------------------
 'a':1A 'cat':5 'fat':2B,4C

to_tsvector

normalising

SELECT to_tsvector('english', 'The Fat Rats');
   to_tsvector
-----------------
 'fat':2 'rat':3

tsquery 

SELECT 'fat & rat'::tsquery;
    tsquery
---------------
 'fat' & 'rat'

SELECT 'fat & (rat | cat)'::tsquery;
          tsquery
---------------------------
 'fat' & ( 'rat' | 'cat' )

SELECT 'fat & rat & ! cat'::tsquery;
        tsquery
------------------------
 'fat' & 'rat' & !'cat'
  • &, |, !
  • <-> (FOLLOWED BY)
    • <N>

to_tsquery 

SELECT to_tsquery('Fat:ab & Cats');
    to_tsquery
------------------
 'fat':AB & 'cat'

Weighting 

SELECT 'fat:ab & cat'::tsquery;
    tsquery
------------------
 'fat':AB & 'cat'

Prefix Matching 

SELECT 'super:*'::tsquery;
  tsquery
-----------
 'super':*

XML

  • Syntax checking
  • Some type-safe operations
XMLPARSE ( { DOCUMENT | CONTENT } `value`)

xml '<foo>bar</foo>'

'<foo>bar</foo>'::xml
  • xml -> obj
XMLSERIALIZE ( { DOCUMENT | CONTENT } `value` AS `type` [ [ NO ] INDENT ] )

JSON

Arrays

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);
  • Variable length
CREATE TABLE tictactoe (
    squares   integer[3][3]
);
  • Fixed lengths

Literals 

'{ `val1` `delim` `val2` `delim` ... }'
'{{1,2,3},{4,5,6},{7,8,9}}'

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

Composite

  • Can be used as columns
CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);
CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
Last updated on
TransactionsWindow Functions