Supported Types of Preference SQL
The well-known data types of SQL are supported with respect to the type of the preference constructor as listed on this page.
Categorical Preference Constructors
All categorical preference constructors handle these data types:
Character strings
* CHARACTER(n) or CHAR(n) — fixed-width n-character string, padded with spaces as needed
* CHARACTER VARYING(n) or VARCHAR(n) — variable-width string with a maximum size of n characters
Numerical Preference Constructors
All numerical preference constructors handle these data types:
Numbers
- INTEGER and SMALLINT
- FLOAT, REAL and DOUBLE PRECISION
- NUMERIC(precision, scale) or DECIMAL(precision, scale)
- DATE — for date values
| DATE 'YYYY-MM-DD' |
| DATE '2011-05-03' |
- TIME without timezone - for time values
| TIME 'HH:MM[:SS[.MS]]' |
| TIME '9:30' |
| TIME '9:30:50' |
| TIME '9:30:50.942' |
TIMESTAMP without timezone - for values of date and time
| TIMESTAMP 'YYYY-MM-DD HH:MM[:SS[.MS]]' |
| TIMESTAMP '2011-05-03 9:30' |
INTERVAL - for intervals of date and time
|
INTERVAL '[[+/-]YY-MM] [[+/-]DD] [[+/-]HH:MM[:SS[.MS]]] INTERVAL 'XX' QUALIFIER QUALIFIER := YEAR | MONTH | DAY | HOUR | MINUTE | SECOND |
For the zero time interval:
| INTERVAL ' ' |
For 1 month, 1 day:
| INTERVAL '0-1 1' |
For -1 months, 1 day, -30 miinutes:
| INTERVAL '-0-1 1 -0:30' |
For 1 day, 1 millisecond:
| INTERVAL '1 0:0:0.001' |
For 1 month:
| INTERVAL '1' MONTH |
For 10 seconds:
| INTERVAL '10' SECOND |
There are also Type TIMESTAMP WITH TIMEZONE and TIME WITH TIMEZONE in PostgreSQL (Constructors TIMETZ and TIMESTAMPZ). They are not supported in PreferenceSQL yet.
In PostgreSQL the granularity is 1 microsecond. Refer to the PostgreSQL Documentation for details.