Database Types Postgres / PostGIS

Introduction

In general following 21 datatypes are supported in Postgres / PostGIS.

Supported Types by StreamPipes

1. Numeric Types
2. Character Types
3. Boolean Type

Numeric Types

Integer

Floating

Character Types

Boolean Types

Boolean literals

In standard SQL, a Boolean value can be TRUE, FALSE, or NULL. However, PostgreSQL is quite flexible when dealing with TRUE and FALSE values:

Should be supported by StreamPipes

1. Date/Time Types
2. Geometric Types (PostGIS)

Date/Time Types

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.

Date input

Possible inputs for date type and recognized as Date input in Streampipes

Time inputs

Time Zone Input

• Time Zone lists:
• Time Zone Abbreviations
• Full time zone name
• List of military time zones

Unix timestamp

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>');

Geometries

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

Index in PostGIS

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:

• PostGIS Workshop about Index

Not supported types by StreamPipes

1. Binary Data Types
2. Monetary Types
3. Enumerated Types
4. Network Address Types
5. Bit String Types
6. Text Search Types
7. XML Type
8. Composite Types
9. Domain Types
10. Object Identifier Types
11. pg_lsn Type
12. Pseudo-Types
13. Range Types
14. JSON Types
15. Arrays
16. UUID Type

• https://www.postgresql.org/docs/current/datatype.html