GREENPLUM: Datatypes - Description

Greenplum data types

This article contains the overview of Greenplum Data Types and detailed examples. Greenplum Unified Analytics Platform (UAP) combines the co-processing of structured and unstructured data with a productivity engine that enables collaboration among your data science team. Greenplum UAP includes Greenplum Database, Greenplum HD, and Greenplum Chorus

Greenplum Data Types	Size	Description
bigint	8 bytes	large range integer
bigserial	8 bytes	large autoincrementing integer
bit [ (n) ]	n bits	fixed-length bit string
bit varying [ (n) ]	actual number of bits	variable-length bit string
boolean	1 byte	logical boolean (true/false)
box	32 bytes	rectangular box in the plane – not allowed in distribution key columns.
bytea	1 byte + binary string	variable-length binary string
character [ (n) ]	1 byte + n	fixed-length, blank padded
character varying [ (n) ]	1 byte + string size	variable-length with limit
cidr	12 or 24 bytes	IPv4 and IPv6 networks
circle	24 bytes	circle in the plane – not allowed in distribution key columns.
date	4 bytes	calendar date (year, month, day)
decimal [ (p, s) ]	variable	user-specified precision, exact
double precision	8 bytes	variable-precision, inexact
inet	12 or 24 bytes	IPv4 and IPv6 hosts and networks
integer	4 bytes	usual choice for integer
interval [ (p) ]	12 bytes	time span
lseg	32 bytes	line segment in the plane – not allowed in distribution key columns.
macaddr	6 bytes	MAC addresses
money	4 bytes	currency amount
path	16+16n bytes	geometric path in the plane – not allowed in distribution key columns.
point	16 bytes	geometric point in the plane – not allowed in distribution key columns.
polygon	40+16n bytes	closed geometric path in the plane – not allowed in distribution key columns.
real	4 bytes	variable-precision, inexact
serial	4 bytes	autoincrementing integer
smallint	2 bytes	small range integer
text	1 byte + string size	variable unlimited length
time [ (p) ] [ without time zone ]	8 bytes	time of day only
time [ (p) ] with time zone	12 bytes	time of day only, with time zone
timestamp [(p)] [without time zone ]	8 bytes	both date and time
timestamp [ (p) ] with time zone	8 bytes	both date and time, with time zone
xml	1 byte + xml size	variable unlimited length

GREENPLUM: String concatenation for multiple rows in same table

 concatenate strings of a string field in a PostgreSQL 'group by' query?

I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table: ID   COMPANY_ID   EMPLOYEE 1    1            Anna 2    1            Bill 3    2            Carol 4    2            Dave and I wanted to group by company_id to get something like: COMPANY_ID   EMPLOYEE 1            Anna, Bill 2            Carol, Dave

Update as of PostgreSQL 9.0: Newer versions of PostgreSQL have the string_agg(expression, delimiter) function that will do exactly what the question asked for, even letting you specify the delimiter string Update as of PostgreSQL 8.4: PostgreSQL 8.4 introduced the aggregate function array_agg(expression) which concatenates the values into an array. Then array_to_string() can be used to give the desired result: SELECT company_id, array_to_string(array_agg(employee), ',') FROM mytable GROUP BY company_id; There is no built-in aggregate function to concatenate strings. It seems like this would be needed, but it's not part of the default set. A web search however reveals some manual implementations: CREATE AGGREGATE textcat_all(   basetype    = text,   sfunc       = textcat,   stype       = text,   initcond    = '' ); Here is the CREATE AGGREGATE documentation. In order to get the ", " inserted in between them without having it at the end, you might want to make your own concatenation function and substitute it for the "textcat" above.: CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$   BEGIN     IF acc IS NULL OR acc = '' THEN       RETURN instr;     ELSE       RETURN acc || ', ' || instr;     END IF;   END; $$ LANGUAGE plpgsql; Note: The function above will output a comma even if the value in the row is null/empty, which outputs: a, b, c, , e, , g If you would prefer to remove extra commas to output: a, b, c, e, g just add an ELSIF check to the function: CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$   BEGIN     IF acc IS NULL OR acc = '' THEN       RETURN instr;     ELSIF instr IS NULL OR instr = '' THEN       RETURN acc;     ELSE       RETURN acc || ', ' || instr;     END IF;   END; $$ LANGUAGE plpgsql;