Akina

ALTER TABLE tablename
MODIFY COLUMN date_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
MODIFY COLUMN date_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

INSERT INTO tablename (active, user_id, value)
WITH RECURSIVE
cte AS ( SELECT 1 num
         UNION ALL
         SELECT num + 1 FROM cte WHERE num < 9999 )
SELECT 1, 1, num
FROM cte;

INSERT INTO tablename (active, user_id, value)
SELECT 1, 1, 1 + t1.num + t2.num * 10 + t3.num * 100 + t4.num * 1000 AS value
FROM       (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4;
HAVING value <= 9999;

Вопрос: как мне правильно спроектировать таблицу для связей между этими товарами. Чтобы каждый из этих товаров ссылался друг на друга?

CREATE TABLE groups_of_goods (
    group_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (group_id, product_id),
    FOREIGN KEY fk_product (product_id) REFERENCES product (product_id)
);

1. Использовать не LIKE, а функции поиска подстроки (INSTR или LOCATE)
   
2. Задать требуемый collation (вплоть до BINARY)
   

я хочу получить все проекты, у которых нет связи с id 4

SELECT p.*
FROM project p
WHERE NOT EXISTS ( SELECT NULL
                   FROM project_user u
                   WHERE p.project_id = u.project_id
                     AND u.user_id = 4 )

Есть ли разница джоинов этих двух строк, с точки зрения нагрузки на БД?

ORDER BY CASE WHEN parent_id IN (0,51,52)
              THEN 1
              ELSE 2 
              END ASC,
         CASE WHEN parent_id IN (0,51,52)
              THEN name
              ELSE ''
              END ASC,
         CASE WHEN parent_id IN (0,51,52)
              THEN ''
              ELSE name
              END DESC

with AAA(A) as (
    select 1 union 
    select 2 union 
    select 3 union 
    select 4 union 
    select 5
)
select 'А01' as "id",
       1 as "sensor",
       generate_series('2021-07-01 00:00:00', '2021-07-31 23:59:59', '10 sec'::interval) as "time",
       t.status[floor(random()* 4 + 1)::int] 
from (select array_agg(distinct AAA.A) as "status" from AAA ) as t ,
     round((random()* 400 - 200)::numeric, 2) as "value"

WITH cte AS (
    SELECT *, SUM(rating) OVER (ORDER BY id) cum_rating
    FROM table
)
SELECT
FROM cte t1
JOIN (SELECT RAND() * MAX(cum_rating) rnd_rating
      FROM cte) t2 ON t2.rnd_rating BETWEEN t1.cum_rating - t1.rating AND t1.cum_rating