PostgresStudy2025/homework/0.sql

begin;

-- Создаем таблицу measurement_batch
create table if not exists measurement_batch(id serial primary key, start_period timestamp, username varchar(64), pos_x numeric, pos_y numeric);
-- Создаем таблицу measurement_types
create table if not exists measurement_types (id serial primary key, name varchar(64));
-- Создаем таблицу measurement_params
create table if not exists measurement_params(id serial primary key, measurement_type_id integer references measurement_types(id), measurement_batch_id integer references measurement_batch(id), height numeric, temperature numeric, pressure numeric, wind_speed numeric, wind_direction numeric, bullet_speed numeric);
-- Добавляем тестовые типы измерений
insert into measurement_types(name) values ('ДМК');
insert into measurement_types(name) values ('РУЖЬЕ');
-- Добавляем тестовые измерения
insert into measurement_batch(start_period, username, pos_x, pos_y) values(now(), 'test', 69, 42);
-- Добавляем тестовые данные замеров
insert into measurement_params(measurement_type_id, measurement_batch_id, height, temperature, pressure, wind_speed, wind_direction, bullet_speed) values (1, 1, 100, 15, 750, 0, 0, 0);

commit;

select mtypes.name, batch.start_period, batch.username, batch.pos_x, batch.pos_y, params.* from measurement_params params join measurement_batch batch on params.measurement_batch_id = batch.id join measurement_types mtypes on params.measurement_type_id = mtypes.id;