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.
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.
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.
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:
ilikeallows you to search partial matches without case-sensitivity
likeenforces case sensitivity
%symbol indicates to stop evaluating string matches after that character
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.
updated_at Date Columns
Previous versions of the CARTO PostgreSQL Extension automatically generated the
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
updated_at columns to your dataset:
The following SQL query describes how to create a trigger that updates the
updated_at column, whenever there is a change in a row:
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:
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. For more background on SQL, check out our Map Academy course.