CREATE TABLE "TankContentStateHistory" (
"TankContentStateHistoryId" serial NOT NULL,
"AverageTemperature" double precision NOT NULL,
"GTVolume" double precision DEFAULT 0.0 NOT NULL
);
INSERT INTO "TankContentStateHistory" ("AverageTemperature", "GTVolume") VALUES (10, 20);
SELECT * FROM "TankContentStateHistory";select string_agg(substring, '') from (
SELECT
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', ceil(random() * 26)::integer, 1)
FROM generate_series(1,12) -- 12 capital letters
union
SELECT
substring('abcdefghijklmnopqrstuvwxyz', ceil(random() * 26)::integer, 1)
FROM generate_series(1,12) -- 12 low case letters
union
SELECT
substring('0123456789', ceil(random() * 10)::integer, 1)
FROM generate_series(1,6) -- 6 digits
union
SELECT
substring('!#$%&()*+,-./:;<=>?@[]^', ceil(random() * 23)::integer, 1)
FROM generate_series(1,2) -- 2 other chars
) seria;create extension if not exists pgcrypto;
select encode(gen_random_bytes(32),'base64');select
portfolio.id, portfolio.name,
array_agg(json_build_object('src', photos.src, 'src_build', photos.src_build))
from portfolio
left join photos on portfolio.id = photos.portfolio_id
group by portfolio.id, portfolio.name
;SELECT "complex"."id", "complex"."rate", MIN("flat"."price_call") "min_flat_price_call"
FROM "flat"
LEFT JOIN "complex" ON "flat"."complex_id" = "complex"."id"
LEFT JOIN "building" ON "flat"."building_id" = "building"."id"
LEFT JOIN "section" ON "flat"."section_id" = "section"."id"
GROUP BY "complex"."id", "complex"."rate"
ORDER BY
"complex"."rate" DESC,
"min_flat_price_call",
"complex"."room_price_min"
LIMIT 30;insert into products values (1, '2021-05-01', 150)
on conflict(id) do update
set
date = case when excluded.date > products.date then excluded.date else products.date end,
price = case when excluded.date > products.date then excluded.price else products.price end;SELECT
COUNT(case when d.created_at > NOW() - interval '7 days' then 1 else null end) as "7 days",
COUNT(case when d.created_at > NOW() - interval '14 days' then 1 else null end) as "14 days",
COUNT(case when d.created_at > NOW() - interval '1 month' then 1 else null end) as "1 month"
c.short_name
FROM documents_owners dow
INNER JOIN contractors_main c ON dow.contractor_id = c.id
INNER JOIN documents_main d ON dow.document_id = d.id
GROUP BY c.short_name
ORDER BY COUNT(dow.document_id) DESCSELECT
pn.country, array_agg(s.name) services
FROM number pn
CROSS JOIN service s
LEFT JOIN buy_number bn ON bn.numberId = pn.id AND bn.serviceId = s.id
WHERE bn.id is NULL
GROUP BY pn.country;select
id,
name,
quantiy
from (
select
test.*,
avg(quantiy) over() as average
from test
) average_test
where quantiy > average;select
sum(amount)
from
records
where
user_id = 1
and date_trunc('month', date) = date_trunc('month', to_date('2021-05-03', 'YYYY-MM-DD'));SELECT
to_timestamp(('1591044532022001'::bigint)/1000000) ts1,
to_timestamp((regexp_replace('1 591 044 464 658 000,00', '[\s,]','','g')::bigint)/100000000) ts2select * from test
join (
select "hash" from test where "on" order by random() limit 1
) t on t.hash = test.hash;SELECT
jobs.job_title,
AVG(employees.salary) average_salary
FROM
employees
JOIN jobs ON employees.job_id = jobs.job_id
WHERE
jobs.job_title Like '%Manager'
GROUP BY jobs.job_title
HAVING AVG(employees.salary) > 10000;SELECT
COALESCE(departments.department_name, 'Total') AS department_name,
COUNT(*) as Count_employees
FROM
employees
JOIN departments ON employees.department_id = departments.department_id
GROUP BY
ROLLUP(departments.department_name)
ORDER BY departments.department_name;create table test (
col varchar(64)
);
insert into test
values
('ЧС.1.1'),
('ЧС.1.2'),
('ЧС.1.3.1'),
('ЧС.1.10'),
('ЧС.1.11.1'),
('П.1.1'),
('П.1.2'),
('П.10.2'),
('П.2.10');
select
col
from
test
order by
(string_to_array(col, '.'))[1],
(string_to_array(col, '.'))[2]::int,
(string_to_array(col, '.'))[3]::int;select col from (
select
col, string_to_array(col, '.') arr
from
test
) tbl
order by
arr[1],
arr[2]::int,
arr[3]::int;