COMP3311 20T3 PostgreSQL Database Systems Summary information about what PostgreSQL v12 provides.
In the examples below, x, y, s, etc. are attributes. Data Types
Expressions
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);
Arithmetic
+-*/%
e.g.x+3, x+y+z, y%4
Logic
and or not = <>
e.g.x>5andx<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)
Defining Things
implies x is not null & unique
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;
Queries
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’);
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
Commands within 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
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