#PostgreSQLRussia channel http://postgres.chat. JOIN us: http://PostgreSQLRussia.org
типа, root -> leaf1 -> leaf2 ->leaf3
1) Можно использовать модуль ltree, который специально для деревьев https://www.postgresql.org/docs/current/static/ltree.html
2) Можно просто реляции и recursive CTE, получится breadth-first search; вот простой пример http://jakub.fedyczak.net/post/postgresql-with-recursive-tree-traversing-example/ (или погуглите ещё "breadth-first search postgresql", я помню что не раз статьи были, более глубокие чем эта)
3) Есть ещё целая книжка, как в SQL работать с графами и деревьями, у Joe Celko https://datubaze.files.wordpress.com/2016/03/celkos-trees-and-hierarchies-in-sql_2004.pdf – это старый вариант, было ещё второе издание.
п.2 -- это standard ansi sql. у Celko не знаю, есть ли варианты с CTE.
п1. -- это чисто постгресовое, но довольно эффективное, т.к. с GiST-индексами.
select
*
from
"gateways_status_history"
where
"identifier" = '11:11:11:11:11:11'
or
"id" = 99
order by
"updated_at" desc
identifier
и id
stable
? https://www.postgresql.org/docs/10/static/xfunc-volatility.html
create index test on test using gin (to_json(array[col1, col2]));
but I catch an exception ERROR: functions in index expression must be marked IMMUTABLE
. How can create the index correct?
SELECT
a.stock_name AS stock_name,
a.delivery_time AS delivery_time,
a.sum_amount AS all,
r.sum_amount AS refusal,
n.sum_amount AS normal,
r.sum_amount :: real / a.sum_amount :: real * 100 AS percent
FROM (
SELECT
accepted.name as stock_name,
sum(accepted.amount) as sum_amount,
accepted.delivery_time
FROM (
SELECT
order_product_status_history.order_product_id as product,
stock.name as name,
order_product.amount AS "amount",
order_product_status_history.date_added AS date,
delivery_time as delivery_time
FROM "order_product"
LEFT JOIN "order" ON "order_product"."order_id" = "order"."id"
LEFT JOIN "customer" ON "order"."customer_id" = "customer"."id"
LEFT JOIN "customer_group" ON "customer"."customer_group_id" = "customer_group"."id"
LEFT JOIN "stock_product" ON "order_product"."stock_product_id" = "stock_product"."id"
LEFT JOIN "stock" ON "stock_product"."stock_id" = "stock"."id"
LEFT JOIN "organization" ON "order"."pvz_id" = "organization"."id"
LEFT JOIN order_product_status_history on order_product.id = order_product_status_history.order_product_id
WHERE (NOT ("order"."status" IN (8, 10)))
AND "order".date_added > '2018-07-01'::date
AND order_product_status_history.status = 14372--STATUS_ACCEPTED
) AS accepted
inner join (
SELECT
order_product_status_history.order_product_id as product,
stock.name as name,
order_product.amount AS "amount",
order_product_status_history.date_added AS date,
delivery_time as delivery_time
FROM "order_product"
LEFT JOIN "order" ON "order_product"."order_id" = "order"."id"
LEFT JOIN "customer" ON "order"."customer_id" = "customer"."id"
LEFT JOIN "customer_group" ON "customer"."customer_group_id" = "customer_group"."id"
LEFT JOIN "stock_product" ON "order_product"."stock_product_id" = "stock_product"."id"
LEFT JOIN "stock" ON "stock_product"."stock_id" = "stock"."id"
LEFT JOIN "organization" ON "order"."pvz_id" = "organization"."id"
LEFT JOIN order_product_status_history on order_product.id = order_product_status_history.order_product_id
WHERE (NOT ("order"."status" IN (8, 10)))
AND "order".date_added > '2018-07-01'::date
AND order_product_status_history.status = 4947--STATUS_IN_STOCK
) AS r ON accepted.product = r.product
group by stock_name, accepted.delivery_time
) as a
left join (
SELECT
accepted.name AS stock_name,
SUM(accepted.amount) AS sum_amount
FROM (
SELECT
order_product_status_history.order_product_id as product,
stock.name as name,
order_product.amount AS "amount",
order_product_status_history.date_added AS date,
delivery_time as delivery_time
FROM "order_product"
LEFT JOIN "order" ON "order_product"."order_id" = "order"."id"
LEFT JOIN "customer" ON "order"."customer_id" = "customer"."id"
LEFT JOIN "customer_group" ON "customer"."customer_group_id" = "customer_group"."id"
LEFT JOIN "stock_product" ON "order_product"."stock_product_id" = "stock_product"."id"
LEFT JOIN "stock" ON "stock_product"."stock_id" = "stock"."id"
LEFT JOIN "organization" ON "order"."pvz_id" = "organization"."id"
LEFT JOIN order_product_status_history on order_product.id = order_product_status_history.order_product_id
WHERE (NOT ("order"."status" IN (8, 10)))
AND "order".date_added > '2018-07-01'::date
AND order_product_status_history.status = 14372--STATUS_ACCEPTED
) AS accepted
inner join (
SELECT
order_product_status_history.order_product_id as product,
stock.name as name,
order_product.amount AS "amount",
order_product_status_history.date_added AS date,
CASE
WHEN EXTRACT (dow FROM order_product_status_history.date_added) IN (6) THEN order_product_status_history.date_added + interval '2 day'
WHEN EXTRACT (dow FROM order_product_status_history.date_added) IN (0) THEN order_product_status_history.date_added + interval '1 day'
ELSE order_product_status_history.date_added
END as dw,
delivery_time as delivery_time
FROM "order_product"
LEFT JOIN "order" ON "order_product"."order_id" = "order"."id"
LEFT JOIN "customer" ON "order"."customer_id" = "customer"."id"
LEFT JOIN "customer_group" ON "customer"."customer_group_id" = "customer_group"."id"
LEFT JOIN "stock_product" ON "order_product"."stock_product_id" = "stock_product"."id"
LEFT JOIN "stock" ON "stock_product"."stock_id" = "stock"."id"
LEFT JOIN "organization" ON "order"."pvz_id" = "organization"."id"
LEFT JOIN order_product_status_history on order_product.id = order_product_status_history.order_product_id
WHERE (NOT ("order"."status" IN (8, 10)))
AND "order".date_added > '2018-07-01'::date
AND order_product_status_history.status = 4947--STATUS_IN_STOCK
) AS r ON accepted.product = r.product
WHERE EXTRACT(EPOCH FROM (r.dw - accepted.date)) / 60 / 60 <= accepted.delivery_time + 6
group by stock_name
) as n on a.stock_name = n.stock_name
inner join (
SELECT
accepted.name AS stock_name,
SUM(accepted.amount) AS sum_amount
from (
SELECT
order_product_status_history.date_added AS date,
delivery_time as delivery_time
FROM "order_product"
LEFT JOIN "order" ON "order_product"."order_id" = "order"."id"
LEFT JOIN "customer" ON "order"."customer_id" = "customer"."id"
LEFT JOIN "customer_group" ON "customer"."customer_group_id" = "customer_group"."id"
LEFT JOIN "stock_product" ON "order_product"."stock_product_id" = "stock_product"."id"
LEFT JOIN "stock" ON "stock_product"."stock_id" = "stock"."id"
LEFT JOIN "organization" ON "order"."pvz_id" = "organization"."id"
LEFT JOIN order_product_status_history on order_product.id = order_product_status_history.order_product_id
WHERE (NOT ("order"."status" IN (8, 10)))
AND "order".date_added > '2018-07-01'::date
AND order_product_status_history.status = 14372--STATUS_ACCEPTED
) AS accepted
inner join (
SELECT
order_product_status_history.order_product_id as product,
stock.name as name,
order_product.amount AS "amount",
order_product_status_history.date_added AS date,
CASE
WHEN EXTRACT (dow FROM order_product_status_history.date_added) IN (6) THEN order_product_status_history.date_added + interval '2 day'
WHEN EXTRACT (dow FROM order_product_status_history.date_added) IN (0) THEN order_product_status_history.date_added + interval '1 day'
ELSE order_product_status_history.date_added
END as dw,
delivery_time as delivery_time
FROM "order_product"
LEFT JOIN "order" ON "order_product"."order_id" = "order"."id"
LEFT JOIN "customer" ON "order"."customer_id" = "customer"."id"
LEFT JOIN "customer_group" ON "customer"."customer_group_id" = "customer_group"."id"
LEFT JOIN "stock_product" ON "order_product"."stock_product_id" = "stock_product"."id"
LEFT JOIN "stock" ON "stock_product"."stock_id" = "stock"."id"
LEFT JOIN "organization" ON "order"."pvz_id" = "organization"."id"
LEFT JOIN order_product_status_history on order_product.id = order_product_status_history.order_product_id
WHERE (NOT ("order"."status" IN (8, 10)))
AND "order".date_added > '2018-07-01'::date
AND order_product_status_history.status = 4947--STATUS_IN_STOCK
) AS r ON accepted.product = r.product
where extract(epoch from (r.dw - accepted.date)) / 60 / 60 > accepted.delivery_time + 6
group by stock_name
) as r on a.stock_name = r.stock_name
вопрос
CREATE TYPE someType AS
(
sometext character varying(128),
somenum smallint,
);
CREATE TABLE "sometable "
(
id uuid NOT NULL default uuid_generate_v4() PRIMARY KEY,
someField someType
)
можно ли делать параметризированый инсерт вот так?
insert into sometable (someCompositeField) values ($1)
и если да то что должно быть в виде параметра ? массив, Josn или строка row('text', 123)
Вопрос:
кручу psql через docker-compose, вот .yml:
version: '3'
services:
postgres:
image: 'postgres'
ports:
- "5432:5432"
environment:
- POSTGRES_DB=db_2020
- POSTGRES_USER=dbuser_2020
- POSTGRES_PASSWORD=minecraft2000
вот настройки джанго, джанго не в контейнере, просто на системе :
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'db_2020',
'USER': 'db_user2020',
'PASSWORD': 'minecraft2000',
'HOST': '0.0.0.0',
'PORT': '5432',
}
джанго пишет : django.db.utils.OperationalError: FATAL: password authentication failed for user "db_user2020"
Может, кто подскажет чего?