Data Types

Learn the supported data types in CARTO.

Geometry

CARTO creates a geometry column called the_geom on every table you generate. The column is where you will store your geometry information, either POINTS, MULTILINESTRINGS, or MULTIPOLYGONS. PostGIS supports many geometry types, but to simplify visualization and analysis, CARTO turns all geometries into one of the three listed. For the time being, you can only have one geometry type per column.

Number

A number column in CARTO can contain any numeric value. In the backend we implement number using the double precision type in PostgreSQL.

Working with Numbers

Where would any data analysis be without using numeric analysis? The first thing to remember in CARTO is that every column has a type and sometimes during import, numbers are interpreted as strings. Be sure that the column you are trying to analyze is properly a number, and if it is not, change it. PostgreSQL has comprehensive documentation for all the analyses you want to do.

LOG VALUE

SELECT * FROM {table_name} WHERE log(population) < 1000

String

Strings are any combination of numbers and characters in UTF-8. There is no limit to the size of your string (just remember your storage quotas!). The CARTO string type is implemented using a text field in PostgreSQL.

Working with Strings

Strings can be very useful for categorizing, grouping, or excluding data from your SQL queries. The string columns in CARTO are case-sensitive, meaning you can use them for precision queries. There are also ways to query them for less precise comparisons of data. See the following for an example of non-case-sensitive matching to part of a string field.

If you are editing your datasets string column by deleting text from one of its fields, the field will stay blank instead of becoming “null”. To make it “null”, you can apply an SQL query for the value that you are replacing.

For example, suppose that you have a dataset with a country column and you want all rows that contain the value Italy to be null.

UPDATE table_name SET column_name = null WHERE column_name ilike 'Italy'

Replace the SQL request with your table name and enter the null value that you are replacing. Additionally, you can search for partial matches without case-sensitivity, for example:

PARTIAL MATCH

SELECT * FROM {table_name} WHERE country_name ilike 'italy%'

• ilike allows you to search partial matches without case-sensitivity
• like enforces case sensitivity
• % symbol indicates to stop evaluating string matches after that character

Date/Time

You can store precise information about date and time fields by using the timestamp Postgres data type. Timestamp PostgreSQL data is based on the standard SQL date/time type of data. For details about the SQL timestamp field and how time zones are determined, see the PostgreSQL documentation about date/time types.

Working with Date/Time

There are many standard SQL date/time functions and operators that can be applied for data analysis, such as: time, interval, date, timestamp, and so on. For details, see the PostgreSQL Date/Time Functions and Operators. As per the PostgreSQL documentation, all date/time functions and operators either include or exclude the timestamp variable as part of the response, depending on how you request the data.

Analyze created_at and updated_at Date Columns

Previous versions of the CARTO PostgreSQL Extension automatically generated the created_at and updated_at date response columns. While these columns are no longer generated (to reduce the size of the dataset and increase the performance time), you can continue to use these data columns if you are analyzing older datasets with an SQL query.

The following SQL query describes how to add the created_at and updated_at columns to your dataset:

alter table TABLENAME add column created_at timestamptz not null DEFAULT now()
alter table TABLENAME add column updated_at timestamptz not null DEFAULT now()

The following SQL query describes how to create a trigger that updates the updated_at column, whenever there is a change in a row:

CREATE OR REPLACE FUNCTION _update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

Tip: Once you define this function in your user database, the function is available to be applied to other datasets associated with your account.

The following SQL query describes how to add the trigger function to the updated at column, so that data is tracked when the date/time information changes:

CREATE trigger update_updated_at_trigger
BEFORE UPDATE ON TABLENAME
FOR EACH ROW
EXECUTE PROCEDURE _update_updated_at()

Non-Default Data Types

The above types, geometry, number, string, and date are the default data types we use in CARTO. You may want to use more advanced types, such as Arrays or specific numeric types. Go ahead! We won’t stop you, you’ll just need to create those fields using SQL.