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;

NOTICEtable “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:

[[email protected]]$ 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 fromwhere 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.

[[email protected] bin]$ ls  data/log

Postgresql-2020-09-17_150932.log  postgresql-2020-09-19_000000.log 

[[email protected] 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] LOGstatement: 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] LOGstatement: 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:

[[email protected] 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.

[email protected] 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.

Leave a Reply

Newsletter Signup

Subscribe to our weekly newsletter below and never miss the latest product or an exclusive offer.

10 Best Web Development Frameworks in 2022 Frontend & Backend 18 Best Open-Source and Free Database Software | best database software for beginners Top 10+ Best Java IDEs & Online Java Compilers | best java ide for beginners Top 5 Themes For Blogger, professional blogger theme download 10 Popular PHP frameworks for web developers| best php frameworks 10 Best Artificial Intelligence Software|artificial intelligence tools BEST Python Courses Online,Top 10 Courses to Learn Python in 2022 Top 13 python libraries for data science how to create google web stories, Steps to create web stories 5 nft games to earn money | Best NFT games for earn crypto Earn Money From Minting NFTs| How to mint NFT for free
%d bloggers like this: