Архивы postgree

PostgreeSQL: контроль уникальности вставки

22.11.2021 Павел Грибов MySQL

Хороший вариант: возложить контроль уникальности вставляемых данных, на уровень БД. Если это по каким-то причинам не возможно, то можно попробовать такой вариант:

insert into users_groups_filling (user_id,user_group) 
select  :user_id,:user_group 
where 
 NOT EXISTS (
	 SELECT id from users_groups_filling where user_id=:user_id and user_group=:user_group
 )

insert into users_groups_filling (user_id,user_group)

select :user_id,:user_group

where

NOT EXISTS (

SELECT id from users_groups_filling where user_id=:user_id and user_group=:user_group

)

Оставить комментарий postgree, контроль уникальности

PostgreeSQL: INSERT or UPDATE в запросе

27.10.2021 Павел Грибов WEB

В Postgree очень удобно реализовывать механизм защиты от избыточной вставки одинаковых значений.

Назначаем на колонки таблицы контроль уникальности

 CONSTRAINT control_un_key UNIQUE (dtfrom_utc)
        INCLUDE(tar_id,user_group),

1 2	CONSTRAINT control_un_key UNIQUE (dtfrom_utc) INCLUDE(tar_id,user_group),

2. Далее при вставке достаточно указать что то в духе:

INSERT INTO tars_list
                ( dtfrom_utc, tar_id, user_group)
         VALUES (:dtfrom_utc,:tar_id,:user_group) ON CONFLICT  DO NOTHING

INSERT INTO tars_list

( dtfrom_utc, tar_id, user_group)

VALUES (:dtfrom_utc,:tar_id,:user_group) ON CONFLICT DO NOTHING

Одно плохое НО. Проверка уникальности не будет работать, если в полях есть null

Оставить комментарий insert or update, postgree, защита от дублей, контроль уникальности

SQL: выборка последних значений по дате из БД

15.10.2021 Павел Грибов MySQL

Задача: в таблице хранятся тарифы «сплошным навалом», необходимо вытащить последние значения всех тарифов.

Решение:

select 
reg_tarifs.date as dttime,
reg_tarifs.tarif as tarif,
reg_tarifs.zone as zone_num,
enum_zones.name as tarzone
from reg_tarifs
inner join enum_zones on enum_zones.id=reg_tarifs.zone
inner join 
(select 
  max(date) as maxdt,zone 
from reg_tarifs
where ezs=5
group by  zone 
) as max
on max.maxdt=reg_tarifs.date and max.zone=reg_tarifs.zone

select

reg_tarifs.date as dttime,

reg_tarifs.tarif as tarif,

reg_tarifs.zone as zone_num,

enum_zones.name as tarzone

from reg_tarifs

inner join enum_zones on enum_zones.id=reg_tarifs.zone

inner join

(select

max(date) as maxdt,zone

from reg_tarifs

where ezs=5

group by zone

) as max

on max.maxdt=reg_tarifs.date and max.zone=reg_tarifs.zone

Оставить комментарий ds, postgree, выборка последних по дате

PostgreSQL: расстояние между двумя координатами в метрах

04.10.2021 Павел Грибов WEB

Можно добавить функцию вида:

CREATE OR REPLACE FUNCTION public.distance(
	lat1 double precision,
	lon1 double precision,
	lat2 double precision,
	lon2 double precision)
    RETURNS double precision
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    R integer = 6371e3; -- Метры
    rad double precision = 0.01745329252;

    f1 double precision = lat1 * rad;
    f2 double precision = lat2 * rad;
    df double precision = (lat2-lat1) * rad;
    dl double precision = (lon2-lon1) * rad;

    a double precision = sin(df/2) * sin(df/2) + cos(f1) * cos(f2) * sin(dl/2) * sin(dl/2);
    c double precision = 2 * atan2(sqrt(a), sqrt(1-a));    
BEGIN                                                     
    RETURN R * c;        
END
$BODY$;

CREATE OR REPLACE FUNCTION public.distance(

lat1 double precision,

lon1 double precision,

lat2 double precision,

lon2 double precision)

RETURNS double precision

LANGUAGE 'plpgsql'

COST 100

VOLATILE PARALLEL UNSAFE

AS $BODY$

DECLARE

R integer = 6371e3; -- Метры

rad double precision = 0.01745329252;

f1 double precision = lat1 * rad;

f2 double precision = lat2 * rad;

df double precision = (lat2-lat1) * rad;

dl double precision = (lon2-lon1) * rad;

a double precision = sin(df/2) * sin(df/2) + cos(f1) * cos(f2) * sin(dl/2) * sin(dl/2);

c double precision = 2 * atan2(sqrt(a), sqrt(1-a));

BEGIN

RETURN R * c;

END

$BODY$;

Оставить комментарий postgree, расстояние между точками

PostGree: удаление всех таблиц в БД

21.09.2021 Павел Грибов WEB

Самый простой способ, в консоли зайти в БД и выполнить скрипт:

DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

DO $$ DECLARE

r RECORD;

BEGIN

FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP

EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';

END LOOP;

END $$;

Оставить комментарий drop all tables, postgree

Пн	Вт	Ср	Чт	Пт	Сб	Вс
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31