COMP3311 20T3 PostgreSQL Database Systems

Summary information about what PostgreSQL v12 provides.
In the examples below, x, y, s, etc. are attributes.

Data Types

Base Data Types integer, float, char(N), varchar(N), text, date, time, timestamp
Domains create domain Name as BaseType check Constraint;
Tuple Types create type Name as (Attr1 Type1, ..., Attrn Typen);

Expressions

Arithmetic + - * / % e.g. x + 3,   x + y + z,   y % 4
Logic and or not = <> e.g. x > 5 and x < 10,   z <> 'abc',
x between 6 and 9
String || like ilike ~ ~* e.g. s1 || s2,   name like '%abc%'
string literals: 'John''s book',   e'hello\n'
Note: double-quotes ("...") are not strings
NULL is null, coalesce() e.g. x is null,   y is not null,
note that x = null always fails,
coalesce(x,y,99) value is first non-null or 99

Functions

Feature PostgreSQL
Case-insensitive SQL pattern matching Attribute ilike Pattern
Regular expression pattern matching Attribute ~ Pattern
String concatenation String1 || String2
String concatenation aggregrate string_agg(TextAttribute[,Separator])
Substring substr(TextAttribute,Start,Length)
Aggregates count(), min(), max(), avg(), sum()

Constraints

Attribute check, not null, unique e.g. check (x >= 0),  x not null,
x unique
Foreign key foreign key foreign key (Attributes)
references Table(Attributes)
e.g. foreign key (x,y) references R(a,b)
Primary key primary key primary key (Attributes)
e.g. primary key (x)
implies x is not null & unique

Defining Things

Tables create table Name (Attributes+Constraints);
e.g. 
create table R (
   s   text primary key,
   x   integer not null,
   y   float check (y > 0),
   foreign key (x) references T(x)
);
Views create or replace view Name(AttrNames)
as SQL_Statement
e.g. 
create or replace view Movies(id,title) as
select mid, name
from   Titles
where  format = 'movie';
Functions create or replace function Name(Params)
returns Type as $$ Body $$ language plpgsql
e.g. 
create or replace function
   fac(n integer) returns integer
as $$
declare
   prod integer := 1;
   i    integer;
begin
   if (n < 1) then
      return 0;
   end if;
   i := 1;
   while (i <= n) loop
      prod := prod * i;
      i := i + 1;   
   end loop;
   return prod;
end;
$$ language plpgsql;
Functions returning tables create or replace function Name(Params)
returns setof Type as $$ Body $$ language plpgsql;
e.g. 
create type Results as (x integer, y integer);
create or replace function
   pairs(_n integer) returns setof Results
as $$
declare
   tup record;
begin
   for tup in
      select x, y from R limit _n
   loop
      return next tup;
   end loop;
end;
$$ language plpgsql;
Domains create domain Name as DataType Constraint
e.g. 
create domain PosInt as integer check (value > 0);
create domain CourseCode as char(8)
              check value ~ '[A-Z]{4}[0-9]{4}';
Aggregates create aggregate Name (
    basetype = TypeOfValuesBeingAggregated,
    initcond = InitialValueOfState,
    sfunc = StateTransitionFunction,
    stype = StateType,
    [ finalfunc = FinalisationFunction ]
);
e.g. 
create function
    add(x integer, y integer) returns integer
as $$
begin  return x+y;  end;
$$ language plpgsql;

create aggregate mySum (
    basetype  = integer
    stype     = integer,
    initcond  = 0,
    sfunc     = add
);
Enums create type Name as enum (Values);
e.g. 
create type Rainbow as enum
   ('red', 'orange', 'yellow', 'green', 'blue', 'purple');

Queries

General format 
select Expressions as Names 
from   Table1
       join Table2 on JoinCondition1
       ...
       join Tablen on JoinConditionn-1
where  Condition
group  by Attributes
having    Condition
order  by Attributes
Examples 
-- assume schema: R(x,y,z), S(a,b)

-- show all info from table R
select * from R;

-- show unique x values from table R
select distinct x from R;

-- show unique x values from table R
select *
from   R join S on R.x = S.a
where  R.y > 5;

-- how many of each x value
select x, count(*) from  R group by x;

-- how many of each common x value
select x, count(*)
from   R
group  by x
having count(*) > 10;

-- how many of each R.x value is
-- associated with an S.a value;
-- if no associated S.a value, count = 0
select x, count(a)
from   R left outer join S on R.x = S.a
group  by x;

Commands outside psql

Getting a list of databases psql -l
Connect to a database to ask SQL queries psql DatabaseName
Create a database createdb DatabaseName
Remove a database dropdb DatabaseName

Commands within psql

Command PostgreSQL
Exit the SQL shell \q
Get a list of tables/views in a database \d
Execute an SQL statement select * from TableName;
Excecute SQL commands from a file \i FileName
Edit a file and reload \ef FileName
Go to previous command Up-arrow
Create a view create or replace V(a,b,c)
as select x,y,z ...
Temporary tables via WITH with tab as (select...) ...
Union, Intersect, Difference (SelectStatement1) union (SelectStatement2)
or SelectStatement1 union SelectStatement2