PostgreSQL

PostgreSQL Tutorial – Learn how to use PostgreSQL language.
The basic objects of PostgreSQL are: tables, columns, indexes, constraints, functions, triggers.
To modify objects is used create, insert, update, select, delete, drop and alter.

PostgreSQL tutorial

• PostgreSQL Create Table
• PostgreSQL Drop Table
• PostgreSQL Select
• PostgreSQL Insert
• PostgreSQL Update
• PostgreSQL Delete
• PostgreSQL Distinct
• PostgreSQL Where
• PostgreSQL And – Or
• PostgreSQL Between
• PostgreSQL Like
• PostgreSQL Order By
• PostgreSQL Group By
• PostgreSQL Having
• PostgreSQL Count
• PostgreSQL AVG
• PostgreSQL MAX
• PostgreSQL MIN
• PostgreSQL SUM
• PostgreSQL View
• PostgreSQL Create Function
• PostgreSQL Create Trigger

PostgreSQL Create Trigger

The PostgreSQL CREATE TRIGGER is used to create a trigger in a PostgreSQL database.

Create Trigger syntax

CREATE TRIGGER name
{ BEFORE | AFTER | INSTEAD OF } 
    ON table_name
    [FOR [EACH] {ROW | STATEMENT}]
    EXECUTE PROCEDURE function_name(arguments);

Create Trigger example

Step 1 – Create postgresql trigger function

CREATE FUNCTION f_goods() 
RETURNS trigger AS $$
BEGIN
 IF NEW.name IS NULL THEN
  RAISE EXCEPTION 'Insert name';
 END IF;
 IF NEW.good_type IS NULL THEN
  RAISE EXCEPTION 'Insert good_type';
 END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 2 – Create postgresql trigger

CREATE TRIGGER trigger_goods
BEFORE INSERT OR UPDATE ON goods
FOR EACH ROW 
EXECUTE PROCEDURE f_goods();

PostgreSQL Create Function

The PostgreSQL CREATE FUNCTION is used to create a function in a PostgreSQL database.

Create Function example

CREATE OR REPLACE FUNCTION
change_price(in p_name varchar(50), in p_price numeric) 
RETURNS varchar AS $$
DECLARE
  v_msg varchar(250):='Price changed';
  v_count numeric:=0;
BEGIN
  SELECT count(*) into v_count 
  FROM goods 
  WHERE name = p_name;
  if v_count=0 then
    v_msg:='No name found';
    return v_msg;
  else
    update goods 
	set price=p_price 
	where name = p_name;
    return v_msg;
  end if;   
EXCEPTION 
  when others then
    begin
     v_msg := 'Error!';
     return v_msg;
    end;    
END;
$$ LANGUAGE 'plpgsql';

PostgreSQL View

The PostgreSQL commands:
– CREATE VIEW is used to create a view.
– ALTER VIEW is used to change view name.
– DROP VIEW is used to delete a view.

View syntax

-- create view
CREATE [OR REPLACE] VIEW 
view_name AS SELECT * query;

-- alter view
ALTER VIEW [ IF EXISTS ] 
view_name RENAME TO new_view_name;

-- drop view
DROP VIEW [ IF EXISTS ] view_name;

View example

CREATE VIEW goods_view 
AS SELECT * FROM goods;

ALTER VIEW goods_view 
RENAME TO pg_goods_view;

DROP VIEW pg_goods_view;

PostgreSQL SUM

The PostgreSQL SUM aggregate function returns the sum value of all rows in a select.

SUM syntax

SELECT SUM(table_column)
FROM table_name;

SUM example

Goods table

SELECT SUM(price) 
FROM goods;

Result

2000

SELECT good_type, SUM(price) sum_price
FROM goods
GROUP BY good_type;

Result