Handling NULL Values¶ Let us understand how to handle nulls. By default if we try to add or concatenate null to another column or expression or literal, it will return null. If we want to replace null with some default value, we can use coalesce. Replace commission_pct with 0 if it is null. coalesce returns …
Data Type Conversion¶ Let us understand how we can type cast to change the data type of extracted value to its original type. In [1]: %load_ext sql In [2]: %env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db In [3]: %%sql SELECT ’09’::int 1 rows affected. Out[3]: int4 9 In [4]: %%sql SELECT current_date AS current_date * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected. Out[4]: current_date 2022-03-14 …
Overview of Numeric Functions¶ Here are some of the numeric functions we might use quite often. abs – always return positive number round – rounds off to specified precision ceil, floor – always return integer. greatest sum, avg min, max random pow, sqrt Some of the functions highlighted are aggregate functions, eg: sum, avg, min, …
Date Manipulation Functions¶ Let us go through some of the important date manipulation functions. Getting Current Date and Timestamp Date Arithmetic using INTERVAL and – operator Getting beginning date or time using date_trunc Extracting information using to_char as well as calendar functions. Dealing with unix timestamp using from_unixtime, to_unix_timestamp Getting Current Date and Timestamp¶ Let …
String Manipulation Functions¶ We use string manipulation functions quite extensively. Here are some of the important functions which we typically use. Case Conversion – lower, upper, initcap Getting size of the column value – length Extracting Data – substr and split_part Trimming and Padding functions – trim, rtrim, ltrim, rpad and lpad Reversing strings – …
Overview of Pre-Defined Functions¶ 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 …
