In general following 21 datatypes are supported in Postgres / PostGIS.
- Numeric Types
- Character Types
- Boolean Type
In standard SQL, a Boolean value can be TRUE, FALSE, or NULL. However, PostgreSQL is quite flexible when dealing with TRUE and FALSE values:
Recognized | True | False |
---|
Yes | true | false |
? | ‘t’ | ‘t’ |
? | ‘true’ | ‘false’ |
? | 'yes' | 'no' |
? | ‘y’ | ‘n’ |
? | ‘1’ | ‘0’ |
? | 'on' | 'off' |
? | >1::BOOLEAN | 0::BOOLEAN |
- Date/Time Types
- Geometric Types (PostGIS)
Support | Name | Storage Size | Description | Low Value | High Value | StreamPipes Domain |
---|
Yes | timestamp [without time zone] | 8 bytes | both date and time (nTZ) | 4713 BC | 294276 AD | http://www.w3.org/2001/XMLSchema#dateTime |
? | timestamp with time zone | 8 bytes | both date and time, wTZ | 4713 BC | 294276 AD | |
? | date | 4 bytes | date (no time of day) | 4713 BC | 5874897 AD | http://www.w3.org/2001/XMLSchema#date |
Yes | time [without time zone] | 8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | http://www.w3.org/2001/XMLSchema#time |
? | time with time zone | 12 bytes | time of day (no date), wTZ | 00:00:00+1459 | 24:00:00-1459 | |
No | interval [ fields ] | 16 bytes | time interval | -178000000 years | 178000000 years | |
Additional Informations to the table:
- timestamp: short form for timestamp without time zone
- timestamptz: short form for timestamp with time zone
- time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6.
Possible inputs for date type and recognized as Date input in Streampipes
Recognized | Example | Description |
---|
? | 1999-01-08 | ISO 8601; January 8 in any mode (recommended format) |
? | January 8, 1999 | unambiguous in any datestyle input mode |
? | 1/8/1999 | January 8 in MDY mode; August 1 in DMY mode |
? | 1/18/1999 | January 18 in MDY mode; rejected in other modes |
? | 01/02/03 | MDY, DMY and YMD |
? | 1999-Jan-08 | January 8 in any mode |
? | Jan-08-1999 | January 8 in any mode |
? | 08-Jan-1999 | January 8 in any mode |
? | 99-Jan-08 | January 8 in YMD mode, else error |
? | 08-Jan-99 | January 8, except error in YMD mode |
? | Jan-08-99 | January 8, except error in YMD mode |
? | 19990108 | ISO 8601; January 8, 1999 in any mode |
? | 990108 | ISO 8601; January 8, 1999 in any mode |
? | 1999.008 | year and day of year |
? | J2451187 | Julian date |
? | January 8, 99 BC | year 99 BC |
Recognized | Example | Description |
---|
? | 04:05:06.789 | ISO 8601 |
? | 04:05:06 | ISO 8601 |
? | 04:05 | ISO 8601 |
? | 040506 | ISO 8601 |
? | 04:05 AM | same as 04:05; AM does not affect value |
? | 04:05 PM | same as 16:05; input hour must be <= 12 |
? | 04:05:06.789-8 | ISO 8601 |
? | 04:05:06-08:00 | ISO 8601 |
? | 04:05-08:00 | ISO 8601 |
? | 040506-08 | ISO 8601 |
? | 04:05:06 PST | time zone specified by abbreviation |
? | 2003-04-12 04:05:06 America/New_York | time zone specified by full name |
Supported | Example | Description |
---|
? | PST | Abbreviation (for Pacific Standard Time) |
? | America/New_York | Full time zone name |
? | PST8PDT | POSIX-style time zone specification |
? | -8:00 | ISO-8601 offset for PST |
? | -800 | ISO-8601 offset for PST |
? | -8 | ISO-8601 offset for PST |
? | zulu | Military abbreviation for UTC |
? | z | Short form of zulu |
The unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970 at UTC. Therefore, the unix time stamp is merely the number of seconds between a particular date and the Unix Epoch.
How to store unix timestamp in Postgres: 1 Storing as bigint 2 Cast with Postgres function to timestamp
- To convert from Unix timestamp to timestamp: <br>
SELECT to_timestamp(<Unix timestamp>);
- To convert Unix timestamp to timestamptz: <br>
SELECT timezone('US/Pacific', to_timestamp(<Unix timestamp>))
- To convert from datetime to Unix timestamp: <br>
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '<timetamptz>');
- to handel PostGIS geometry the extension postgis has to be added as <br>
CREATE EXTENSION IF NOT EXISTS postgis;
. - Geometries are handled in StreamPipes in WKT notation and must to be stored together with an SRID (StreamPipes convention).
- The used column name of the geometry are offen: geom or the_geom, but any other name will do it as well.
- The universal type geometry can store all sub-types.
- Sub-types can only stored the specified sub-type itself, otherwise a [null]-value will be stored instead of the real geometry.
Supported | Type | PostGIS function | StreamPipes Domain |
---|
Yes | Geometry | ST_GeomFromText(text WKT, integer srid) | http://www.opengis.net/ont/geosparql#Geometry |
coming soon | Point | ST_PointFromText(text WKT, integer srid); | |
coming soon | LineString | ST_LineFromText(text WKT, integer srid); | |
coming soon | Polygon | ST_PolygonFromText(text WKT, integer srid); | |
coming soon | MultiPoint | ST_MPointFromText(text WKT, integer srid); | |
coming soon | MultiLineString | ST_MLineFromText(text WKT, integer srid); | |
coming soon | MultiPolygon | ST_MPolyFromText(text WKT, integer srid); | |
coming soon | GeometryCollection | ST_GeomCollFromText(text WKT, integer srid); | |
Indexes make using a spatial database for large data sets possible. Without indexing, any search for a feature would require a “sequential scan” of every record in the database. Indexing speeds up searching by organizing the data into a search tree which can be quickly traversed to find a particular record.
Index can be created with a simple query. The name of the index should be unique. Best practice is tablename_geomColumnName_idx.
CREATE INDEX [NAME OF THE INDEX]
ON [TABLENAME]
USING GIST ([GEOM-COLUMN]);
More Information about spatial Index:
- Binary Data Types
- Monetary Types
- Enumerated Types
- Network Address Types
- Bit String Types
- Text Search Types
- XML Type
- Composite Types
- Domain Types
- Object Identifier Types
- pg_lsn Type
- Pseudo-Types
- Range Types
- JSON Types
- Arrays
- UUID Type