Архивы postgressql

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

22.10.2025 Павел Грибов 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]')

1	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;

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

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

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

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

Оставить комментарий jsonb, выборка, хранение json

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

06.10.2025 Павел Грибов 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$

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$

Оставить комментарий postgressql, имя таблицы, параметр функции

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

13.08.2025 Павел Грибов 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$

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$

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

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

27.11.2024 Павел Грибов 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 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)
);

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)

);

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

PosgreSQL: Разница дат

28.12.2023 Павел Грибов postgressql

Разницу дат в месяцах в запросе можно получить например так:

select extract(month from age(now(), '2023-11-01'))

1	select extract(month from age(now(), '2023-11-01'))

Соответственно в днях, часах, годах и т.п. можно получить подставив вместо month: day, hour, year и т.д.

Оставить комментарий age, postgresql, разница дат

1 2 »

Пн	Вт	Ср	Чт	Пт	Сб	Вс
		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