Basic PostgreSQL Query: Introduction, Explanation, and 50 Examples
Table of Contents
1. CREATE TABLE query in postgresql
CREATE TABLE is a keyword that will create a new, initially empty table in the database. The table will be owned by the user who has issued this command.
Postgres=# create table dummy_table(name varchar(20),address text,age int);
CREATE TABLE
2. INSERT query in postgresql
The INSERT command is used to insert data into a table:
Postgres=# insert into dummy_table values(‘XYZ’,’location-A’,25);
INSERT 0 1
Postgres=# insert into dummy_table values(‘ABC’,’location-B’,35);
INSERT 0 1
Postgres=# insert into dummy_table values(‘DEF’,’location-C’,40);
INSERT 0 1
Postgres=# insert into dummy_table values(‘PQR’,’location-D’,54);
INSERT 0 1
3. SELECT query without WHERE condition in postgresql
The SELECT command (when used without the optional WHERE condition) is used to fetch all data from a database table:
Postgres=# select * from dummy_table;
Name | address | age
———+————–+ —–
XYZ | location-A | 25
ABC | location-B | 35
DEF | location-C | 40
PQR | location-D | 54
(4 rows)
4. UPDATE query in postgresql
UPDATE is used to make updates to the data or row(s) of a database table. In the example below we use UPDATE to change the age of a person whose name is ‘PQR’:
Postgres=# update dummy_table set age=50 where name=’PQR’;
UPDATE 1
Postgres=# select * from dummy_table;
Name | address | age
——–+————–+——-
XYZ | location-A | 25
ABC | location-B | 35
DEF | location-C | 40
PQR | location-D | 50
(4 rows)
Next, we’ll use the UPDATE command to change the name and age of a person whose address is ‘location-D’:
Postgres=# update dummy_table set name=’GHI’,age=54 where address=’location-D’;
UPDATE 1
Postgres=# select * from dummy_table;
Name | address | age
——+————+—–
XYZ | location-A | 25
ABC | location-B | 35
DEF | location-C | 40
GHI | location-D | 54
(4 rows)
Postgres=#
If we want to modify all the values in the address and age columns in dummy_table, then we do not need to use the WHERE clause. The UPDATE query would look like this:
Postgres=# update dummy_table set age=54,address=’location-X’;
UPDATE 4
Postgres=# select * from dummy_table ;
Name | address | age
——+————+——–
XYZ | location-X | 54
ABC | location-X | 54
DEF | location-X | 54
GHI | location-X | 54
(4 rows)
Postgres=#
A RETURNING clause returns the updated rows. This is optional in UPDATE:
Postgres=# update dummy_table set age=30 where name=’XYZ’ returning age as age_no;
Age_no
———
30
(1 row)
UPDATE 1
It is always recommended to perform such operations under transaction blocks (i.e., BEGIN…COMMIT/ROLLBACK;), so we have the option to roll back the operation.
5. DELETE query in postgresql
The DELETE command is used to delete row(s). It can be used with or without the optional WHERE condition, but take note: if the WHERE condition is missing, the command will delete all rows, leaving you with an empty table.
In this example, we are deleting one row whose age column has the value 65:
Postgres=# delete from dummy_table where age=65;
DELETE 1
Postgres=#
6. Comparison Operators in postgresql queries
In postgresql, with the help of comparison operators we can find results where the value in a column is not equal to the specified condition or value.
Less than or equal to query:
Postgres=# select * from dummy_table where age <=50;
Name | address | age
——+————+—–
XYZ | location-A | 25
ABC | location-B | 35
DEF | location-C | 40
PQR | location-D | 50
(4 rows)
Greater than or equal to query:
Postgres=# select * from dummy_table where age>=50;
Name | address | age
——+————+—–
PQR | location-D | 50
(1 row)
Not equal to query:
Postgres=# select * from dummy_table where age<>50;
Name | address | age
——+————+—–
XYZ | location-A | 25
ABC | location-B | 35
DEF | location-C | 40
(3 rows)
Equal to query:
Postgres=# select * from dummy_table where age=50;
Name | address | age
——+————+—–
PQR | location-D | 50
(1 row)
7. SELECT DISTINCT query in postgresql
The SELECT DISTINCT statement is used to return only distinct values from the table. It removes any duplicate values.
SELECT query without DISTINCT clause
Postgres=# select age from dummy_table order by 1;
Age
—–
1
1
2
2
3
(5 rows)
SELECT query with DISTINCT clause
Postgres=# select distinct age from dummy_table order by 1;
Age
—–
1
2
3
(3 rows)
8. TRUNCATE query in postgresql
The TRUNCATE command is used to empty a table:
Postgres=# truncate table dummy_table;
TRUNCATE TABLE
9. DROP TABLE query in postgresql
This DROP TABLE command is used to drop a table from the database:
Postgresql=# drop table if exists dummy;
NOTICE: table “dummy” does not exist, skipping
DROP TABLE
This command has removed the full table, including any associated data, indexes, rules, triggers, and constraints for that table.
10. CREATE VIEW query in postgresql
The CREATE VIEW command is used to generate views. Views are pseudo-tables, which are used to present a full table, subset, or select columns from the underlying table:
Postgres=# create or replace view vi as select * from dummy_table where age is NULL;
CREATE VIEW
11. Create a table in Postgresql using the SELECT statement
Using the syntax in the example below, we can create a table using a SELECT statement:
Postgres=# select ‘My name is X’ as col1 , 10 as col2, ‘Address is -XYZ location’ as col3 into new_table;
SELECT 1
Postgres=# select * from new_table ;
Col1 | col2 | col3
—————+——+————————–
My name is X | 10 | Address is -XYZ location
(1 row)
12. Query timeout in postgresql
We can command a query to timeout after a certain period with the help of GUC parameters (short for grand unified configuration) like statement_timeout, which aborts any statement that takes more than the specified number of milliseconds:
Postgresql=# set statement_timeout=10;
SET
Postgresql=# select pg_sleep(20);
ERROR: canceling statement due to statement timeout
13. Using CREATE SEQUENCE with the INSERT query in postgresql
The CREATE SEQUENCE command is a sequential number generator. Once the sequence is created, we can use the sequence’s nextval and currval functions to insert values into a table:
Postgres=# create sequence seq;
CREATE SEQUENCE
Postgres=# create table tab(n int);
CREATE TABLE
Postgres=# insert into tab values (nextval(‘seq’));
INSERT 0 1
Postgres=# insert into tab values (currval(‘seq’));
INSERT 0 1
Postgres=# insert into tab values (nextval(‘seq’));
INSERT 0 1
Postgres=# select * from tab;
N
—
1
1
2
(3 rows)
14. Importing BLOB data types into postgresql
Postgresql doesn’t directly support blobs (binary large objects), but we can work with them using the following methods:
Let’s assume you have an image (in png format) downloaded in the /home/edb/ folder:
[edb@localhost]$ ls /home/edb/mypic.png
/home/edb/mypic.png
We want to store this image in the postgresql database.
Go to the bin folder of your postgresql installation and connect to the psql terminal:
Postgres=# Create table testing(n int,n1 oid);
CREATE TABLE
Postgres=# insert into testing values (1,lo_import(‘/home/edb/mypic.png’));
INSERT 0 1
The lo_import() function loads the named file into pg_largeobject and returns an OID (object identifier) value that will refer to the large object. Selecting the testing table will show just the OID and not the bits that have made up this photo.
15. ILIKE query in postgresql
The ILIKE operator is a matching function similar to the LIKE operator, but with the advantage that it matches valus case-insensitively.
Postgres=# select * from ted;
N
—–
TAR
Tar
Tar
Tar
(4 rows)
Using ILIKE in a WHERE condition
Postgres=# select * from ted where n ilike ‘TAR%’;
N
—–
TAR
Tar
Tar
Tar
(4 rows)
16. Hierarchical queries in postgresql
Hierarchical queries are ones where the results have a structured or parent-child relationship and are displayed in a tree structure. To see how hierarchical queries work, create a dummy table:
Create table test_table(
Emp_no int,
Ename char(5),
Job char(9),
Manager_no int
);
Insert data into ‘test_table’:
Insert into test_table values(10,’A1′,’CEO’,null);
Insert into test_table values(11, ‘B1’, ‘VP’, 10);
Insert into test_table values(12, ‘B2’, ‘VP’, 10);
Insert into test_table values(13, ‘B3’, ‘VP’, 10);
Insert into test_table values(14, ‘C1’, ‘DIRECTOR’, 13);
Insert into test_table values(15, ‘C2’, ‘DIRECTOR’, 13);
Insert into test_table values(16, ‘D1’, ‘MANAGER’, 15);
Insert into test_table values(17 ,’E1′, ‘ENGINEER’, 11);
Insert into test_table values(18, ‘E2’, ‘ENGINEER’, 11);
We can perform hierarchical queries on this table using the methods below.
17. Length function in postgresql
The length function returns the number of characters or number of bytes in a specified string variable.
Basic SELECT query
Postgres=# select name,age from dummy_table;
Name | age
——+—–
XYZ | 25
ABC | 35
DEF | 40
PQR | 54
PQR |
(5 rows)
Query with length function for column name and age
Postgres=# select length(name),length(age) from dummy_table;
Length | length
——–+——–
3 | 2
3 | 2
3 | 2
3 | 2
3 |
(5 rows)
18. When a query has no destination for result data in postgresql
Say that while selecting a given function, we receive the error message below:
Postgresql=# create or replace function f(n int)
Returns int
As
$$
Begin
Select now();
Return 1;
End;
$$ language ‘plpgsql’;
CREATE FUNCTION
Postgres=# select f(9);
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
To avoid such errors, we can either use PERFORM or declare a variable and use it in a SELECT INTO statement:
Using PERFORM
Postgres=# create or replace function f(n int)
Returns int
As
$$
Begin
Perform
Now();
Return 1;
End;
$$ language ‘plpgsql’;
CREATE FUNCTION
Postgresql=# select f(9);
F
—
1
(1 row)
Declaring a variable and using it in a SELECT INTO statement
Postgres=# create or replace function f(n int)
Returns int
As
$$
Declare
A date;
Begin
Select now() into a;
Raise notice ‘%s’,a;
Return 1;
End;
$$ language ‘plpgsql’;
CREATE FUNCTION
Postgresql=# select f(9);
NOTICE: 24-SEP-20 13:15:46.23388s
F
—
1
(1 row)
19. Exporting query result to a text file in postgresql
With the help of the COPY command, we can export data from a table to an outside text file as well as import data from a text file into a table.
Exporting data from a table to a text file
Postgres=# copy dummy_table to ‘/tmp/abc.txt’;
COPY 5
Postgres=# \! Cat /tmp/abc.txt
XYZ location-A 25
ABC location-B 35
DEF location-C 40
PQR location-D 50
CXC 1 50
Importing data from a text file into a table
Postgres=# copy dummy_table from ‘/tmp/abc.txt’;
COPY 5
1. With the help of common table expressions (CTE):
2.
3. Postgres=#WITH RECURSIVE cte AS ( SELECT emp_no, ename, manager_no, 1 AS level FROM test_table where manager_no is null UNION ALL SELECT e.emp_no, e.ename, e.manager_no, c.level + 1 FROM cte c JOIN test_table e ON e.manager_no = c.emp_no ) SELECT * FROM cte;
4.
5. Emp_no | ename | manager_no | level
6. ———–+———-+——————+——-
7. 10 | A1 | | 1
8. 11 | B1 | 10 | 2
9. 12 | B2 | 10 | 2
10. 13 | B3 | 10 | 2
11. 14 | C1 | 13 | 3
12. 17 | E1 | 11 | 3
13. 18 | E2 | 11 | 3
14. 15 | C2 | 13 | 3
15. 16 | D1 | 15 | 4
16. (9 rows)
17.
18. Postgres=#
19.
20.Using the tablefunc extension:
The tablefunc extension is a contrib module that resides in the contrib/ folder in postgresql sources.
First, create the tablefunc extension:
Postgres=# CREATE EXTENSION tablefunc;
CREATE EXTENSION
Postgres=#
Then, use tablefunc’s connectby function to display results hierarchically:
Postgres=# SELECT * FROM connectby(‘dummy_table’, ’emp_no’, ‘manager_no’, ’10’, 0, ‘->’) AS t(emp_no int, manager_no int, level int, ord text) order by emp_no;
Emp_no | manager_no | level | ord
——–+————+——-+—————-
10 | | 0 | 10
11 | 10 | 1 | 10->11
12 | 10 | 1 | 10->12
13 | 10 | 1 | 10->13
14 | 13 | 2 | 10->13->14
15 | 13 | 2 | 10->13->15
16 | 15 | 3 | 10->13->15->16
17 | 11 | 2 | 10->11->17
18 | 11 | 2 | 10->11->18
(9 rows)
Postgres=#
20. Listing databases query in postgresql
The following query can be used to show all the databases created:
Postgres=# select oid,datname from pg_database;
Oid | datname
———–+———–
13743 | postgres
1 | template1
13742 | template0
(3 rows)
We can also list out all the database names using the \l command at the psql prompt.
21. Checking query execution time in postgresql
We can check the time needed for a query to execute by enabling \timing at the psql prompt:
Postgres=# \timing
Timing is on.
The SELECT query will now show the execution time:
Postgres=# select * from dummy_table;
Name | address | age
——+————+——–
XYZ | location-A | 25
ABC | location-B | 35
DEF | location-C | 40
PQR | location-D | 50
CXC | 1 | 50
(5 rows)
Time: 0.440 ms
Postgres=#
22. Dynamic SQL query in postgresql
Dynamic SQL is used to reduce repetitive tasks when it comes to querying.
Dynamic SQL queries are not cached in memory.
Postgres=# do
Postgres-# $$
Postgres$# begin
Postgres$# execute ‘select * from dummy_table’;
Postgres$# end;
Postgres$# $$;
DO
23. COUNT query in postgresql
The COUNT query returns the number of rows in a table. If we use (*) this will include null values; otherwise null values will be excluded.
Postgres=# select count(*) from dummy_table;
Count
——-
5
(1 row)
Postgres=# select count(avg) from dummy_table;
Count
——-
4
(1 row)
24. LIMIT and OFFSET query in postgresql
The LIMIT clause is used to limit the data amount returned by the SELECT statement. The query below will display only 1 row:
Postgres=# select * from dummy_table limit 1;
Name | address | age
——+————+—–
XYZ | location-A | 25
(1 row)
OFFSET is used when we want to skip a particular number of rows:
Postgres=# select * from dummy_table offset 4;
Name | address | age
——+———+—–
Cxc | 1 | 50
(1 row)
25. IF … ELSE expression in postgresql
We can use conditional statements like IF … ELSE in an anonymous block. The example below checks if the values of variables abc and xyz are matching and prints the result— i.e., 150:
Postgres=# Do
$$
Declare
Abc int;
Xyz int;
Begin
Abc:=100;
Xyz:=abc;
If abc=xyz then
Xyz=150;
Raise notice ‘%’,xyz;
Else
End if;
End;
$$
;
NOTICE: 150
DO
26. UPDATE with JOIN query in postgresql
We can use UPDATE with a JOIN and WHERE clause when we want to update the values from one table (table X) based on values from another table (table Y):
Postgres=# create table X(n int, n1 char(10));
CREATE TABLE
Postgres=# insert into X values (1,’abc’);
INSERT 0 1
Postgres=# insert into X values (2,’xyz’);
INSERT 0 1
Postgres=# insert into X values (3,’pqr’);
INSERT 0 1
Postgres=# create table Y(n int, n1 char(10));
CREATE TABLE
Postgres=# insert into Y values (1,”);
INSERT 0 1
Postgres=# insert into Y values (2,”);
INSERT 0 1
Postgres=# insert into Y values (5,’axyz’);
INSERT 0 1
Postgres=# update Y set n1=X.n1 from X where X.n=Y.n;
UPDATE 2
Postgres=# select * from Y;
N | n1
—+————
5 | axyz
1 | abc
2 | xyz
(3 rows)
Postgres=#
27. INNER JOIN query in postgresql
The INNER JOIN command will find rows from two (or more) tables where the specified columns data in the tables match:
Postgres=# select * from x inner join y on x.n1 = y.n1;
N | n1 | n | n1
—+————+—+————
1 | abc | 1 | abc
2 | xyz | 2 | xyz
(2 rows)
Postgres=#
28. CASE expression in postgresql
The CASE expression is a generic conditional expression, similar to the IF … ELSE statement.
Postgres=# SELECT age,
CASE age WHEN 25 THEN ‘one’
WHEN 50 THEN ‘two’
ELSE ‘other’
END
FROM dummy_table;
Age | case
—–+——-
25 | one
35 | other
40 | other
50 | two
50 | two
(5 rows)
29. Postgresql recursive query
Recursive queries are used to deal with hierarchical queries or tree-structured data. The structure of a WITH RECURSIVE query is always: a) Non-recursive term
b) UNION (or UNION ALL), then a recursive term
Where the recursive term includes a reference to the query’s output.
CREATE TABLE emp_test (
Id int,
Ename varchar(255),
Emanager int
);
INSERT INTO emp_test VALUES (1, ‘abc’, null);
INSERT INTO emp_test VALUES (2, ‘xyz’, 1);
INSERT INTO emp_test VALUES (3, ‘def’, 2);
INSERT INTO emp_test VALUES (4, ‘cde’, 1);
INSERT INTO emp_test VALUES (5, ‘qrs’, 2);
INSERT INTO emp_test VALUES (9, ‘iop’, 3);
INSERT INTO emp_test VALUES (10, ‘klm’, 4);
The recursive query below will give all the reports in a certain order:
Postgres=#WITH RECURSIVE emp_testnew AS (
SELECT id, ename, emanager
FROM emp_test
WHERE id = 2
UNION ALL
SELECT e.id, e.ename, e.emanager
FROM emp_test e
INNER JOIN emp_testnew e1 ON e1.id = e.emanager
)
SELECT *
FROM emp_testnew;
Id | ename | emanager
—-+——-+———-
2 | xyz | 1
3 | def | 2
5 | qrs | 2
9 | iop | 3
(4 rows)
Postgres=#
30. Postgresql log queries
Using the pg_stat_statements module, we can track execution statistics for all SQL statements. To do this, we need to create an extension and add in shared_preload_libraries inside the postgresql.conf file:
Postgres=# create extension pg_stat_statements;
CREATE EXTENSION
Postgres=# show shared_preload_libraries ;
Shared_preload_libraries
————————————————-
$libdir/pg_stat_statements
(1 row)
Postgres=# select query from pg_stat_statements where query like ‘create table%’;
-[ RECORD 1 ]——————-
Query | create table test(n int)
We can also configure postgresql to generate log output by enabling these parameters in the postgresql.conf file:
Logging_collector = on
Log_directory = ‘log‘
Log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log‘
Log_destination = ‘stderr’
Log file will be created under the pg_log directory which resides under the data folder.
[centos@tushar-ldap-docker bin]$ ls data/log
Postgresql-2020-09-17_150932.log postgresql-2020-09-19_000000.log
[centos@tushar-ldap-docker bin]$
Queries will be recorded in these files.
31. Using a variable in a postgresql query
We can declare a variable in postgresql at the psql prompt:
Postgres=# \set cond 50
Using a variable in a WHERE condition
Postgres=# select * from dummy_table where age=:cond;
Name | address | age
——+————+—–
PQR | location-D | 50
(1 row)
OR
Postgres=# \set cond 50
Postgres=# select :cond+100 ;
?Column?
———-
150
(1 row)
32. Date query in postgresql
Postgresql offers functions for date and time that can be used in queries.
Postgres=# select now();
Now
———————————-
22-SEP-20 03:08:42.636385 +05:30
(1 row)
Postgres=# select current_date;
Current_date
————–
22-SEP-20
(1 row)
Postgres=# select current_time;
Current_time
———————–
03:08:53.648466+05:30
(1 row)
We can also perform a date range query to find rows with values between two time stamps:
Postgres=# create table datetable(n int,n1 date);
CREATE TABLE
Postgres=# insert into datetable values (1,’12-01-1980′);
INSERT 0 1
Postgres=# insert into datetable values (2,’12-01-2020′);
INSERT 0 1
Postgres=# insert into datetable values (3,’12-01-2000′);
INSERT 0 1
Postgres=# select * from datetable where n1 between ’12-01-1980′ and ’12-01-2000′;
N | n1
—+——————–
1 | 12-JAN-80 00:00:00
3 | 12-JAN-00 00:00:00
(2 rows)
33. Postgresql function RETURN QUERY result
When a PL/pgsql function is declared to return a SETOF some data type, the return is specified by a RETURN QUERY command:
Postgres=# CREATE FUNCTION get(int) RETURNS SETOF integer AS
$BODY$
BEGIN
RETURN QUERY SELECT age
FROM dummy_table
WHERE age >= $1 ;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
Postgres=# select * from get(9);
Get
—–
25
35
40
50
(4 rows)
34. Postgresql parallel query performance
Parallel queries in postgresql allow you to finish queries faster by utilizing many cpus. These gucs parameters are set in postgresql.conf file:
#max_parallel_maintenance_workers = 2 # taken from max_parallel_workers
#max_parallel_workers_per_gather = 2 # taken from max_parallel_workers
#parallel_leader_participation = on
#max_parallel_workers = 8 # maximum number of max_worker_processes that
# can be used in parallel operations
Postgres=# create table ty(n int);
CREATE TABLE
Postgres=# insert into ty values (generate_series(1,300000));
INSERT 0 300000
Postgres=# analyze ty;
ANALYZE
Postgres=# explain select * from ty where n<=1;
QUERY PLAN
———————————————————————
Gather (cost=1000.00..4536.88 rows=30 width=4)
Workers Planned: 1
-> Parallel Seq Scan on ty (cost=0.00..3533.88 rows=18 width=4)
Filter: (n <= 1)
(4 rows)
Postgres=#
35. Logical operators in postgresql
There are three basic logical operators available in postgresql: AND, OR, and NOT.
These operators are used to match conditions in SQL statements—e.g., in WHERE and HAVING clauses.
AND = when both boolean expressions are true then it will return TRUE
OR = when any boolean expression is true then it will return TRUE
NOT = reverses the value of Boolean operator
Some logical operator examples
· If both expressions are true, then the result is TRUE. Postgresql=# select 1=1/1 and 200=2+198 as result_and_operator; result_and_operator ——————— t (1 row) postgresql=#
· If one expression is true and another expression is NULL, then the result is NULL. Postgresql=# select 4=4 and null; ?Column? ———- (1 row)
· If one expression is true and another expression is false, then the result is TRUE. Postgres=# select 1=100 OR 2=2; ?Column? ———- t (1 row)
36. Catching duplicate rows in a postgresql table
In the following SQL query, there are two records with the value 50:
Postgres=# select age from dummy_table;
Age
—–
25
35
40
50
50
(5 rows)
We can use the following SELECT … HAVING query to find the duplicate rows:
Postgres=# select age, count(age) from dummy_table group by age having count(age)>1;
Age | count
—–+——-
50 | 2
(1 row)
37. Enum query in postgresql
Enumerated (enum) types are data types that comprise a static, ordered set of values.
Postgres=# CREATE TYPE mood AS ENUM (‘sad’, ‘ok’, ‘happy’);
CREATE TYPE
Postgres=# create table testi(n int, n1 mood);
CREATE TABLE
Postgres=# insert into testi values (1,’happy’);
INSERT 0 1
Postgres=# insert into testi values (1,’sad’);
INSERT 0 1
Postgres=# insert into testi values (1,’ok’);
INSERT 0 1
If the enum has not been specified, it will give an error:
Postgres=# insert into testi values (1,’happyo’);
ERROR: invalid input value for enum mood: “happyo”
38. Pivot query in postgresql
A pivot table is a useful way to analyze large quantities of data by organizing it into a more manageable format.
CREATE TABLE newtb(id SERIAL, rowid varchar(10), attri varchar(10), val varchar(10));
INSERT INTO newtb(rowid, attri, val) values(‘t1′,’a1′,’v1’);
INSERT INTO newtb(rowid, attri, val) values(‘t1′,’a2′,’v2’);
INSERT INTO newtb(rowid, attri, val) values(‘t1′,’a3′,’v3’);
INSERT INTO newtb(rowid, attri, val) values(‘t1′,’a4′,’v4’);
INSERT INTO newtb(rowid, attri, val) values(‘t2′,’a1′,’v5’);
INSERT INTO newtb(rowid, attri, val) values(‘t2′,’a2′,’v6’);
INSERT INTO newtb(rowid, attri, val) values(‘t2′,’a3′,’v7’);
INSERT INTO newtb(rowid, attri, val) values(‘t2′,’a4′,’v8’);
To create a pivot table you need to install the tablefunc extension:
Postgres=# create extension tablefunc;
CREATE EXTENSION
Select *
FROM crosstab(
‘Select rowid, attri, val
From newtb
Where attri = ”a2” or attri = ”a3”
Order by 1,2′)
AS newtb(row_name varchar(10), category_1 varchar(10), category_2 varchar(10), category_3 varchar(10));
Row_name | category_1 | category_2 | category_3
———-+————+————+————————–
T1 | v2 | v3 |
T2 | v6 | v7 |
(2 rows)
39. SELF JOIN query in postgresql
When we join a table against itself, this is called a SELF JOIN. This can be done using INNER JOIN or LEFT JOIN. SELF joins are useful when comparing the columns of rows within the same table:
Postgres=# create table emp1(emp_id int, firstname char(10), lastname char(10) , manager_id int);
CREATE TABLE
Postgres=#
Postgres=#
Postgres=# insert into emp1 values(1,’ABC’,’XYZ’,NULL);
INSERT 0 1
Postgres=# insert into emp1 values(2,’TYU’,’BGV’,1);
INSERT 0 1
Postgres=# insert into emp1 values(3,’TEU’,’ZZV’,1);
INSERT 0 1
Postgres=# insert into emp1 values(4,’REU’,’AZV’,2);
INSERT 0 1
Postgres=# insert into emp1 values(5,’QREU’,’WZV’,2);
INSERT 0 1
Postgres=# insert into emp1 values(6,’DREU’,’QZV’,3);
INSERT 0 1
Postgres=# select a.firstname,b.lastname from emp1 a inner join emp1 b on a.emp_id=b.manager_id order by 1 ;
Firstname | lastname
————+————
ABC | ZZV
ABC | BGV
TEU | QZV
TYU | WZV
TYU | AZV
(5 rows)
Postgres=#
40. Parent-child recursive query in postgresql
With the help of common table expressions (CTE) we can perform parent-child recursive queries:
Postgres=# CREATE TABLE recu_pc (
Id SERIAL PRIMARY KEY,
Name varchar(10) NOT NULL,
Parent_id integer );
CREATE TABLE
Postgres=# insert into recu_pc values (1, ‘Grandmother’, NULL);
INSERT 0 1
Postgres=# insert into recu_pc values (2, ‘mother’, 1);
INSERT 0 1
Postgres=# insert into recu_pc values (3, ‘daughter’, 2);
INSERT 0 1
Postgres=# WITH RECURSIVE rec_q (id) as
(
SELECT recu_pc.id, recu_pc.name from recu_pc where name=’mother’
UNION ALL
SELECT recu_pc.id, recu_pc.name from rec_q, recu_pc where recu_pc.parent_id = rec_q.id
)
SELECT *
FROM rec_q;
Id | name
—-+———-
2 | mother
3 | daughter
(2 rows)
41. Defining a variable in a query in postgresql
Using an anonymous block, we can define a variable that can be passed to in a query:
Postgres=# do
$$
Declare
A int;
Begin
Select age into a from dummy_table
Where name =’XYZ’;
Raise notice ‘%’,a;
End;
$$;
NOTICE: 25
DO
42. PREPARE statement in postgresql
A prepared statement is used to optimize performance. When the PREPARE statement is executed, it is not only parsed but analyzed too, and when we fire the EXECUTE command the prepared statement is planned and executed.
Prepared statements can accept parameters.
Postgres=# prepare test(int) as
Select * from dummy_table where age=$1;
PREPARE
Postgres=# execute test(50);
Name | address | age
——+————+———
PQR | location-D | 50
CXC | 1 | 50
(2 rows)
43. Checking NULL values in postgresql
To Identify or select rows that have NULL values, the IS NULL condition can be used in the WHERE clause.
Postgres=# select * from dummy_table;
Name | address | age
——+————+—–
XYZ | location-A | 25
ABC | location-B | 35
DEF | location-C | 40
PQR | location-D | 54
PQR | location-D |
(5 rows)
Identifying null values in ‘age’ column:
Postgres=# select name from dummy_table where age is null;
Name
——
PQR
(1 row)
44. Checking if a query is empty in postgresql
We can use EXISTS to check if a query is empty. EXISTS is a boolean operator that tests the existence of rows in a subquery.
Postgres=# select exists(select * from (select ‘true’ where 1=3));
Exists
——–
F
(1 row)
Postgres=# select exists(select * from (select ‘true’ where 1=1));
Exists
——–
T
(1 row)
45. Query execution time log in postgresql
To see the execution time log of a query, you need to enable the relevant GUC parameters:
Postgresql=# set log_min_duration_statement=0;
SET
Postgresql=# set log_statement=’all’;
SET
Now, if we check the log file, which was created in the data/log folder, we should receive execution time messages:
2020-09-23 02:47:12.887 +0530 [30737] LOG: statement: create table gg1(n int);
2020-09-23 02:47:12.888 +0530 [30737] LOG: duration: 1.165 ms
2020-09-23 02:47:28.092 +0530 [30737] LOG: statement: insert into gg1 values (generate_series(1,100000));
2020-09-23 02:47:28.182 +0530 [30737] LOG: duration: 94.858 ms
46. Running a query in a shell script in postgresql
We can use the bash shell script to execute psql commands. The example below is a print.sh file in which we perform the addition of two numbers:
[edb@tushar-ldap-docker misc]$ cat print.sh
#!/bin/bash
# This script is used to print addition of 2 numbers
# 1. Get path of bin directory.
BIN_DIRECTORY=’/usr/psql-11/bin/’
$BIN_DIRECTORY/psql -U postgres -p 5432 -d postgres -c ‘select 5+5;’
Execute the shell script.
Edb@tushar-ldap-docker misc]$ print.sh
?Column?
———-
10
(1 row)
47. UNION operator in postgresql
UNION is used to combine the results of two or more SQL statements while eliminating duplicate rows.
Postgres=# create table tab1(n int);
CREATE TABLE
Postgres=# insert into tab1 values (1),(2);
INSERT 0 2
Postgres=# create table tab2(n int);
CREATE TABLE
Postgres=# insert into tab2 values (3),(2);
INSERT 0 2
Postgres=# select * from tab1 union select * from tab2;
N
—
2
1
3
(3 rows)
48. Query optimization in postgresql
These are a few of the tools available for improving postgresql query performance:
Unfortunately there is no single fixed formula for the optimization of postgresql queries, as query performance can be affected by many things (for example, performance could be affected by a hardware issue).
1. EXPLAIN
2. VACUUM
3. ANALYZE
4. Database indexes
5. Postgresql configuration parameters
6. EXPLAIN
The EXPLAIN command shows the execution plan of a statement. Adding the ANALYZE keyword in the EXPLAIN plan will execute the query and display the true row counts and true run time.
7. VACUUM
The VACUUM command basically removes all the tuples that were deleted or made obsolete by an UPDATE but were not physically removed from their table.
8. ANALYZE
ANALYZE collects statistics about the contents of tables in the database
9. Database Indexes
Creating database indexes let you avoid a full table scan, which makes executing a query very slow.
10. Postgresql configuration parameters
We may use some of the postgresql configuration parameters to get better performance—e.g., max_connections, checkpoint_segments, work_mem, and random_page_cost.
49. ALTER TABLE query in postgresql
The ALTER TABLE command is used to add or modify columns in table:
Postgres=# alter table dummy_table add col1 int;
ALTER TABLE
Postgres=# alter table dummy_table rename col1 to col2;
ALTER TABLE
Postgres=# alter table dummy_table alter column col2 type char;
ALTER TABLE
Postgres=# alter table dummy_table drop column col2;
ALTER TABLE
50. COMMENT in postgresql
Postgresql gives us a feature where we can provide a comment about a database object using the COMMENT statement.
Postgres=# Comment on table dummy_table is ‘This was a testing table’;
COMMENT
Postgres=# Comment on function f is ‘Don”t update the function please’ ;
COMMENT
Using the pg_description system catalog table we can retrieve the comment.
Hi everybody, here every person is sharing these familiarity, thus it’s fastidious to read this web
site, and I used to pay a quick visit this web site everyday.
I have taken notice that in cameras, special receptors help to maintain focus automatically. The actual sensors connected with some digital cameras change in contrast, while others work with a beam associated with infra-red (IR) light, specifically in low light. Higher specs cameras oftentimes use a mix of both methods and might have Face Priority AF where the digital camera can ‘See’ your face while focusing only upon that. Thanks for sharing your thinking on this web site.
Hello, i believe that i saw you visited my website thus i return the want?I am attempting to find things to enhance my site!I assume its adequate to use some of your ideas!!
I simply could not leave your web site prior to suggesting that I really enjoyed the usual info an individual supply for your guests? Is going to be back incessantly in order to check out new posts
Whether you believe in God or not, this is a must-read message!!!
Throughout time, we can see how we have been slowly conditioned to come to this point where we are on the verge of a cashless society. Did you know that the Bible foretold of this event almost 2,000 years ago?
In Revelation 13:16-18, we read,
“He (the false prophet who decieves many by his miracles) causes all, both small and great, rich and poor, free and slave, to receive a mark on their right hand or on their foreheads, and that no one may buy or sell except one who has the mark or the name of the beast, or the number of his name.
Here is wisdom. Let him who has understanding calculate the number of the beast, for it is the number of a man: His number is 666.”
Referring to the last generation, this could only be speaking of a cashless society. Why? Revelation 13:17 tells us that we cannot buy or sell unless we receive the mark of the beast. If physical money was still in use, we could buy or sell with one another without receiving the mark. This would contradict scripture that states we need the mark to buy or sell!
These verses could not be referring to something purely spiritual as scripture references two physical locations (our right hand or forehead) stating the mark will be on one “OR” the other. If this mark was purely spiritual, it would indicate only in one place.
This is where it really starts to come together. It is shocking how accurate the Bible is concerning the implatnable RFID microchip. These are notes from a man named Carl Sanders who worked with a team of engineers to help develop this RFID chip
“Carl Sanders sat in seventeen New World Order meetings with heads-of-state officials such as Henry Kissinger and Bob Gates of the C.I.A. to discuss plans on how to bring about this one-world system. The government commissioned Carl Sanders to design a microchip for identifying and controlling the peoples of the world—a microchip that could be inserted under the skin with a hypodermic needle (a quick, convenient method that would be gradually accepted by society).
Carl Sanders, with a team of engineers behind him, with U.S. grant monies supplied by tax dollars, took on this project and designed a microchip that is powered by a lithium battery, rechargeable through the temperature changes in our skin. Without the knowledge of the Bible (Brother Sanders was not a Christian at the time), these engineers spent one-and-a-half-million dollars doing research on the best and most convenient place to have the microchip inserted.
Guess what? These researchers found that the forehead and the back of the hand (the two places the Bible says the mark will go) are not just the most convenient places, but are also the only viable places for rapid, consistent temperature changes in the skin to recharge the lithium battery. The microchip is approximately seven millimeters in length, .75 millimeters in diameter, about the size of a grain of rice. It is capable of storing pages upon pages of information about you. All your general history, work history, crime record, health history, and financial data can be stored on this chip.
Brother Sanders believes that this microchip, which he regretfully helped design, is the “mark” spoken about in Revelation 13:16–18. The original Greek word for “mark” is “charagma,” which means a “scratch or etching.” It is also interesting to note that the number 666 is actually a word in the original Greek. The word is “chi xi stigma,” with the last part, “stigma,” also meaning “to stick or prick.” Carl believes this is referring to a hypodermic needle when they poke into the skin to inject the microchip.”
Mr. Sanders asked a doctor what would happen if the lithium contained within the RFID microchip leaked into the body. The doctor replied by saying a terrible sore would appear in that location. This is what the book of Revelation says:
“And the first (angel) went, and poured out his vial on the earth; and there fell a noisome and grievous sore on the men which had the mark of the beast, and on them which worshipped his image” (Revelation 16:2).
You can read more about it here–and to also understand the mystery behind the number 666: https://2ruth.org/rfid-mark-of-the-beast-666-revealed/
The third angel’s warning in Revelation 14:9-11 states,
“Then a third angel followed them, saying with a loud voice, ‘If anyone worships the beast and his image, and receives his mark on his forehead or on his hand, he himself shall also drink of the wine of the wrath of God, which is poured out full strength into the cup of His indignation. He shall be tormented with fire and brimstone in the presence of the holy angels and in the presence of the Lamb. And the smoke of their torment ascends forever and ever; and they have no rest day or night, who worship the beast and his image, and whoever receives the mark of his name.'”
Who is Barack Obama, and why is he still in the public scene?
So what’s in the name? The meaning of someone’s name can say a lot about a person. God throughout history has given names to people that have a specific meaning tied to their lives. How about the name Barack Obama? Let us take a look at what may be hiding beneath the surface.
Jesus says in Luke 10:18, “…I saw Satan fall like lightning from heaven.”
The Hebrew Strongs word (H1299) for “lightning”: “bârâq” (baw-rawk)
In Isaiah chapter 14, verse 14, we read about Lucifer (Satan) saying in his heart:
“I will ascend above the heights of the clouds, I will be like the Most High.”
In the verses in Isaiah that refer directly to Lucifer, several times it mentions him falling from the heights or the heavens. The Hebrew word for the heights or heavens used here is Hebrew Strongs 1116: “bamah”–Pronounced (bam-maw’)
In Hebrew, the letter “Waw” or “Vav” is often transliterated as a “U” or “O,” and it is primarily used as a conjunction to join concepts together. So to join in Hebrew poetry the concept of lightning (Baraq) and a high place like heaven or the heights of heaven (Bam-Maw), the letter “U” or “O” would be used. So, Baraq “O” Bam-Maw or Baraq “U” Bam-Maw in Hebrew poetry similar to the style written in Isaiah, would translate literally to “Lightning from the heights.” The word “Satan” in Hebrew is a direct translation, therefore “Satan.”
So when Jesus told His disciples in Luke 10:18 that He beheld Satan fall like lightning from heaven, if this were to be spoken by a Jewish Rabbi today influenced by the poetry in the book of Isaiah, he would say these words in Hebrew–the words of Jesus in Luke 10:18 as, And I saw Satan as Baraq O Bam-Maw.
The names of both of Obama’s daughters are Malia and Natasha. If we were to write those names backward (the devil does things in reverse) we would get “ailam ahsatan”. Now if we remove the letters that spell “Alah” (Allah being the false god of Islam), we get “I am Satan”. Coincidence? I don’t think so.
Obama’s campaign logo when he ran in 2008 was a sun over the horizon in the west, with the landscape as the flag of the United States. In Islam, they have their own messiah that they are waiting for called the 12th Imam, or the Mahdi (the Antichrist of the Bible), and one prophecy concerning this man’s appearance is the sun rising in the west.
“Then I saw another angel flying in the midst of heaven, having the everlasting gospel to preach to those who dwell on the earth—to every nation, tribe, tongue, and people— saying with a loud voice, ‘Fear God and give glory to Him, for the hour of His judgment has come; and worship Him who made heaven and earth, the sea and springs of water.'” (Revelation 14:6-7)
Why have the word’s of Jesus in His Gospel accounts regarding His death, burial, and resurrection, been translated into over 3,000 languages, and nothing comes close? The same God who formed the heavens and earth that draws all people to Him through His creation, likewise has sent His Word to the ends of the earth so that we may come to personally know Him to be saved in spirit and in truth through His Son Jesus Christ.
Jesus stands alone among the other religions that say to rightly weigh the scales of good and evil and to make sure you have done more good than bad in this life. Is this how we conduct ourselves justly in a court of law? Bearing the image of God, is this how we project this image into reality?
Our good works cannot save us. If we step before a judge, being guilty of a crime, the judge will not judge us by the good that we have done, but rather by the crimes we have committed. If we as fallen humanity, created in God’s image, pose this type of justice, how much more a perfect, righteous, and Holy God?
God has brought down His moral laws through the 10 commandments given to Moses at Mt. Siani. These laws were not given so we may be justified, but rather that we may see the need for a savior. They are the mirror of God’s character of what He has put in each and every one of us, with our conscious bearing witness that we know that it is wrong to steal, lie, dishonor our parents, murder, and so forth.
We can try and follow the moral laws of the 10 commandments, but we will never catch up to them to be justified before a Holy God. That same word of the law given to Moses became flesh about 2,000 years ago in the body of Jesus Christ. He came to be our justification by fulfilling the law, living a sinless perfect life that only God could fulfill.
The gap between us and the law can never be reconciled by our own merit, but the arm of Jesus is stretched out by the grace and mercy of God. And if we are to grab on, through faith in Him, He will pull us up being the one to justify us. As in the court of law, if someone steps in and pays our fine, even though we are guilty, the judge can do what is legal and just and let us go free. That is what Jesus did almost 2,000 years ago on the cross. It was a legal transaction being fulfilled in the spiritual realm by the shedding of His blood.
For God takes no pleasure in the death of the wicked (Ezekiel 18:23). This is why in Isaiah chapter 53, where it speaks of the coming Messiah and His soul being a sacrifice for our sins, why it says it pleased God to crush His only begotten Son.
This is because the wrath that we deserve was justified by being poured out upon His Son. If that wrath was poured out on us, we would all perish to hell forever. God created a way of escape by pouring it out on His Son whose soul could not be left in Hades but was raised and seated at the right hand of God in power.
So now when we put on the Lord Jesus Christ (Romans 13:14), God no longer sees the person who deserves His wrath, but rather the glorious image of His perfect Son dwelling in us, justifying us as if we received the wrath we deserve, making a way of escape from the curse of death–now being conformed into the image of the heavenly man in a new nature, and no longer in the image of the fallen man Adam.
Now what we must do is repent and put our trust and faith in the savior, confessing and forsaking our sins, and to receive His Holy Spirit that we may be born again (for Jesus says we must be born again to enter the Kingdom of God–John chapter 3). This is not just head knowledge of believing in Jesus, but rather receiving His words, taking them to heart, so that we may truly be transformed into the image of God. Where we no longer live to practice sin, but rather turn from our sins and practice righteousness through faith in Him in obedience to His Word by reading the Bible.
Our works cannot save us, but they can condemn us; it is not that we earn our way into everlasting life, but that we obey our Lord Jesus Christ:
“And having been perfected, He became the author of eternal salvation to all who obey Him.” (Hebrews 5:9)
“Now I saw a new heaven and a new earth, for the first heaven and the first earth had passed away. Also there was no more sea. Then I, John, saw the holy city, New Jerusalem, coming down out of heaven from God, prepared as a bride adorned for her husband. And I heard a loud voice from heaven saying, ‘Behold, the tabernacle of God is with men, and He will dwell with them, and they shall be His people. God Himself will be with them and be their God. And God will wipe away every tear from their eyes; there shall be no more death, nor sorrow, nor crying. There shall be no more pain, for the former things have passed away.’
Then He who sat on the throne said, ‘Behold, I make all things new.’ And He said to me, ‘Write, for these words are true and faithful.’
And He said to me, ‘It is done! I am the Alpha and the Omega, the Beginning and the End. I will give of the fountain of the water of life freely to him who thirsts. He who overcomes shall inherit all things, and I will be his God and he shall be My son. But the cowardly, unbelieving, abominable, murderers, sexually immoral, sorcerers, idolaters, and all liars shall have their part in the lake which burns with fire and brimstone, which is the second death.'” (Revelation 21:1-8).
A lot of of the things you point out happens to be astonishingly precise and it makes me ponder why I had not looked at this in this light previously. This piece really did switch the light on for me as far as this subject goes. But there is just one factor I am not too comfortable with so while I make an effort to reconcile that with the central theme of the point, permit me observe what the rest of your readers have to point out.Nicely done.
Incredible! This blog looks just like my old one! It’s on a totally different topic but it has pretty much the same page layout and design. Wonderful choice of colors!
You made some nice points there. I did a search on the subject and found nearly all persons will agree with your blog.