1 pgCheatSheet - inf-swe

1
1.1
pgCheatSheet
Drop/Create Table
drop table if exists projekt cascade;
drop table if exists bauteil cascade;
create table projekt (
prj_id serial NOT NULL primary key,
name text NOT NULL
);
create table bauteil (
teile_id serial NOT NULL,
name text NOT NULL,
fk_prj_id int NULL,
primary key (teile_id),
foreign key (fk_prj_id) references projekt(prj_id)
);
create index idx_projekt_name on projekt(name);
create index idx_bauteil_fk_prj_id on bauteil(fk_prj_id);
1.2
Select
select 'yes' as answer; -- rename output column
select p.name from produkte p where p.name like '%tee'; -- table alias p
select 'yes' where 42 in (41, 42, 43); -- in clause
select generate_series(1,42)
union
select generate_series(1,21)
order by 1; -- 1..42
select generate_series(1,42)
except
select generate_series(1,21)
order by 1; -- 22..42
select generate_series(1,42) order by 1 limit 21 -- 1..21
1
1.3
Joins
Figure 1: SQL Joins
select
*
from
a
inner join b on a.id = b.fk_a
left join c on b.id = c.fk_b
right join d on c.id = d.fk_c;
-- inner join
-- left join
-- right join
select * from a,b;
-- cross join
2
1.4
Subselect
select 'yes' where 42 = (select 21 + 21); -- subselect returns 42
select
(select 'yes'::text where 42 = 42) as answer
where
21 != 42
select
*
from
(select generate_series(20,100) num) a,
(select generate_series(1,50) num) b
where
a.num = b.num; -- two sub selects used as a "table"
-- move subselect to the top of the query
with s1 as (select generate_series(1,42) num),
s2 as (select generate_series(1,21) num)
select s1.num s1_num, s2.num s2_num from s1, s2 where s1.num > s2.num
1.5
Group/Order by
select
name, count(*)
from
produkte
group by 1
having count(*) > 2
order by 1; -- group by product names and count, order by product name
select generate_series(1,42) order by random() -- random order
1.6
Aggregation
select sum(preis) from rechn_prod; -- sum of all
select avg(preis) from rechn_prod; -- avg of each row
select count(*) from rechn_prod;
-- number of rows
1.7
Delete
delete from projekt;
delete from projekt where prj_id > 1;
truncate table projekt cascade;
3
1.8
Insert
insert into projekt("prj_id", "name") values(1,'1st Project');
insert into projekt("prj_id", "name") values
(2,'2nd Project'),
(3,'3rd Project');
insert into projekt("prj_id", "name")
select
s.num,
'Prj #' || s.num
from
(select generate_series(4, 42) num) s
1.9
Update
update projekt set "name" = 'Test' where prj_id = 1;
1.10
Functions
select round(42.21, 1); -- 42.2
select random(); -- 0..1
select (random() * 1000)::int % 12 + 1; -- 1..12
select to_char(42, 'FM00000'); -- 00042
1.11
Datetime
select now(); -- current date and time
select CURRENT_DATE; -- current date
select CURRENT_DATE - '1 days'::interval; -- yesterday
select extract(year from now()); -- current year
select extract(month from now()); -- current month
select extract(day from now()); -- current day
select to_char(now(), 'YYYY-MM-DD'); -- format current date
4
1.12
String
select * from produkte where name like '%kaffee%';
select concat('Hallo', ' ', 'Peter'); -- Hallo Peter
select 'Hallo' || ' ' || 'Marie'; -- Hallo Marie
select format('Testing %s, %s, %s, %%', 'one', 'two', 'three'); -- Testing one, two, three, %
select upper('text'); -- TEXT
select lower('TEXT'); -- text
select length('Hallo'); -- 5
select substring('Hallo Tom', 7, length('Hallo Tom')); -- Tom, ab dem 7ten Zeichen (nicht Index!)
1.13
Other
select coalesce(null, null, 42); -- first non null, 42
select 41.7::int -- 42, round and cast a numeric to int
select 't'::bool "true", 'f'::bool "false" -- boolean true and false
select row_number() OVER (ORDER BY name), name from produkte; -- row number and product name
select * from generate_series(1, 42); -- "table" with 1..42
5