sudo service postgresql statussudo su - postgrespsql
with d as (
select
*,
row_number() over (partition by user_login order by issued_at) rn
from tbl
) select * from d where rn = 1;select
coalesce(type::text, 'total by type') type,
coalesce(cost::text, 'total') cost,
sum(cost) as balance
from expenses e
where plan_date = '2023-12-04'
group by rollup (type,cost);create extension pgcrypto;
with a as (
select
house_id,
doc_id,
json_agg(json_build_object('doc_name', doc_name, 'file_id', file_id, 'file_path', file_path)) j
from tbl
group by house_id, doc_id
) select house_id, json_agg(j)
from a
group by house_id;create table timeslots (
staff_id int,
date date,
time_from time,
time_to time,
free boolean default true
);CREATE EXTENSION btree_gist;
create table timeslots (
staff_id int,
slot_time tsrange,
free boolean default true,
EXCLUDE USING GIST (staff_id WITH =, slot_time WITH &&)
);create table users (
id serial primary key,
balance numeric(9, 2)
);
create table user_cards (
id serial,
user_id int references users(id),
filename text
);create table customers (
id serial PRIMARY KEY,
name text
);
create table orders (
id serial PRIMARY KEY,
customer_id int
);
alter table orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id) ;CREATE INDEX email_ix ON Staff(email);
CREATE INDEX jobtitle_id_ix ON Staff(jobtitle_id);SELECT Jobtitles.name, salary
FROM Jobtitles
JOIN Staff ON Staff.jobtitle_id = Jobtitles.jobtitle_id
ORDER BY salary DESC
FETCH FIRST ROW WITH TIES;select 't1', phone from t1 where phone = '987654321'
union all select 't2', phone from t2 where phone = '987654321'
union all select 't3', phone from t3 where phone = '987654321'SELECT
users.*, categories.key as category
FROM users
JOIN categories ON categories.user_id = users.id
WHERE categories.key = 1
;delete t.*
from t
left join t t1 on t.a = t1.a and t.id > t1.id
where t1.id is not null;Create table course (
id serial primary key,
name text
);
Create table programm (
id serial primary key,
name text
);
Create table course_programms (
cource_id int references course(id),
progamm_id int references programm(id),
programm_order int,
primary key (cource_id, progamm_id)
);select t1.customer_id
from t t1
join t t2 on t1.customer_id = t2.customer_id and t2.type_system ='CRM'
where t1.type_system ='DBO'CREATE SEQUENCE profiles_seq START 1;
CREATE OR REPLACE FUNCTION nextval_rand(regclass)
RETURNS text AS
$func$
BEGIN
EXECUTE format('ALTER SEQUENCE profiles_seq INCREMENT %s', (random() * 100)::int + 1);
RETURN 'Пользователь #' || nextval($1)::text;
END
$func$ LANGUAGE plpgsql SECURITY DEFINER;
create table profiles (
id uuid not null,
username text default nextval_rand('profiles_seq'::regclass),
primary key (id)
);
insert into profiles (id) values
(gen_random_uuid()),
(gen_random_uuid()),
(gen_random_uuid()),
(gen_random_uuid()),
(gen_random_uuid());
select * from profiles;select
problems.*,
case when coalesce(max(status_id), 2) = 2 then 'Not Completed' else 'Completed' end problem_status
from problems
left join tasks on tasks.problem_id = problems.id
group by problems.id, problems.title
order by problems.id;with problem_status as (
select
problems.*,
max(status_id) status_id
from problems
left join tasks on tasks.problem_id = problems.id
group by problems.id, problems.title
) select problem_status.id, problem_status.title, statuses.title
from problem_status
left join statuses on statuses.id = problem_status.status_id
order by problem_status.id;select *
from t
order by value desc
fetch first 1 rows with ties;