2. Нужно ли включать order by в таких огромных таблицах?
3. Ну типа берем сначала вытаскиваем данные с одной таблицы, потом по результату смотрим id, по нему вытаскиваем данные связанные со второй таблицы, затем смотрим новый результат и так же вытаскиваем с третьей. Получается в бд не будет строится огромная временная таблица из трех больших таблиц.
Во-первых неясно пароль какого пользователя запрашивается?
Какие запросы к бд mysql можно считать быстрыми и какие медленными?
select day, data
from generate_series('2017-12-10', '2017-12-12', interval '1 day') as day,
lateral (
select data from tablename
where "timestamp" between day and day + interval '1 day'
order by "timestamp" desc limit 1
) ljd;with recursive t as (
(select "timestamp"::date as day, data from tablename order by "timestamp" desc limit 1)
union all
select bpt.* from t, lateral (
select "timestamp"::date as day, data from tablename where "timestamp" < t.day order by "timestamp" desc limit 1
) as bpt
)
select * from t;ERROR: cannot use RETURN QUERY in a non-SETOF function
CREATE OR REPLACE FUNCTION checkPermission(text, text) RETURNS BOOLEAN AS
$$
BEGIN
RETURN $1 && $2;
END;
$$ LANGUAGE plpgsqlCREATE OR REPLACE FUNCTION checkPermission(text, text) RETURNS BOOLEAN AS
$$
SELECT $1 && $2;
$$ LANGUAGE sqlFor timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
но как переменная current_amount попадет внутрь триггера?
либо ограничить инсерты\апдейты (разрешив их только вызовами изнутри sql-сервера), но мне кажется, что sql-сервер не позволяет добавлять такие ограничения на таблицы
могут ли гарантировать триггеры целостность данных?
select ticket_number , min(some_id), max(some_id) from tablename group by ticket_numberTable aliases in a multiple-table DELETE should be declared only in the table_references part of the statement. Elsewhere, alias references are permitted but not alias declarations.
delete t from test as t where t.i = 100;
select org from tablename group by org having count(distinct active) > 1;The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables.
UPDATE "TABLE1"
SET
"Value2" = (NOT EXISTS(
SELECT NULL
FROM "TABLE2"
WHERE "что-то" = "кое-что"
)
AND NOT EXISTS(
SELECT NULL
FROM "TABLE3"
WHERE "что-то" = "кое-что"
))
WHERE "кое-что" = ANY ($1 :: INT [])postgres=# create schema garage;
CREATE SCHEMA
postgres=# CREATE TABLE garage.users
(
car_id text COLLATE pg_catalog."default"
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
CREATE TABLE
postgres=# insert into garage.users values ('1'), ($$'1'$$), (null);
INSERT 0 3
postgres=# select car_id, car_id is null from garage.users ;
car_id | ?column?
--------+----------
1 | f
'1' | f
| t