Category Archives: PostgreSQL

PostgreSQL Tutorial

PostgreSQL tutorial

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… Read More »

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 $$… Read More »

PostgreSQL Create Function

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… Read More »

PostgreSQL View

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… Read More »

PostgreSQL SUM

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 id good_type name description price 1 A Car_1 Car 1 description 100 2 A Car_2 Car 2 description 200 3 A Car_3 Car 3 description 100 4 B Boat_4 Boat 4… Read More »

PostgreSQL MIN

PostgreSQL MIN The PostgreSQL MIN aggregate function returns the minimum value in a select. MIN syntax SELECT MIN(table_column) FROM table_name; MIN example Goods table id good_type name description price 1 A Car_1 Car 1 description 100 2 A Car_2 Car 2 description 200 3 A Car_3 Car 3 description 100 4 B Boat_4 Boat 4 description 500 5… Read More »

PostgreSQL MAX

PostgreSQL MAX The PostgreSQL MAX aggregate function returns the maximum value in a select. MAX syntax SELECT MAX(table_column) FROM table_name; MAX example Goods table id good_type name description price 1 A Car_1 Car 1 description 100 2 A Car_2 Car 2 description 200 3 A Car_3 Car 3 description 100 4 B Boat_4 Boat 4 description 500 5… Read More »

PostgreSQL AVG

PostgreSQL AVG The PostgreSQL AVG aggregate function returns the average in a select query. AVG syntax SELECT AVG(table_column) FROM table_name; AVG example Goods table id good_type name description price 1 A Car_1 Car 1 description 100 2 A Car_2 Car 2 description 200 3 A Car_3 Car 3 description 100 4 B Boat_4 Boat 4 description 500 5… Read More »

PostgreSQL Count

PostgreSQL Count The PostgreSQL COUNT function returns the number of the rows in a select. COUNT syntax SELECT COUNT(*) FROM table_name; SELECT COUNT(column_name) FROM table_name; COUNT example Goods table id good_type name description price 1 A Car_1 Car 1 description 100 2 A Car_2 Car 2 description 200 3 A Car_3 Car 3 description 100 4 B Boat_4… Read More »

PostgreSQL Having

PostgreSQL Having The PostgreSQL HAVING is used with a GROUP BY clause and returns records where aggregate values meet the specified conditions. Having syntax SELECT column_name1, column_name2, function(column_name1) FROM table_name GROUP BY column_name1, column_name2 HAVING function(column_name1) operator value Having example Goods table id good_type name description price 1 A Car_1 Car 1 description 100 2 A Car_2 Car… Read More »