Supported ClickHouse functions
Contents
This is an ever-expanding list of enabled ClickHouse functions.
You can find their full definitions in the ClickHouse documentation. Additionally, we include a list of popular ones and their uses in the HogQL expressions and SQL insight documentation.
Most of the type conversion functions are safer versions of the equivalent ClickHouse functions to avoid NULL/zero mistakes. If the function's name includes a leading underscore, then it's not safe and simply calls the underlying ClickHouse function.
Arithmetic
plusminusmultiplydivideintDivintDivOrZeromodulomoduloOrZeropositiveModulonegateabsgcdlcmmax2min2multiplyDecimaldivideDecimal
Arrays and strings in common
emptynotEmptylengthreverseinnotIn
Arrays
arrayrangearrayConcatarrayElementhashasAllhasAnyhasSubstrindexOfarrayCountcountEqualarrayEnumeratearrayEnumerateUniqarrayPopBackarrayPopFrontarrayPushBackarrayPushFrontarrayResizearraySlicearraySortarrayReverseSortarrayUniqarrayJoinarrayDifferencearrayDistinctarrayEnumerateDensearrayIntersectarrayReducearrayReversearrayFilterarrayFlattenarrayCompactarrayZiparrayAUCarrayMaparrayFillarrayFoldarrayWithConstantarraySplitarrayReverseFillarrayReverseSplitarrayRotateLeftarrayRotateRightarrayExistsarrayAllarrayFirstarrayLastarrayFirstIndexarrayLastIndexarrayMinarrayMaxarraySumarrayAvgarrayCumSumarrayCumSumNonNegativearrayProductarrayStringConcatgenerateSeries
Comparison
equalsnotEqualslessgreaterlessOrEqualsgreaterOrEquals
Logical
andorxornot
Type conversions
hexunhextoInttoIntOrZerotoIntOrDefault_toInt8_toInt16_toInt32_toInt64_toUInt64_toUInt128toFloattoFloatOrZerotoFloatOrDefaulttoDecimaltoUUIDtoStringtoBooltoJSONStringtoTypeNameparseDateTimeparseDateTimeBestEffortcityHash64sha1UUIDv7ToDateTimetoNullableStringtoUUIDOrDefault
sha1
sha1(value) returns the SHA-1 digest of a string as 40 lowercase hexadecimal characters:
sha1 accepts one argument and returns NULL for a NULL input. It hashes UTF-8 bytes without normalizing Unicode.
For direct PostgreSQL queries, sha1 requires the pgcrypto extension. After installing or moving the extension, refresh the source's schemas in PostHog.
Dates and times
toDatetoDateTimetoDateTime64toDateTimeUS_toDatetoTimeZonetimeZoneOftimeZoneOffsettimezonetoYeartoQuartertoMonthtoDayOfYeartoDayOfMonthtoDayOfWeektoHourtoMinutetoSecondtoUnixTimestamptoUnixTimestamp64MillifromUnixTimestamp64MillitoStartOfIntervaltoStartOfYeartoStartOfISOYeartoStartOfQuartertoStartOfMonthtoLastDayOfMonthtoMondaytoStartOfWeektoLastDayOfWeektoStartOfDaytoStartOfHourtoStartOfMinutetoStartOfSecondtoStartOfFiveMinutestoStartOfTenMinutestoStartOfFifteenMinutestoTimetoISOYeartoISOWeektoWeektoYearWeekagedateDiffdateTruncdateAdddateSubtimeStampAddtimeStampSubnowNOWnowInBlockcurrent_timestampcurrent_datetodayyesterdaytimeSlottoYYYYMMtoYYYYMMDDtoYYYYMMDDhhmmssaddYearsaddMonthsaddWeeksaddDaysaddHoursaddMinutesaddSecondsaddQuarterssubtractYearssubtractMonthssubtractWeekssubtractDayssubtractHourssubtractMinutessubtractSecondssubtractQuarterstimeSlotsformatDateTimedateNamemonthNamefromUnixTimestamptoModifiedJulianDayfromModifiedJulianDaytoIntervalSecondtoIntervalMinutetoIntervalHourtoIntervalDaytoIntervalWeektoIntervalMonthtoIntervalQuartertoIntervalYear
Postgres-style date functions
date_partdate_truncmake_timestampdate_bindate_adddate_subtractdate_diffmake_intervalmake_timestamptzmake_dateto_charto_dateto_timestamp
Strings
left– operates on UTF-8 characters, not bytes (maps to ClickHouse'sleftUTF8)right– operates on UTF-8 characters, not bytes (maps to ClickHouse'srightUTF8)lengthUTF8leftPadorlpadrightPadorrpadleftPadUTF8rightPadUTF8lowerupperlowerUTF8upperUTF8isValidUTF8toValidUTF8formatreverseUTF8concatsubstringsubstringUTF8appendTrailingCharIfAbsentconvertCharsetbase58Encodebase58DecodetryBase58Decodebase64Encodebase64DecodetryBase64DecodeendsWithstartsWithtrimorbtrimtrimLeftorltrimtrimRightorrtrimencodeXMLComponentdecodeXMLComponentextractTextFromHTMLasciiconcatWithSeparatorrepeatinitcap
Searching in strings
positionpositionCaseInsensitivepositionUTF8positionCaseInsensitiveUTF8multiSearchAllPositionsmultiSearchAllPositionsCaseInsensitivemultiSearchAllPositionsCaseInsensitiveUTF8multiSearchAllPositionsUTF8multiSearchFirstPositionmultiSearchFirstPositionCaseInsensitivemultiSearchFirstPositionCaseInsensitiveUTF8multiSearchFirstPositionUTF8multiSearchFirstIndexmultiSearchFirstIndexCaseInsensitivemultiSearchFirstIndexCaseInsensitiveUTF8multiSearchFirstIndexUTF8multiSearchAnymultiSearchAnyCaseInsensitivemultiSearchAnyCaseInsensitiveUTF8multiSearchAnyUTF8matchmultiMatchAnymultiMatchAnyIndexmultiMatchAllIndicesmultiFuzzyMatchAnymultiFuzzyMatchAnyIndexmultiFuzzyMatchAllIndicesextractextractAllextractAllGroupsHorizontalextractAllGroupsVerticalextractGroupslikeilikenotLikenotILikelocatengramDistancengramDistanceCaseInsensitivengramDistanceCaseInsensitiveUTF8ngramDistanceUTF8ngramSearchngramSearchCaseInsensitivengramSearchCaseInsensitiveUTF8ngramSearchUTF8countSubstringscountSubstringsCaseInsensitivecountSubstringsCaseInsensitiveUTF8countMatchescountMatchesCaseInsensitivehasSubsequencehasSubsequenceCaseInsensitivehasSubsequenceCaseInsensitiveUTF8hasSubsequenceUTF8hasTokenhasTokenCaseInsensitivehasTokenCaseInsensitiveOrNullhasTokenOrNullhasAllTokenshasAnyTokensregexpExtract
Replacing in strings
replacereplaceAllreplaceOnereplaceRegexpAllreplaceRegexpOneregexpQuoteMetatranslatetranslateUTF8
Conditional
ifmultiIfthrowIf
Mathematical
epiexploglnexp2log2exp10log10sqrtcbrterferfclgammatgammasincostanasinacosatanpowpowerintExp2intExp10coshacoshsinhasinhatanhatan2hypotlog1psigndegreesradiansfactorialwidth_bucketrand
Rounding
floorceiltruncroundroundBankersroundToExp2roundDurationroundAgeroundDown
Maps
mapmapFromArraysmapAddmapSubtractmapPopulateSeriesmapContainsmapKeysmapValuesmapContainsKeyLikemapExtractKeyLikemapApplymapFiltermapUpdate
Splitting strings
splitByCharsplitByStringsplitByRegexpsplitByWhitespacesplitByNonAlphasplit_partalphaTokensextractAllGroupsngramstokens
Bit
bitAndbitOrbitXorbitNotbitShiftLeftbitShiftRightbitRotateLeftbitRotateRightbitSlicebitTestbitTestAllbitTestAnybitCountbitHammingDistance
Bitmap
bitmapBuildbitmapToArraybitmapSubsetInRangebitmapSubsetLimitsubBitmapbitmapContainsbitmapHasAnybitmapHasAllbitmapCardinalitybitmapMinbitmapMaxbitmapTransformbitmapAndbitmapOrbitmapXorbitmapAndnotbitmapAndCardinalitybitmapOrCardinalitybitmapXorCardinalitybitmapAndnotCardinality
URLs
protocoldomaindomainWithoutWWWtopLevelDomainfirstSignificantSubdomaincutToFirstSignificantSubdomaincutToFirstSignificantSubdomainWithWWWportpathpathFullqueryStringfragmentqueryStringAndFragmentextractURLParameterextractURLParametersextractURLParameterNamesURLHierarchyURLPathHierarchyencodeURLComponentdecodeURLComponenttryDecodeURLComponentencodeURLFormComponentdecodeURLFormComponentnetloccutWWWcutQueryStringcutFragmentcutQueryStringAndFragmentcutURLParameter
JSON
isValidJSONJSONHasJSONLengthJSONArrayLengthJSONTypeJSONExtractUIntJSONExtractIntJSONExtractFloatJSONExtractBoolJSONExtractStringJSONExtractJSONExtractKeysJSONExtractKeysAndValuesJSONExtractRawJSONExtractArrayRawJSONExtractKeysAndValuesRawJSON_VALUE
Geo
greatCircleDistancegeoDistancegreatCircleAnglepointInEllipsespointInPolygongeohashDecodegeohashEncodegeohashesInBoxh3IsValidh3GetResolutionh3GetBaseCellh3EdgeAngleh3EdgeLengthMh3EdgeLengthKmgeoToH3h3ToGeoh3ToGeoBoundaryh3kRingh3HexAreaM2h3HexAreaKm2h3IndexesAreNeighborsh3ToChildrenh3ToParenth3ToStringstringToH3h3IsResClassIIIh3IsPentagonh3GetFacesh3CellAreaM2h3CellAreaRads2h3ToCenterChildh3ExactEdgeLengthMh3ExactEdgeLengthKmh3ExactEdgeLengthRadsh3NumHexagonsh3PointDistMh3PointDistKmh3PointDistRadsh3GetRes0Indexesh3GetPentagonIndexesh3Lineh3Distanceh3HexRingh3GetUnidirectionalEdgeh3UnidirectionalEdgeIsValidh3GetOriginIndexFromUnidirectionalEdgeh3GetDestinationIndexFromUnidirectionalEdgeh3GetIndexesFromUnidirectionalEdgeh3GetUnidirectionalEdgesFromHexagonh3GetUnidirectionalEdgeBoundary
Nullable
isNullisNotNullcoalesceifNullnullIfassumeNotNulltoNullable
Tuples
tupletupleElementuntupletupleHammingDistancetupleToNameValuePairstuplePlustupleMinustupleMultiplytupleDividetupleNegatetupleMultiplyByNumbertupleDivideByNumberdotProduct
Time window
tumblehoptumbleStarttumbleEndhopStarthopEnd
Distance window
L1NormL2NormLinfNormLpNormL1DistanceL2DistanceLinfDistanceLpDistanceL1NormalizeL2NormalizeLinfNormalizeLpNormalizecosineDistance
Window functions
lagorlagInFrameleadorleadInFramerankdense_rankrow_numberfirst_valuelast_valuenth_value
Other
isFiniteisInfiniteifNotFiniteisNaNbartransformformatReadableDecimalSizeformatReadableSizeformatReadableQuantityformatReadableTimeDeltagreatestleastlanguageCodeToName