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;date_label >= '1994-01-01' and date_label < '1995-01-01'ModelName::find()->where(['between', 'date_label', '1994-01-01', '1995-01-01' ]);php7.1-mysql_7.1.12-2+ubuntu17.10.1+deb.sury.org+2_amd64.deb
php7.1-mysql_7.1.12-1+ubuntu17.10.1+deb.sury.org+1_amd64.deb
disabled и date_added - в таблице announcements
SELECT `announcements`.* FROM `announcements`
WHERE `disabled` = 0
ORDER BY `date_added` DESC
LIMIT 15select i from unnest(array[1,2,3,4]) as i
where not exists (select 1 from tablename where id = i);with
data (new_user_id, new_name) as (values(%(user_id)s, %(name)s)),
wr as (
INSERT INTO users(user_id, name)
select new_user_id, new_name from data
ON CONFLICT (user_id) DO NOTHING returning user_id
), upd as (
update users set name = new_name from data where users.user_id = data.new_user_id and data.new_user_id not in (
select user_id from wr
)
)
select count(*) from wr;$array_col2[$i] = ($array_col1[$i] + $D * mt_rand(-1,1));curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_INFILE, fopen('pathtofile', 'rb'));WITH new_tbl_main( id ) AS (
INSERT INTO tbl_main ( institution_id ) VALUES ( 38)
RETURNING id
), chi2 as (
INSERT INTO tbl_child1 ( tbl_main_id )
SELECT new_tbl_main.id FROM new_tbl_main
)
INSERT INTO tbl_child1 ( tbl_main_id )
SELECT new_tbl_main.id FROM new_tbl_main
RETURNING tbl_main_idERROR: 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 sqlЕсть у постгреса оператор &&
select oprkind, l.typname, oprname, r.typname from pg_operator join pg_type l on oprleft = l.oid join pg_type r on oprright = r.oid where oprname = '&&';
oprkind | typname | oprname | typname
---------+-----------+---------+-----------
b | box | && | box
b | polygon | && | polygon
b | tinterval | && | tinterval
b | circle | && | circle
b | inet | && | inet
b | tsquery | && | tsquery
b | anyarray | && | anyarray
b | anyrange | && | anyrangesudo lsof -a +L1COMMAND PID USER FD TYPE DEVICE SIZE/OFF NLINK NODE NAME
php 17455 melkij 4r REG 254,1 1048576000 0 2624572 /home/melkij/tmpfile (deleted)select sum(amount) from (
select avg(/**/) as amount
/* ... */
) totalFor 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-сервер не позволяет добавлять такие ограничения на таблицы
могут ли гарантировать триггеры целостность данных?
Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result
select /*payload*/ from user
where rank_golos >0
order by rank_golos >=22.1 desc,
if (rank_golos >=22.1, rank_yearnub, rank_golos) desc,
if (rank_golos >=22.1, rank_golos, rank_yearnub) descselect ticket_number , min(some_id), max(some_id) from tablename group by ticket_number