Supported Types of Preference SQL

The well-known data typesof 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, Time and Time Intervals

• 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.