Utilities

Utilities are functions for working with data of various types that are difficult to categorise. Their descriptions are collected in this section.

coalesce()

Description

Returns the first value other than NULL from the list of argument values.
Usage

coalesce(argument1[, argument2, {…​])

If the only argument has the value NULL, then NULL is returned.

See example 
SELECT
    coalesce(NULL, 'Tengri', NULL) AS result_1,
    coalesce(NULL, '', NULL) AS result_2,
    coalesce('Tengri') AS result_3,
    coalesce(NULL) AS result_4;
+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| Tengri   |          | Tengri   | null     |
+----------+----------+----------+----------+

generate_series()

Description

Generates a list of values in the range between start and stop.
Usage

generate_series([start,] stop[, step])

The parameters start and stop are treated as "inclusive".
The default value for start is 0, and for step is 1.

See example 
SELECT
    generate_series(10) AS stop,
    generate_series(5, 10) AS start_stop,
    generate_series(5, 10, 2) AS start_stop_step;
+--------------------------+----------------+-----------------+
| stop                     | start_stop     | start_stop_step |
+--------------------------+----------------+-----------------+
| {0,1,2,3,4,5,6,7,8,9,10} | {5,6,7,8,9,10} | {5,7,9}         |
+--------------------------+----------------+-----------------+

hash()

Description

Returns a hash of the data from argument as a number.
Usage

hash(argument)
See example 
SELECT
    hash('Tengri') AS hash;
+----------------------+
|         hash         |
+----------------------+
| 15418814193266442000 |
+----------------------+

read_csv()

Description

Reads the .csv file and writes the read data to a table.
Usage 

read_csv(filepath[,
    skip = 5,
    header = FALSE,
    all_varchar = TRUE]
    )

Reads the .csv file at the specified filepath path and writes the read data to a table. Skipping of initial rows, type conversion and column header settings can be adjusted by optional parameters.

To download .csv files from a local computer, use Download Wizard.

Parameters

  • skip — optional parameter specifying the number of initial lines of the file to skip when reading (including the first line!).

  • header — optional parameter specifying whether to interpret the first row of the table as a column header row (default — TRUE)

  • all_varchar — optional parameter; if TRUE, type conversion will be disabled and the type of all columns will be text (default — FALSE)

See examples

The examples use the file strava_activities.csv from the script Analysing geodata from sports trackers.

Let’s output the first five lines from the file without using optional function parameters:

SELECT *
    FROM read_csv('abogdanov/734b6e098011_strava_activities.csv')
    LIMIT 5
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| Activity ID | Activity Date             | Activity Name         | Activity Type | Activity Description | Elapsed Time | Distance | Max Heart Rate | Relative Effort | Commute | Activity Private Note | Activity Gear        | Filename                 | Athlete Weight | Bike Weight | Elapsed Time_1 | Moving Time | Distance_1 | Max Speed | Average Speed | Elevation Gain | Elevation Loss | Elevation Low | Elevation High | Max Grade | Average Grade | Average Positive Grade | Average Negative Grade | Max Cadence | Average Cadence | Max Heart Rate_1 | Average Heart Rate | Max Watts | Average Watts | Calories | Max Temperature | Average Temperature | Relative Effort_1 | Total Work | Number of Runs | Uphill Time | Downhill Time | Other Time | Perceived Exertion | Type | Start Time | Weighted Average Power | Power Count | Prefer Perceived Exertion | Perceived Relative Effort | Commute_1 | Total Weight Lifted | From Upload | Grade Adjusted Distance | Weather Observation Time | Weather Condition | Weather Temperature | Apparent Temperature | Dewpoint | Humidity | Weather Pressure | Wind Speed | Wind Gust | Wind Bearing | Precipitation Intensity | Sunrise Time | Sunset Time | Moon Phase | Bike    | Gear | Precipitation Probability | Precipitation Type | Cloud Cover | Weather Visibility | UV Index | Weather Ozone | Jump Count | Total Grit | Average Flow | Flagged | Average Elapsed Speed | Dirt Distance | Newly Explored Distance | Newly Explored Dirt Distance | Activity Count | Total Steps | Carbon Saved | Pool Length | Training Load | Intensity | Average Grade Adjusted Pace | Timer Time | Total Cycles | Recovery | With Pet | Competition | Long Run | For a Cause | Media |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| 187782986   | Aug 31, 2014, 10:54:43 AM | Из дома к родителям   | Ride          | null                 | 2462         | 19.41    | null           | null            | false   | null                  | Merida S-Presso 900d | activities/187782986.gpx | 83             | 11          | 2462           | 2286        | 19415.4    | 13.5      | null          | 158.2          | null           | 118.2         | 206            | 12        | 0.3           | null                   | null                   | null        | null            | null             | null               | null      | 192           | null     | null            | null                | null              | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | 1512511 | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null  |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| 187840092   | Aug 31, 2014, 12:48:49 PM | Послеобеденный заезд  | Ride          | null                 | 2396         | 15.9     | null           | null            | false   | null                  | null                 | activities/187840092.gpx | 83             | null        | 2396           | 1920        | 15906.6    | 13.4      | null          | 93.9           | null           | 122.5         | 214.6          | 8.8       | -0.5          | null                   | null                   | null        | null            | null             | null               | null      | 178           | null     | null            | null                | null              | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null    | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null  |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| 187844596   | Aug 31, 2014, 1:29:23 PM  | Послеобеденный заезд  | Ride          | null                 | 103          | 0.7      | null           | null            | false   | null                  | null                 | activities/187844596.gpx | 83             | null        | 103            | 103         | 708.4      | 8.4       | null          | 0              | null           | 127.4         | 136            | 3.7       | 1.2           | null                   | null                   | null        | null            | null             | null               | null      | 172           | null     | null            | null                | null              | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null    | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null  |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| 187850580   | Aug 31, 2014, 1:37:59 PM  | Крылатское малый круг | Ride          | null                 | 561          | 4.41     | null           | null            | false   | null                  | null                 | activities/187850580.gpx | 83             | null        | 561            | 561         | 4414.1     | 13.6      | null          | 46.7           | null           | 137.6         | 179.4          | 10.4      | -0.1          | null                   | null                   | null        | null            | null             | null               | null      | 192           | 120      | null            | null                | null              | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null    | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null  |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+
| 187867604   | Aug 31, 2014, 1:59:58 PM  | Послеобеденный заезд  | Ride          | null                 | 886          | 5.03     | null           | null            | false   | null                  | null                 | activities/187867604.gpx | 83             | null        | 886            | 820         | 5036.3     | 10.6      | null          | 34.6           | null           | 123           | 156.7          | 7.3       | 0.3           | null                   | null                   | null        | null            | null             | null               | null      | 127           | null     | null            | null                | null              | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null    | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null  |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+--------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+---------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------+

Let’s output the first five lines from the .xlsx file, ignoring the header line:

SELECT *
    FROM read_csv('abogdanov/734b6e098011_strava_activities.csv',
        header = FALSE
        )
    LIMIT 5
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| column00    | column01                  | column02              | column03      | column04             | column05     | column06 | column07       | column08        | column09 | column10              | column11             | column12                 | column13       | column14    | column15     | column16    | column17 | column18  | column19      | column20       | column21       | column22      | column23       | column24  | column25      | column26               | column27               | column28    | column29        | column30       | column31           | column32  | column33      | column34 | column35        | column36            | column37        | column38   | column39       | column40    | column41      | column42   | column43           | column44 | column45   | column46               | column47    | column48                  | column49                  | column50 | column51            | column52    | column53                | column54                 | column55          | column56            | column57             | column58 | column59 | column60         | column61   | column62  | column63     | column64                | column65     | column66    | column67   | column68  | column69 | column70                  | column71           | column72    | column73           | column74 | column75      | column76   | column77   | column78     | column79 | column80              | column81      | column82                | column83                     | column84       | column85    | column86     | column87    | column88      | column89  | column90                    | column91   | column92     | column93 | column94 | column95    | column96 | column97    | column98 |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| Activity ID | Activity Date             | Activity Name         | Activity Type | Activity Description | Elapsed Time | Distance | Max Heart Rate | Relative Effort | Commute  | Activity Private Note | Activity Gear        | Filename                 | Athlete Weight | Bike Weight | Elapsed Time | Moving Time | Distance | Max Speed | Average Speed | Elevation Gain | Elevation Loss | Elevation Low | Elevation High | Max Grade | Average Grade | Average Positive Grade | Average Negative Grade | Max Cadence | Average Cadence | Max Heart Rate | Average Heart Rate | Max Watts | Average Watts | Calories | Max Temperature | Average Temperature | Relative Effort | Total Work | Number of Runs | Uphill Time | Downhill Time | Other Time | Perceived Exertion | Type     | Start Time | Weighted Average Power | Power Count | Prefer Perceived Exertion | Perceived Relative Effort | Commute  | Total Weight Lifted | From Upload | Grade Adjusted Distance | Weather Observation Time | Weather Condition | Weather Temperature | Apparent Temperature | Dewpoint | Humidity | Weather Pressure | Wind Speed | Wind Gust | Wind Bearing | Precipitation Intensity | Sunrise Time | Sunset Time | Moon Phase | Bike      | Gear     | Precipitation Probability | Precipitation Type | Cloud Cover | Weather Visibility | UV Index | Weather Ozone | Jump Count | Total Grit | Average Flow | Flagged  | Average Elapsed Speed | Dirt Distance | Newly Explored Distance | Newly Explored Dirt Distance | Activity Count | Total Steps | Carbon Saved | Pool Length | Training Load | Intensity | Average Grade Adjusted Pace | Timer Time | Total Cycles | Recovery | With Pet | Competition | Long Run | For a Cause | Media    |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| 187782986   | Aug 31, 2014, 10:54:43 AM | Из дома к родителям   | Ride          | null                 | 2462         | 19.41    | null           | null            | false    | null                  | Merida S-Presso 900d | activities/187782986.gpx | 83.0           | 11.0        | 2462.0       | 2286.0      | 19415.4  | 13.5      | null          | 158.2          | null           | 118.2         | 206.0          | 12.0      | 0.3           | null                   | null                   | null        | null            | null           | null               | null      | 192.0         | null     | null            | null                | null            | null       | null           | null        | null          | null       | null               | null     | null       | null                   | null        | null                      | null                      | 0.0      | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | 1512511.0 | null     | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null     | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null     |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| 187840092   | Aug 31, 2014, 12:48:49 PM | Послеобеденный заезд  | Ride          | null                 | 2396         | 15.90    | null           | null            | false    | null                  | null                 | activities/187840092.gpx | 83.0           | null        | 2396.0       | 1920.0      | 15906.6  | 13.4      | null          | 93.9           | null           | 122.5         | 214.6          | 8.8       | -0.5          | null                   | null                   | null        | null            | null           | null               | null      | 178.0         | null     | null            | null                | null            | null       | null           | null        | null          | null       | null               | null     | null       | null                   | null        | null                      | null                      | 0.0      | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null      | null     | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null     | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null     |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| 187844596   | Aug 31, 2014, 1:29:23 PM  | Послеобеденный заезд  | Ride          | null                 | 103          | 0.70     | null           | null            | false    | null                  | null                 | activities/187844596.gpx | 83.0           | null        | 103.0        | 103.0       | 708.4    | 8.4       | null          | 0.0            | null           | 127.4         | 136.0          | 3.7       | 1.2           | null                   | null                   | null        | null            | null           | null               | null      | 172.0         | null     | null            | null                | null            | null       | null           | null        | null          | null       | null               | null     | null       | null                   | null        | null                      | null                      | 0.0      | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null      | null     | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null     | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null     |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+
| 187850580   | Aug 31, 2014, 1:37:59 PM  | Крылатское малый круг | Ride          | null                 | 561          | 4.41     | null           | null            | false    | null                  | null                 | activities/187850580.gpx | 83.0           | null        | 561.0        | 561.0       | 4414.1   | 13.6      | null          | 46.7           | null           | 137.6         | 179.4          | 10.4      | -0.1          | null                   | null                   | null        | null            | null           | null               | null      | 192.0         | 120.0    | null            | null                | null            | null       | null           | null        | null          | null       | null               | null     | null       | null                   | null        | null                      | null                      | 0.0      | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null      | null     | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null     | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null     |
+-------------+---------------------------+-----------------------+---------------+----------------------+--------------+----------+----------------+-----------------+----------+-----------------------+----------------------+--------------------------+----------------+-------------+--------------+-------------+----------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+----------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-----------------+------------+----------------+-------------+---------------+------------+--------------------+----------+------------+------------------------+-------------+---------------------------+---------------------------+----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+-----------+----------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+----------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+----------+

read_parquet()

Description

Reads the .parquet file and writes the read data to a table.
Usage

read_parquet(filepath)

Reads the .parquet file at the specified filepath path and writes the read data to a table.

To download .parquet files from a local computer, use Download Wizard.

See example

Let’s download one of the data files from here via Download Wizard and read it:

SELECT *
    FROM read_parquet('abogdanov/05e4fddda22d_green_tripdata_2025-01.parquet')
    LIMIT 5
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| VendorID | lpep_pickup_datetime | lpep_dropoff_datetime | store_and_fwd_flag | RatecodeID | PULocationID | DOLocationID | passenger_count | trip_distance | fare_amount | extra | mta_tax | tip_amount | tolls_amount | ehail_fee | improvement_surcharge | total_amount | payment_type | trip_type | congestion_surcharge | cbd_congestion_fee |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| 2        | 2025-01-01 00:03:01  | 2025-01-01 00:17:12   | N                  | 1          | 75           | 235          | 1               | 5.93          | 24.7        | 1     | 0.5     | 6.8        | 0            | null      | 1                     | 34           | 1            | 1         | 0                    | 0                  |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| 2        | 2025-01-01 00:19:59  | 2025-01-01 00:25:52   | N                  | 1          | 166          | 75           | 1               | 1.32          | 8.6         | 1     | 0.5     | 0          | 0            | null      | 1                     | 11.1         | 2            | 1         | 0                    | 0                  |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| 2        | 2025-01-01 00:05:29  | 2025-01-01 00:07:21   | N                  | 5          | 171          | 73           | 1               | 0.41          | 25.55       | 0     | 0       | 0          | 0            | null      | 1                     | 26.55        | 2            | 2         | 0                    | 0                  |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| 2        | 2025-01-01 00:52:24  | 2025-01-01 01:07:52   | N                  | 1          | 74           | 223          | 1               | 4.12          | 21.2        | 1     | 0.5     | 6.13       | 6.94         | null      | 1                     | 36.77        | 1            | 1         | 0                    | 0                  |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+
| 2        | 2025-01-01 00:25:05  | 2025-01-01 01:01:10   | N                  | 1          | 66           | 158          | 1               | 4.71          | 33.8        | 1     | 0.5     | 7.81       | 0            | null      | 1                     | 46.86        | 1            | 1         | 2.75                 | 0                  |
+----------+----------------------+-----------------------+--------------------+------------+--------------+--------------+-----------------+---------------+-------------+-------+---------+------------+--------------+-----------+-----------------------+--------------+--------------+-----------+----------------------+--------------------+

read_xlsx()

Description

Reads the .xlsx file and writes the read data to a table.
Usage 

read_xlsx(filepath[,
    sheet = 'Лист1',
    range = 'A5:E100',
    stop_at_empty = TRUE,
    header = TRUE,
    all_varchar = TRUE]
    )

Reads the .xlsx file at the specified filepath and writes the read data to a table. If a read range or file sheet is specified, selects only the specified data. Type conversion and column header settings can be adjusted by optional parameters.

To load .xlsx files from a local computer, use Download Wizard.

Options

  • sheet — optional parameter specifying the sheet name of the .xlsx file to read. By default, the first sheet is taken.

  • range — optional parameter specifying the range of cells of the table of the .xlsx file to be read. The range is specified from the top left cell to the bottom right cell, for example: range = 'A5:E100'.
    It is possible to specify the range non-strictly, e.g.:

    • range = 'A:E100' — selects cells from the first cell in column A to E100.

    • range = 'B:E' — selects cells from the first in column B to the last in column E (in this case it is recommended to use the constraint stop_at_empty = TRUE, see example below).

  • stop_at_empty — optional parameter indicating whether to stop reading on the first empty row of the table

  • header — optional parameter indicating whether to interpret the first row of the table as a column header row (default — TRUE if there are no empty cells in the first row)

  • all_varchar — optional parameter; if TRUE, type conversion will be disabled and the type of all columns will be text (default — FALSE)

See examples

The examples use the file instagram_example_result_1.xlsx from the script Analysing data from Instagram* posts.

Let’s output the first five lines from the file without using optional function parameters:

SELECT *
    FROM read_xlsx('abogdanov/b95f3e020363_instagram_example_result_1.xlsx')
    LIMIT 5
+-------------------+-------+----------+----------+--------------+------------+--------------+
| artist_name       | songs | ig_posts | comments | tagged_users | likescount | repliescount |
+-------------------+-------+----------+----------+--------------+------------+--------------+
| "telfarglobal"    | 9     | 136      | 985      | 120          | 634        | 156          |
+-------------------+-------+----------+----------+--------------+------------+--------------+
| "everlane"        | 1     | 95       | 532      | 6            | 513        | 65           |
+-------------------+-------+----------+----------+--------------+------------+--------------+
| "asos"            | 3     | 93       | 503      | 60           | 583        | 85           |
+-------------------+-------+----------+----------+--------------+------------+--------------+
| "victoriassecret" | 1     | 83       | 686      | 49           | 185        | 16           |
+-------------------+-------+----------+----------+--------------+------------+--------------+
| "summerfridays"   | 2     | 65       | 574      | 48           | 173        | 18           |
+-------------------+-------+----------+----------+--------------+------------+--------------+

Let’s output the first five rows of the first four columns from the .xlsx file:

SELECT *
    FROM read_xlsx('abogdanov/b95f3e020363_instagram_example_result_1.xlsx',
        range = 'A1:D6'
        )
+-------------------+-------+----------+----------+
| artist_name       | songs | ig_posts | comments |
+-------------------+-------+----------+----------+
| "telfarglobal"    | 9     | 136      | 985      |
+-------------------+-------+----------+----------+
| "everlane"        | 1     | 95       | 532      |
+-------------------+-------+----------+----------+
| "asos"            | 3     | 93       | 503      |
+-------------------+-------+----------+----------+
| "victoriassecret" | 1     | 83       | 686      |
+-------------------+-------+----------+----------+
| "summerfridays"   | 2     | 65       | 574      |
+-------------------+-------+----------+----------+

Let’s output columns A to C from file .xlsx, for this we will use the range by column names and the stop_at_empty parameter (otherwise the range will not have an explicit right border, because the end cell of the range is not specified):

SELECT *
    FROM read_xlsx('abogdanov/b95f3e020363_instagram_example_result_1.xlsx',
        range = 'A:C',
        stop_at_empty = TRUE
        )
+--------------------------------------------------------------------+-------+----------+
| artist_name                                                        | songs | ig_posts |
+--------------------------------------------------------------------+-------+----------+
| "telfarglobal"                                                     | 9     | 136      |
+--------------------------------------------------------------------+-------+----------+
| "everlane"                                                         | 1     | 95       |
+--------------------------------------------------------------------+-------+----------+
| "asos"                                                             | 3     | 93       |
+--------------------------------------------------------------------+-------+----------+
| "victoriassecret"                                                  | 1     | 83       |
+--------------------------------------------------------------------+-------+----------+
| "summerfridays"                                                    | 2     | 65       |
+--------------------------------------------------------------------+-------+----------+
| ...                                                                | ...   | ...      |
+--------------------------------------------------------------------+-------+----------+
226 rows

cast()

Description

Casts the data type to the specified data type.
Usage

cast(argument AS DATA_TYPE)

Casts data type of an argument to the specified one. If the cast fails, an error is raised.

See also try_cast(), Operator ::.

See examples 

  • SELECT
    cast('1' AS BIGINT)                        AS result_1,
    cast('00:00:00.0' AS TIME)                 AS result_2,
    cast('2025-12-03T00:00:00.0' AS TIMESTAMP) AS result_3;
    +----------+----------+---------------------+
| result_1 | result_2 | result_3            |
+----------+----------+---------------------+
| 1        | 00:00:00 | 2025-12-03 00:00:00 |
+----------+----------+---------------------+

  • SELECT
    cast('Tengri' AS BIGINT); -- error expected
    ERROR: ConversionException: Conversion Error: Could not convert string 'Tengri' to INT64

try_cast()

Description

Casts the data type to the specified data type.
Usage

try_cast(argument AS DATA_TYPE)

Casts data type of an argument to the specified one. If the cast fails, returns NULL.

See also cast(), Operator ::.

See examples 
SELECT
    try_cast('1' AS BIGINT)                        AS result_1,
    try_cast('00:00:00.0' AS TIME)                 AS result_2,
    try_cast('2025-01-01T00:00:00.0' AS TIMESTAMP) AS result_3,
    try_cast('Tengri' AS BIGINT)                   AS result_4, -- null expected
    try_cast('00:00:00.0' AS TIMESTAMP)            AS result_5; -- null expected
+----------+----------+---------------------+----------+----------+
| result_1 | result_2 | result_3            | result_4 | result_5 |
+----------+----------+---------------------+----------+----------+
| 1        | 00:00:00 | 2025-01-01 00:00:00 | null     | null     |
+----------+----------+---------------------+----------+----------+

unnest()

Description

Expands lists or structures from argument into a set of distinct values.
Usage

unnest(argument) [, recursive := true] [, max_depth := <num>]

Applying the function to a list yields one line for each element in the list. The usual scalar expressions in the same SELECT expression are repeated for each row output.

When multiple lists are expanded in the same SELECT expression, they are expanded each into a separate column. If one list is longer than another, the shorter list is filled with NULL values.

The function changes the cardinality of the data.

Parameters

  • recursive := true
    Enables recursive mode. If this mode is enabled (value true), the function fully expands lists and then fully expands nested structures. This can be useful for fully "flattening" columns that contain lists within lists or structures within lists. Note that lists within structures are not expanded.

    Read more about the parameter in the examples

    Let’s show how this parameter works on two examples with the same data with and without the parameter enabled:

    SELECT
    unnest([[1, 2, 3], [4, 5]], recursive := true) AS result;
    +--------+
| result |
+--------+
| 1      |
+--------+
| 2      |
+--------+
| 3      |
+--------+
| 4      |
+--------+
| 5      |
+--------+
    SELECT
    unnest([[1, 2, 3], [4, 5]]) AS result;
    +---------+
|  result |
+---------+
| {1,2,3} |
+---------+
| {4,5}   |
+---------+

  • max_depth := <num>
    The max_depth parameter allows you to limit the maximum depth of recursive deployment. Recursive mode is automatically enabled if the maximum depth is specified.

    For more information about the parameter, see examples.

    Let’s show how this parameter works using three examples with the same data: with default deployment depth (1), with deployment depth 2 and with deployment depth 3:

    SELECT
    unnest([[['T', 'e'], ['n', 'g']], [['r', 'i'], []], [['!', '!', '!']]])
AS result;
    +-------------------------+
|          result         |
+-------------------------+
| {['T', 'e'],['n', 'g']} |
+-------------------------+
| {['r', 'i'],[]}         |
+-------------------------+
| {['!', '!', '!']}       |
+-------------------------+
    SELECT
    unnest([[['T', 'e'], ['n', 'g']], [['r', 'i'], []], [['!', '!', '!']]],
    max_depth := 2)
AS result;
    +---------+
|  result |
+---------+
| {T,e}   |
+---------+
| {n,g}   |
+---------+
| {r,i}   |
+---------+
| {}      |
+---------+
| {!,!,!} |
+---------+
    SELECT
    unnest([[['T', 'e'], ['n', 'g']], [['r', 'i'], []], [['!', '!', '!']]],
    max_depth := 3)
AS result;
    +--------+
| result |
+--------+
| T      |
+--------+
| e      |
+--------+
| n      |
+--------+
| g      |
+--------+
| r      |
+--------+
| i      |
+--------+
| !      |
+--------+
| !      |
+--------+
| !      |
+--------+
See more examples 
SELECT
    unnest([1,2,3])       AS numbers,
    unnest(['a','b','c']) AS letters;
+---------+---------+
| numbers | letters |
+---------+---------+
| 1       | a       |
+---------+---------+
| 2       | b       |
+---------+---------+
| 3       | c       |
+---------+---------+
SELECT
    unnest([1,2,3])   AS numbers,
    unnest(['a','b']) AS letters;
+---------+---------+
| numbers | letters |
+---------+---------+
| 1       | a       |
+---------+---------+
| 2       | b       |
+---------+---------+
| 3       | null    |
+---------+---------+
SELECT
    unnest([{'column_a': 1, 'column_b': 84},
            {'column_a': 100, 'column_b': NULL, 'column_c':22}],
            recursive := true);
+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
| 1        | 84       | null     |
+----------+----------+----------+
| 100      | null     | 22       |
+----------+----------+----------+
SELECT
    unnest([{'column_a': 1, 'column_b': 84},
            {'column_a': 100, 'column_b': NULL, 'column_c':22}])
AS result;
+-----------------------------------------------------+
|                        result                       |
+-----------------------------------------------------+
| {"column_a": 1, "column_b": 84, "column_c": null}   |
+-----------------------------------------------------+
| {"column_a": 100, "column_b": null, "column_c": 22} |
+-----------------------------------------------------+

Operator ::

Description

Casts the data type to the specified data type.
Usage

argument::DATA_TYPE

Casts data type of an argument to the specified one. If the conversion fails, an error is raised.

See also cast(), try_cast().

See examples 

  • SELECT
    '1'::BIGINT                         AS result_1,
    '00:00:00.0'::TIME                  AS result_2,
    '2025-01-01T00:00:00.0'::TIMESTAMP  AS result_3;
    +----------+----------+---------------------+
| result_1 | result_2 | result_3            |
+----------+----------+---------------------+
| 1        | 00:00:00 | 2025-01-01 00:00:00 |
+----------+----------+---------------------+

  • SELECT
    'Tengri'::BIGINT; -- error expected
    ERROR: ConversionException: Conversion Error: Could not convert string 'Tengri' to INT64