Common PostgreSQL Functions

Common PostgreSQL Functions
| String concatenation
Syntax
String | string
Example
'Post' | 'gresql' -- PostgreSQL is returned.

Length () String length
Syntax
Length (string)
Example
Length ('doo') -- returns 4

LIKE pattern matching
Syntax
String LIKE pattern
Example
'Abc' LIKE 'abc' -- returns true.
'Abc' LIKE 'a % '-- Return true

To_char () converts the timestamp into a string
Syntax
To_char (timestamp, text)
Example
To_char (create_date, 'yyyy/MM/dd ')
To_char (create_date, 'hh12: MI: ss ')

To_date () converts a string to a date
Syntax
To_date (text, text)
Example
To_date ('05 Jan 2015 ', 'dd Mon yyyy ')

To_timestamp () converts a string to a timestamp.
Syntax
To_timestamp (text, text)
Example
To_timestamp ('05 Jan 2015 ', 'dd Mon yyyy ')

CASE conditional expression, Similar to if/else in other programming languages
Syntax 1
Case when condition THEN result [WHEN...] [ELSE result] END
Example 1
Case when gender = 'male' then' 'else' 'end
Syntax 2 (simplified form)
CASE expression WHEN value THEN result [WHEN...] [ELSE result] END
Example 2
CASE gender WHEN 'male' then' program 'else' 'end

COALESCE () returns the first non-NULL ParameterIf all parameters are NULL, NULL is returned.
Syntax
COALESCE (value [,...])
Example
COALESCE (actual_qty, 0) as actual_qty

NULLIF () If value1 is equal to value2, NULL is returned; otherwise, value1 is returned.
Syntax
NULLIF (value1, value2)
Example
NULLIF (value, '(none )')

Ascii () converts the first character of a parameter to an ASCII code.
Syntax
Ascii (string)
Example
Ascii ('x') -- returns 120

Chr () converts ASCII code into characters
Syntax
Chr (int)
Example
Chr (65) -- return

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.