Data Type Precision
About
In Tabulify, precision refers to:
- a numeric or date time precision
- but also to the character length
This article explains why in details.
Database Precision and Length Specifier
In the database world, when specifying a data type, you may give
- the p specifier (precision)
- or n specifier (length)
For instance:
numeric(p)
varchar(n)
timestamp(p)
| Name | Symbol | Description |
|---|---|---|
| numeric precision | p | The number of digits |
| character length | n | The number of character |
| datetime precision | p | The number of fractional digits retained in the seconds field |
Tabulify definition
In tabulify, we use the term precision to refer to all of them
- a number has a precision
- a character has also a precision (ie a length)
- a datetime has a precision
Implicit Precision
Note that the precision may be implicit because it's embedded in the name of the type. For instance:
- tinyint is a signed one-byte integer (-127/128)
- smallint is a signed two-byte integer (-32,768/32,767)
In this case, the precision reported is the maximum length of the value.
- 3 for a tinyint (ie there is 3 digits in 128)
- 5 for a smallint (ie there is 5 digits in 32,767)
- 10 for a date (ie there is 10 characters in YYYY-MM-DD)
Summary
Below is a summary table of what the precision represents by data type:
| Type | Definition | Description |
|---|---|---|
| Numeric | numeric precision or maximum length | For type without a precision specifier, the precision reports the number of digits For instance, for an integer, it will be 10 (ie 4,294,967,295) |
| Character | maximum length | The number of characters |
| Time | timestamp/datetime precision or maximum length | For type without a precision specifier, the precision reports the character length For instance, for a date, it will be 10 (ie YYYY-MM-DD) |
List
You can list the precision by data type with the connection type command.
For instance, for the sqlite howto connection
tabul connection type sqlite
Known Types for the connection (sqlite)
name aliases ansi_type max_precision supported description
-------------------------- ------------------------------------- -------------------------- ------------- --------- ------------------------------------------------
bigint int8 bigint 19 ✓ Eight-byte integer
blob blob Large binary objects
boolean bool boolean ✓ Logical Boolean (true/false)
character char character 2147483647 ✓ Fixed-length blank padded character string
character varying varchar, varying character character varying 2147483647 ✓ Variable-length character string
date date ✓ Calendar date (year, month, day)
decimal dec decimal 100 ✓ Exact numeric of selectable precision
double precision double, float8 double precision ✓ Double precision floating-point number (8 bytes)
float float ✓ Flexible Precision floating-point number
integer int, int4 integer 10 ✓ Four-byte integer
json json ✓ Textual JSON data
mediumint int3 mediumint 9 ✓ Three-byte integer
national character nchar national character 2147483647 ✓ Fixed-length text in Unicode character set
national character varying nvarchar national character varying 2147483647 ✓ Variable-length text in a Unicode character set
null null Null
numeric num numeric 100 ✓ Exact numeric of selectable precision
real double precision ✓ Double precision floating-point number (8 bytes)
smallint int2 smallint 5 ✓ Two-byte integer
text long character varying ✓ Very long variable-length character string
time time ✓ Time of day without time zone
time with time zone timetz time with time zone ✓ Time of day, including time zone
timestamp datetime, timestamp without time zone timestamp 7 ✓ Date and time without time zone
timestamp with time zone timestamptz timestamp with time zone ✓ Date and time, including time zone
tinyint int1 tinyint 4 ✓ One-byte integer (0-255)
xml xml ✓ XML data