Identifiers
String concatenate operator [ string || string ]
select 'Gordon' || ' ' || 'Moore' As fullName;
fullname
--------------
Gordon Moore
(1 row)// Concatenates two or more strings using “||”. (SQL)
This operator can be applied on table columns as well. e.g. “select first_name || ‘ ‘ || last_name As fullName from person”
Square & cube root operator (|/ & ||/)
select |/25 As sqrt;
sqrt
------
5
(1 row)// Square root operator. (SQL)
select ||/125 As cubert;
cubert
------
5
(1 row)// Cube root operator. (SQL)
Factorial operator (!)
select 5! As factorial;
factorial
-----------
120
(1 row)// Factorial operator. (SQL)
Binary complement operator (~)
select ~60 As compl;
compl
----------
-61
(1 row)// Binary 2’s complement. This operator has flipping effect on bits. (SQL)
Assume if A = 60, now in binary format they will be as follows − A = 0011 1100 ~A = 1100 0011 (flipping bits. change 0 to 1 & 1 to 0)
String lower & upper function [ lower(string), upper(string) ]
select lower('Rohit Kumawat') As lowerCase, upper('Rohit Kumawat') As upperCase;
lowercase | uppercase
---------------+---------------
rohit kumawat | ROHIT KUMAWAT
(1 row)// Postgres lower & upper function (SQL)
Number of characters in string [ char_length(string) ]
select char_length('Arizona') as num_chars;
num_chars
-----------
7
(1 row)// Number of characters in string (SQL)
Location of specified substring [ position(substring in string) ]
select position('pan' in 'japan') As pos;
pos
-----
3
(1 row)// Location of specified substring (SQL)
Extract substring [ substring(string from [int] for [int] ]
select substring('postgres' from 3 for 3) As sub_string;
sub_string
------------
stg
(1 row)// Extract substring ‘postgres’ starting from 3rd character upto 3 characters length (SQL)
Insert newline in SQL output
postgres=# select 'line 1'||E'\n'||'line 2' As newline;
newline
---------
line 1 +
line 2
(1 row)// Insert new line using E’\n’ (SQL)
Another option is to use the chr() function. (E is used for the Escape String constant). Here’s a few other escape sequences: \b backspace \f form feed \n newline \r carriage return \t tab
Quote identifier
Update "my table" set "a&b" = 0;
// Using double quotes as delimited identifier
This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. Also double quotes are used for escaping reserved keywords in postgres
Dollar-quoted string constant
select $$Maria’s dogs$$ As col;
col
--------------------
Maria’s dogs
(1 row)// Use dollar quotes string constant instead of double quotes for strings (SQL)
If a string contains many single quotes or backslashes then Postgres has an alternative called “dollar quoting”.