не знаю должен ли там быть postmaster. Особо не шарю в администрировании. Может 5432 занят думаю чем то. фиг знает
не удалось подключиться к серверу: Нет маршрута до узла
Куда добавить этот запрос?
BEGIN;
INSERT ...;
UPDATE ...;
COMMIT;
...
LEFT JOIN "readed_news" "rn"
ON ("rn"."newsId" = "news"."id" )
...
...
AND id NOT IN (
SELECT newsId FROM readed_news WHERE userId = 5
)
Only query values should be bound via this method: it shouldn’t be used to merge table or field names to the query (Psycopg will try quoting the table name as a string value, generating invalid SQL). If you need to generate dynamically SQL queries (for instance choosing dynamically a table name) you can use the facilities provided by the psycopg2.sql module:
>>> cur.execute("INSERT INTO %s VALUES (%s)", ('numbers', 10)) # WRONG >>> cur.execute( # correct ... SQL("INSERT INTO {} VALUES (%s)").format(Identifier('numbers')), ... (10,))
created_at и кстати какой лучше выбрать тип для хранения времени если время хранится в стиле 1606208106781
books и вот тут должен быть список книг пользователя их количество может быть от 0 до 100
как сделать так чтобы делая запрос чтобы получить информацию о пользователе я еще получал информацию еще о книгах
SELECT u.*, b.*
FROM users u
LEFT JOIN books b ON (b.id = ANY(u.books))
WHERE ...
name | surname | balance | created_at | books | id | title | author
______________________________________________________________________________________________________________
Alex | Smith | 22.10 | 1606349583252 | {2,10,88} | 2 | A Game of Thrones | George R. R. Martin
Alex | Smith | 22.10 | 1606349583252 | {2,10,88} | 10 | A Clash of Kings | George R. R. Martin
Alex | Smith | 22.10 | 1606349583252 | {2,10,88} | 88 | A Storm of Swords | George R. R. Martin
sales
------------------
datetime
client_first_name
client_last_name
client_email
client_region
client_city
client_age
client_gender
product_name
product_category
product_type
product_manufacturer
product_price
quantity
shop_id
shop_region
shop_city
seller_first_name
seller_last_name
seller_department
...
SELECT client_region, client_gender, client_age, product_category, product_type, shop_id, SUM(quantity), SUM(product_price*quantity)
FROM sales
GROUP BY client_region, client_gender, client_age, product_category, product_type, shop_id
SELECT ...
FROM table1 JOIN table2 ...
JOIN (
VALUES (123, ARRAY[123, 789]),
(345, ARRAY[345])
) t (id, aliases) ON (table1.userid = ANY(t.aliases))
...
SELECT t.id, string_agg(tt.data, ',')
FROM
(
VALUES (123, 'a'),
(123, 'aa'),
(789, 'b'),
(345, 'c'),
(345, 'd')
) tt (userid, data)
JOIN
(
VALUES (123, ARRAY[123, 789]),
(345, ARRAY[345])
) t (id, aliases) ON (tt.userid = ANY(t.aliases))
GROUP by t.id
select array_agg(v)
from (select jsonb_array_elements(col)->'a' v
from (values ('[{"a": 1, "b": 2}, {"a": 10, "b": 20}]'::jsonb)) as t(col)
) t1
SELECT SUM (CASE WHEN status = 1 THEN amount END) as sum1, ... FROM ... GROUP BY ..
SET search_path TO test, public;
select distinct pbx_call_id,
first_value(clid) over (partition by pbx_call_id order by callstart) clid,
... -- остальные поля
from calls;
select pbx_call_id,
jsonb_agg(json_build_object('disposition', disposition, 'sip', sip)) as sips
from calls
group by pbx_call_id
filterParams.Append("Relationship", $"\"Relationship\" @> @Relationship::jsonb", ....);