Overview of Pre-Defined Functions

Topic
Materials

Like any RDBMS, Postgres provides robust set of pre-defined functions to come up with solutions quickly as per the business requirements. There are many functions, but we will see the most common ones here.

  • Following are the categories of functions that are more commonly used.

    • String Manipulation

    • Date Manipulation

    • Numeric Functions

    • Type Conversion Functions

    • CASE and WHEN

    • and more

  • One can go to the official documentation from Postgres website.

%load_ext sql

Copy to clipboard

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

%sql SELECT * FROM information_schema.routines LIMIT 10

Copy to clipboard

10 rows affected.

Copy to clipboard

specific_catalog specific_schema specific_name routine_catalog routine_schema routine_name routine_type module_catalog module_schema module_name udt_catalog udt_schema udt_name data_type character_maximum_length character_octet_length character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision type_udt_catalog type_udt_schema type_udt_name scope_catalog scope_schema scope_name maximum_cardinality dtd_identifier routine_body routine_definition external_name external_language parameter_style is_deterministic sql_data_access is_null_call sql_path schema_level_routine max_dynamic_result_sets is_user_defined_cast is_implicitly_invocable security_type to_sql_specific_catalog to_sql_specific_schema to_sql_specific_name as_locator created last_altered new_savepoint_level is_udt_dependent result_cast_from_data_type result_cast_as_locator result_cast_char_max_length result_cast_char_octet_length result_cast_char_set_catalog result_cast_char_set_schema result_cast_char_set_name result_cast_collation_catalog result_cast_collation_schema result_cast_collation_name result_cast_numeric_precision result_cast_numeric_precision_radix result_cast_numeric_scale result_cast_datetime_precision result_cast_interval_type result_cast_interval_precision result_cast_type_udt_catalog result_cast_type_udt_schema result_cast_type_udt_name result_cast_scope_catalog result_cast_scope_schema result_cast_scope_name result_cast_maximum_cardinality result_cast_dtd_identifier
itversity_retail_db pg_catalog boolin_1242 itversity_retail_db pg_catalog boolin FUNCTION None None None None None None boolean None None None None None None None None None None None None None None itversity_retail_db pg_catalog bool None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog boolout_1243 itversity_retail_db pg_catalog boolout FUNCTION None None None None None None cstring None None None None None None None None None None None None None None itversity_retail_db pg_catalog cstring None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog byteain_1244 itversity_retail_db pg_catalog byteain FUNCTION None None None None None None bytea None None None None None None None None None None None None None None itversity_retail_db pg_catalog bytea None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog byteaout_31 itversity_retail_db pg_catalog byteaout FUNCTION None None None None None None cstring None None None None None None None None None None None None None None itversity_retail_db pg_catalog cstring None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog charin_1245 itversity_retail_db pg_catalog charin FUNCTION None None None None None None "char" None None None None None None None None None None None None None None itversity_retail_db pg_catalog char None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog charout_33 itversity_retail_db pg_catalog charout FUNCTION None None None None None None cstring None None None None None None None None None None None None None None itversity_retail_db pg_catalog cstring None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog namein_34 itversity_retail_db pg_catalog namein FUNCTION None None None None None None name None None None None None None None None None None None None None None itversity_retail_db pg_catalog name None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog nameout_35 itversity_retail_db pg_catalog nameout FUNCTION None None None None None None cstring None None None None None None None None None None None None None None itversity_retail_db pg_catalog cstring None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog int2in_38 itversity_retail_db pg_catalog int2in FUNCTION None None None None None None smallint None None None None None None None None None None None None None None itversity_retail_db pg_catalog int2 None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog int2out_39 itversity_retail_db pg_catalog int2out FUNCTION None None None None None None cstring None None None None None None None None None None None None None None itversity_retail_db pg_catalog cstring None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
%%sql 

SELECT * FROM information_schema.routines 
WHERE routine_name ~ 'str'

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
36 rows affected.

Copy to clipboard

specific_catalog specific_schema specific_name routine_catalog routine_schema routine_name routine_type module_catalog module_schema module_name udt_catalog udt_schema udt_name data_type character_maximum_length character_octet_length character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision type_udt_catalog type_udt_schema type_udt_name scope_catalog scope_schema scope_name maximum_cardinality dtd_identifier routine_body routine_definition external_name external_language parameter_style is_deterministic sql_data_access is_null_call sql_path schema_level_routine max_dynamic_result_sets is_user_defined_cast is_implicitly_invocable security_type to_sql_specific_catalog to_sql_specific_schema to_sql_specific_name as_locator created last_altered new_savepoint_level is_udt_dependent result_cast_from_data_type result_cast_as_locator result_cast_char_max_length result_cast_char_octet_length result_cast_char_set_catalog result_cast_char_set_schema result_cast_char_set_name result_cast_collation_catalog result_cast_collation_schema result_cast_collation_name result_cast_numeric_precision result_cast_numeric_precision_radix result_cast_numeric_scale result_cast_datetime_precision result_cast_interval_type result_cast_interval_precision result_cast_type_udt_catalog result_cast_type_udt_schema result_cast_type_udt_name result_cast_scope_catalog result_cast_scope_schema result_cast_scope_name result_cast_maximum_cardinality result_cast_dtd_identifier
itversity_retail_db pg_catalog btvarstrequalimage_5050 itversity_retail_db pg_catalog btvarstrequalimage FUNCTION None None None None None None boolean None None None None None None None None None None None None None None itversity_retail_db pg_catalog bool None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog string_to_array_394 itversity_retail_db pg_catalog string_to_array FUNCTION None None None None None None ARRAY None None None None None None None None None None None None None None itversity_retail_db pg_catalog _text None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES NO None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog array_to_string_395 itversity_retail_db pg_catalog array_to_string FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL NO MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog string_to_array_376 itversity_retail_db pg_catalog string_to_array FUNCTION None None None None None None ARRAY None None None None None None None None None None None None None None itversity_retail_db pg_catalog _text None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES NO None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog array_to_string_384 itversity_retail_db pg_catalog array_to_string FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL NO MODIFIES NO None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog strpos_868 itversity_retail_db pg_catalog strpos FUNCTION None None None None None None integer None None None None None None None None None None None None None None itversity_retail_db pg_catalog int4 None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substr_877 itversity_retail_db pg_catalog substr FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substr_883 itversity_retail_db pg_catalog substr FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substring_936 itversity_retail_db pg_catalog substring FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substring_937 itversity_retail_db pg_catalog substring FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog pg_get_partition_constraintdef_3408 itversity_retail_db pg_catalog pg_get_partition_constraintdef FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL NO MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog pg_get_constraintdef_1387 itversity_retail_db pg_catalog pg_get_constraintdef FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL NO MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog RI_FKey_restrict_del_1648 itversity_retail_db pg_catalog RI_FKey_restrict_del FUNCTION None None None None None None trigger None None None None None None None None None None None None None None itversity_retail_db pg_catalog trigger None None None None 0 EXTERNAL None None INTERNAL GENERAL NO MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog RI_FKey_restrict_upd_1649 itversity_retail_db pg_catalog RI_FKey_restrict_upd FUNCTION None None None None None None trigger None None None None None None None None None None None None None None itversity_retail_db pg_catalog trigger None None None None 0 EXTERNAL None None INTERNAL GENERAL NO MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substring_1680 itversity_retail_db pg_catalog substring FUNCTION None None None None None None bit None None None None None None None None None None None None None None itversity_retail_db pg_catalog bit None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substring_1699 itversity_retail_db pg_catalog substring FUNCTION None None None None None None bit None None None None None None None None None None None None None None itversity_retail_db pg_catalog bit None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog string_agg_transfn_3535 itversity_retail_db pg_catalog string_agg_transfn FUNCTION None None None None None None internal None None None None None None None None None None None None None None itversity_retail_db pg_catalog internal None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES NO None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog string_agg_finalfn_3536 itversity_retail_db pg_catalog string_agg_finalfn FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES NO None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog string_agg_3538 itversity_retail_db pg_catalog string_agg None None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES NO None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog bytea_string_agg_transfn_3543 itversity_retail_db pg_catalog bytea_string_agg_transfn FUNCTION None None None None None None internal None None None None None None None None None None None None None None itversity_retail_db pg_catalog internal None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES NO None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog bytea_string_agg_finalfn_3544 itversity_retail_db pg_catalog bytea_string_agg_finalfn FUNCTION None None None None None None bytea None None None None None None None None None None None None None None itversity_retail_db pg_catalog bytea None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES NO None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog string_agg_3545 itversity_retail_db pg_catalog string_agg None None None None None None None bytea None None None None None None None None None None None None None None itversity_retail_db pg_catalog bytea None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES NO None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substring_2012 itversity_retail_db pg_catalog substring FUNCTION None None None None None None bytea None None None None None None None None None None None None None None itversity_retail_db pg_catalog bytea None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substring_2013 itversity_retail_db pg_catalog substring FUNCTION None None None None None None bytea None None None None None None None None None None None None None None itversity_retail_db pg_catalog bytea None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substr_2085 itversity_retail_db pg_catalog substr FUNCTION None None None None None None bytea None None None None None None None None None None None None None None itversity_retail_db pg_catalog bytea None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substr_2086 itversity_retail_db pg_catalog substr FUNCTION None None None None None None bytea None None None None None None None None None None None None None None itversity_retail_db pg_catalog bytea None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substring_2073 itversity_retail_db pg_catalog substring FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog substring_2074 itversity_retail_db pg_catalog substring FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 SQL None None SQL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog cstring_in_2292 itversity_retail_db pg_catalog cstring_in FUNCTION None None None None None None cstring None None None None None None None None None None None None None None itversity_retail_db pg_catalog cstring None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog cstring_out_2293 itversity_retail_db pg_catalog cstring_out FUNCTION None None None None None None cstring None None None None None None None None None None None None None None itversity_retail_db pg_catalog cstring None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog cstring_recv_2500 itversity_retail_db pg_catalog cstring_recv FUNCTION None None None None None None cstring None None None None None None None None None None None None None None itversity_retail_db pg_catalog cstring None None None None 0 EXTERNAL None None INTERNAL GENERAL NO MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog cstring_send_2501 itversity_retail_db pg_catalog cstring_send FUNCTION None None None None None None bytea None None None None None None None None None None None None None None itversity_retail_db pg_catalog bytea None None None None 0 EXTERNAL None None INTERNAL GENERAL NO MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog pg_get_constraintdef_2508 itversity_retail_db pg_catalog pg_get_constraintdef FUNCTION None None None None None None text None None None None None None None None None None None None None None itversity_retail_db pg_catalog text None None None None 0 EXTERNAL None None INTERNAL GENERAL NO MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog json_strip_nulls_3261 itversity_retail_db pg_catalog json_strip_nulls FUNCTION None None None None None None json None None None None None None None None None None None None None None itversity_retail_db pg_catalog json None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog strip_3623 itversity_retail_db pg_catalog strip FUNCTION None None None None None None tsvector None None None None None None None None None None None None None None itversity_retail_db pg_catalog tsvector None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
itversity_retail_db pg_catalog jsonb_strip_nulls_3262 itversity_retail_db pg_catalog jsonb_strip_nulls FUNCTION None None None None None None jsonb None None None None None None None None None None None None None None itversity_retail_db pg_catalog jsonb None None None None 0 EXTERNAL None None INTERNAL GENERAL YES MODIFIES YES None YES 0 None None INVOKER None None None NO None None None NO None None None None None None None None None None None None None None None None None None None None None None None None
%%sql

SELECT substring('Thomas' from 2 for 3)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

substring
hom
%%sql

SELECT substring('Thomas', 2, 3)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

substring
hom