Postgres

Data types

Date / time types

timestamp84713 BC to 294276 AD1 microsecondtimestamp with time zone84713 BC to 294276 AD1 microseconddate44713 BC to 5874897 AD1 daytime800:00:00 to 24:00:00 (time of day)1 microsecondtime with time zone1200:00:00+1559 to 24:00:00-15591 microsecondinterval16-178000000 years to 178000000 years1 microsecond

Name	Storage size (bytes)	Range	Resolution

time, timestamp and interval accept an optional precision value p.

Date and time input is accepted in a ton of formats. Generally, ISO 8601 (1998-04-07) is recommended.

Valid input for a time-of-day type is a time of day, followed by an optional time zone. 04:05, 04:05:06, 04:05:06.789, or 04:05:06.789-8 (ISO 8601 with time zone as UTC offset) formats are great for the time component, and abbreviations (like PST for Pacific Standard Time) or full time zone names (America/New_York) are great for time zones.

Valid input for the timestamp types are

1999-01-08 04:05:06

and

1999-01-08 04:05:06 -8:00,

which both follow ISO 8601.

Postgres will not infer that you want a TIMESTAMP WITH TIME ZONE type from TIMESTAMP 1999-01-08 04:05:06 -8:00, it will automatically assume TIMESTAMP WITHOUT TIME ZONE and ignore the UTC offset.

Special values

epochdate, timestamp1970-01-01 00:00:00+00 (Unix system time zero)infinitydate, timestampLater than all other timestamps-infinitydate, timestampEarlier than all other timestampsnowdate, time, timestampCurrent transaction's start timetodaydate, timestampMidnight (00:00:00) todaytomorrowdate, timestampMidnight (00:00:00) tomorrowyesterdaydate, timestampMidnight (00:00:00) yesterday

Input string	Valid types	Description

Time output format can be specified, but the default is ISO 8601. Time zone format for ISO 6801 is always in the format +/-hh, the signed numeric offset from UTC.

Because of weirdness due to daylights savings, it's best practice to use a type that has both time and date when using timezones, and avoiding the time with time zone type.

When specifying time zone data, the difference between using an abbreviation or the full IANA time zone name is that the full name implies subjectivity to daylight savings rules, whereas an abbreviation is basically the same as a UTC offset.

Time zone data isn't hard wired into the server, it's derived from timezone configuration files stored on the system.

interval values follow this syntax:

[@] quantity unit [quantity unit...] [direction]

where quantity is a number and unit is one of

microsecondmillisecondsecondminutehourdayweekmonthyeardecadecenturymillenium

Full text search

Full text search is a feature allowing users to query for natual language documents, the most common type being documents containing query terms sorted by similarity to the query.

Full text indexing allows documents to be preprocessed and an index saved to improve the performance of subsequent searches. Preprocessing includes:

Parsing documents into tokens such as numbers, words, email addresses, ect. for application-specific processing.
Converting tokens into lexemes, normalizing them so different forms of the same word are made alike, such as converting upper-case to lower-case and removing suffixes, and eliminating stop-words (words so common they are useless for searching). Postgres uses dictionaries for this step.
Storing preprocessed documents in a way optimized for search, for example as a sorted array of normalized lexemes. Sometimes it is useful to store positional information to use proximity ranking so that a document with a more dense region of query words is ranked higher.

A data type called tsvector is provided for storing preprocessed documents along with a type tsquery for representing processed queries.

A document is the unit of search in a full text search system (for example, a magazine article or email). It is usually a textual field in a row of a table or a concatenation of textual fields, and might not be stored anywhere as a whole (when concatenating fields, use coalesce instead of ||).