Рубрика: postgressql

В этом разделе отображаются статьи, которые связаны с тегом postgreesql. Решения проблем, методы исправления ошибок, построение запросов.

PostgreSQL: объединение результатов двух запросов по ключевым полям

postgressql

Задача: есть две таблицы, из которых получаются данные сгруппированные по городу и населенному пункту. Необходимо их объединить в один результирующий запрос

Решение: оказывается вполне возможно объединить две выборки посредством LEFT/INNER JOIN. Если схематично, то примерно так:

select
    s1.cnt_all,s2.cnt_no,s1.city,s1.settler
from (select * from aaa) as s1
inner join ((select * from bbb) as s2) on (s1.city=s2.city and s1.settler=s2.settler)

Ну или мой результирующий:

select 
	s1.cnt_all,
	case when s2.cnt_pilot is null then 0 else s2.cnt_pilot end as cnt_pilot, 
	s1.city,
	s1.settler 
from (
	SELECT 
		count(posting_addresses.id) as cnt_all,
		ls_addresses.city,
		ls_addresses.settler 
	FROM posting_addresses
		inner join ls_addresses on ls_addresses.ls=posting_addresses.ls and ls_addresses.address_type=1
		inner join divisions on divisions.area=ls_addresses.area and divisions.id=12	
	where posting_addresses.period='2026-01-01'  
	group by
		ls_addresses.city,
		ls_addresses.settler
	order by cnt_all desc
) as s1
left join (
SELECT 
	count(refusals.id) as cnt_pilot,
	ls_addresses.city,
	ls_addresses.settler 
FROM refusals 
inner join ls_addresses on ls_addresses.ls=refusals.ls and ls_addresses.address_type=1
inner join divisions on divisions.area=ls_addresses.area and divisions.id=12 
where refusals.dt ='2026-01-01'  
group by
	ls_addresses.city,
	ls_addresses.settler
order by cnt_pilot desc
) as s2 on (s1.city=s2.city and s1.settler=s2.settler)
Оставить комментарий ,

PostgreSQL и выборка из полей содержащих JSON

postgressql

Postgre позволяет хранить данные в JSON с колонках с типами json и jsonb, которые по сути отличаются только способом хранения. json — в виде текста (хорошо видно визуально, можно обрабатывать запросы как с «текстом») и jsonb — в виде сжатых бинарных данных (занимает меньше места, можно индексировать. Причем если данные хранятся в виде jsonb, то порядок сохранения ключей в JSON не гарантируется. В принципе вполне удобно хранить так данные, если не возможно заранее определить точно структуру таблицы.

Ну и ниже пример работы с такими данными.

Вставка:

insert into epd_expansion (period,ls,orgid,parties) values ('2025-10-01',1,2,'[4,5,6]')

Выборка:

SELECT * FROM public.epd_expansion where parties is not null;

А вот так например, как можно например сделать выборку всех строк, имеющих в массиве partise число 3:

SELECT * FROM public.epd_expansion where parties@>'[3]'::jsonb;

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

PostgreSQL: имя таблицы как параметр функции

postgressql

Штатно в PostgreSQL я не нашел способа передавать имя таблицы как переменная у функции. Однако есть возможность это обойти. Например так:

CREATE OR REPLACE FUNCTION public.SetReceived(busname varchar ,idr integer)
	RETURNS boolean
	LANGUAGE plpgsql
AS $function$
	BEGIN
		EXECUTE format(concat('update ',busname,' set received=true where id=',idr));		
		return true;
	END;
$function$
Оставить комментарий , ,

PostgreeSQL: нечеткое сравнение в триггере

postgressql

Ну собственно синтаксис такой-же как и в запросах: оператор LIKE. Соответственно триггер с не чётким сравнением может выглядеть например так:

CREATE OR REPLACE FUNCTION public.on_insert_ls()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$BEGIN
 NEW.id=nextval('ls_id_seq'::regclass);
  if new.ls like '22%' then 
	new.is_epd=true;
   else new.is_epd=false;
  end if;
 RETURN NEW;
END;$function$

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

Перенос БД с MySQL на Postgresql

postgressql

Как то уже описывал в одном из постов, что в одной из инсталяций внедрял WordPress на основе БД PostreeSQL. И в принципе спустя год могу сказать что особых проблем пока не возникло. Кроме одного НО! Если требуется какой-то установить плагин, то чаще всего он просто так, без некоторой адаптации не заработает. Вот сейчас адаптирую плагин «WP Voting Contest Lite«, который можно использовать для «выбора лучшего фото» пользователями.

В данном случае столкнулся для начала с проблемой его установки. При активации он пытается создать, если отсутствуют таблицы:

  • wp_votes_tbl
  • wp_votes_post_contestant_track
  • wp_votes_custom_registeration_contestant
  • wp_votes_custom_field_contestant

В которых встречается имеющийся в MySQL, но отсутствующий в PostgreSQL тип ENUM. Ну чтож.. создадим эти типы «руками», благо такая возможность в PostgreSQL предусмотрена:

CREATE TYPE public.admin_only_enum AS ENUM ('Y','N');
CREATE TYPE public.grid_only_enum AS ENUM ('Y','N');
CREATE TYPE public.list_only_enum AS ENUM ('Y','N');	
CREATE TYPE public.pretty_view_enum AS ENUM ('Y','N');	
CREATE TYPE public.question_type_enum AS ENUM ('TEXT','TEXTAREA','MULTIPLE','SINGLE','DROPDOWN','FILE','DATE');	
CREATE TYPE public.required_enum AS ENUM ('Y','N');	
CREATE TYPE public.set_limit_enum AS ENUM ('Y','N');	
CREATE TYPE public.show_labels_enum AS ENUM ('Y','N');

И далее создать таблицы по типу:

CREATE TABLE public.wp_votes_custom_registeration_contestant (
	id bigserial NOT NULL,
	"sequence" int4 NULL,
	question_type public.question_type_enum NULL,
	question text NULL,
	system_name varchar(45) NULL,
	response text NULL,
	required public.required_enum NULL,
	required_text text NULL,
	admin_only public.admin_only_enum NULL,
	delete_time varchar(45) NULL,
	react_val text NULL,
	wp_user int4 NULL,
	CONSTRAINT wp_votes_custom_registeration_contestant_pk PRIMARY KEY (id)
);
Оставить комментарий
1 2