Key arguments are coerced to text value arguments are converted as per to_json or to_jsonb. Jsonb_object_agg_unique_strict ( key 'any', value 'any' ) → jsonbĬollects all the key/value pairs into a JSON object. Json_object_agg_unique_strict ( key 'any', value 'any' ) → json SELECT json_arrayagg(v) FROM (VALUES(2),(1)) t(v) → If ORDER BY is specified, the elements will appear in the array in that order rather than in the input order. If ABSENT ON NULL is specified, any NULL values are omitted. Json_objectagg ( ] ])īehaves in the same way as json_array but as an aggregate function so it only takes one value_expression parameter. Values are converted to JSON as per to_json or to_jsonb. This is the SQL standard's equivalent to bool_and.Ĭollects all the input values, including nulls, into a JSON array. Returns true if any non-null input value is true, otherwise false.Ĭomputes the number of input rows in which the input value is not null. Returns true if all non-null input values are true, otherwise false.

Can be useful as a checksum for an unordered set of values. (The inputs must all have the same dimensionality, and cannot be empty or null.)Īvg ( double precision ) → double precisionĬomputes the average (arithmetic mean) of all the non-null input values.Ĭomputes the bitwise AND of all non-null input values.Ĭomputes the bitwise OR of all non-null input values.Ĭomputes the bitwise exclusive OR of all non-null input values. Returns an arbitrary value from the non-null input values.Ĭollects all the input values, including nulls, into an array.Ĭoncatenates all the input arrays into an array of one higher dimension. This following example shows how to use the PostgreSQL to_char() function to output a timestamp value in a specified format.Any_value ( anyelement ) → same as input type The PostgreSQL to_char() function returns a string converted from the specified timestamp, interval, or number according to the specified format. Modifierįill mode (suppress trailing zeroes and padding blanks) You can use the modifiers in the following table within numeric format strings.

Minus sign in specified position (if number 0) Patternĭigit position (can be discarded if it doesn’t matter)ĭigit position (will not be removed, even if it doesn’t matter) You can use the template patterns in the following table to construct numeric format strings. Translation mode (use localized day and month names based on lc_time) Modifierįill mode (suppress leading zeroes and padding blanks)įixed format global option (see usage notes) You can use the modifiers in the following table with date/time template patterns. Timezone offset from UTC ( to_char supported ) Lowercase time zone abbreviation (only to_char supported ) Uppercase time zone abbreviation ( to_char supported ) Month in lowercase Roman numerals (i to xii i is January) Month in uppercase Roman numerals (I to XII I is January) Julian calendar date (integer days since local midnight on NovemBC) ISO 8601 week number Week number of the year (01–53 the first Thursday of the year is in week 1)Ĭentury (2 digits) (21st century started on ) Week number of the year (1–53) (the first week starts on the first day of the year) Week of the month (1–5) (the first week starts on the first day of the month) ISO 8601 Day of the week, Monday ( 1) to Sunday ( 7) ISO 8601 week number day of the year (001–371 the 1st day of the year is the Monday of the first ISO week)ĭay of the week, Sunday ( 1) to Saturday ( 7) Last 2 digits of ISO 8601 week number yearĪll-caps month name (blank padded to 9 characters)Īll lowercase month name (blank padded to 9 characters)Ībbreviated uppercase month name (3 characters in English, localized lengths vary)Ībbreviated lowercase month name (3 characters in English, localized lengths vary)ĭate name in all caps (blank padded to 9 characters)Īll lowercase date name (9 characters padded with blanks)Ībbreviated uppercase date name (3 English characters, localized lengths vary)Ībbreviated lowercase date name (3 English characters, localized lengths vary) Last 3 digits of ISO 8601 week number year ISO 8601 week number year (4 or more digits) Hundredths of a millisecond (00000–99999) You can use the template patterns in the following table to construct date/time format templates. Placeholders define the format of the output string, and modifiers can change the behavior of the template. Templates consist of some placeholders and modifiers. The format used for converting this value. It can be used for values of types: TIMESTAMP, TIMESTAMPTZ, INTERVAL, INTEGER, BIGINT, NUMERIC, REAL, DOUBLE.

To_char ( value, format TEXT ) -> TEXT Parameters value

Postgresql string function