Re: [GENERAL] Negative numbers to DOMAIN casting
Hi Tom, thank you for the explanation. Regards, Matija Lesar On 14 December 2016 at 15:53, Tom Lane wrote: > Matija Lesar writes: > > I have uint4 domain created like this: > > CREATE DOMAIN uint4 AS int8 > >CHECK(VALUE BETWEEN 0 AND 4294967295); > > > If I try to cast negative number to this domain check constraint is not > > validated: > > SELECT -1::uint4, pg_typeof(-1::uint4), 1::uint4, pg_typeof(1::uint4); > > :: binds tighter than minus, so you would need to write these like > "(-1)::uint4" to get the behavior you're expecting. See > > https://www.postgresql.org/docs/9.5/static/sql-syntax- > lexical.html#SQL-PRECEDENCE > > regards, tom lane >
[GENERAL] Negative numbers to DOMAIN casting
Hi, I have uint4 domain created like this: CREATE DOMAIN uint4 AS int8 CHECK(VALUE BETWEEN 0 AND 4294967295); If I try to cast negative number to this domain check constraint is not validated: SELECT -1::uint4, pg_typeof(-1::uint4), 1::uint4, pg_typeof(1::uint4); ?column? | pg_typeof | uint4 | pg_typeof --+---+---+--- -1 | bigint| 1 | uint4 Also the pg_typeof returns bigint, but if i do int8 to int2 conversion pg_typeof returns right type: SELECT pg_typeof(-1::int4::int2); pg_typeof --- smallint If I put number inside brackets I get check error: select (-1)::uint4; ERROR: value for domain uint4 violates check constraint "uint4_check" The same error is thrown if I use CAST: SELECT CAST(-1 AS uint4); ERROR: value for domain uint4 violates check constraint "uint4_check" And also if domain is used in table then check is also working as expected: CREATE TABLE test(i uint4); INSERT INTO test VALUES(-1); ERROR: value for domain uint4 violates check constraint "uint4_check" I tested this on PG 9.4 and 9.5. Is this expected behavior? Regards, Matija Lesar
Re: [GENERAL] first_value/last_value
On 19 May 2016 at 05:04, Tom Smith wrote: > It would really save all the troubles for many people if postgresql has a > built-in first/last function along with sum/avg. > There is already a C extension and a wiki sample and implemented for > window function. > I am curious why these two functions were not added along their window > implementation counter part, > for completness and consistency > > > On Wed, May 18, 2016 at 10:42 PM, Melvin Davidson > wrote: > >> >> >> On Wed, May 18, 2016 at 10:36 PM, Adam Brusselback < >> adambrusselb...@gmail.com> wrote: >> >>> Here is an example that works in a single query. Since you have two >>> different orders you want the data back in, you need to use subqueries to >>> get the proper data back, but it works, and is very fast. >>> >>> CREATE TEMPORARY TABLE foo AS >>> SELECT generate_series as bar >>> FROM generate_series(1, 100); >>> >>> CREATE INDEX idx_foo_bar ON foo (bar); >>> >>> >>> SELECT * >>> FROM ( >>> SELECT bar >>> FROM foo >>> ORDER BY bar asc >>> LIMIT 1 >>> ) x >>> UNION ALL >>> SELECT * >>> FROM ( >>> SELECT bar >>> FROM foo >>> ORDER BY bar desc >>> LIMIT 1 >>> ) y; >>> >>> DROP TABLE foo; >>> >> >> Seems to me SELECT min(), max() FROM deja.vu ; would >> also work. >> >> >> -- >> *Melvin Davidson* >> I reserve the right to fantasize. Whether or not you >> wish to share my fantasy is entirely up to you. >> > > You can always create your aggregate function for this. Here is example for getting non null first and last value: CREATE OR REPLACE FUNCTION firstval_sfunc(anyelement, anyelement) returns anyelement language sql as $BODY$ SELECT CASE WHEN $1 is NULL THEN $2 ELSE $1 END; $BODY$; CREATE AGGREGATE myfirstval(anyelement) ( SFUNC = firstval_sfunc, STYPE = anyelement ); CREATE OR REPLACE FUNCTION lastval_sfunc(anyelement, anyelement) returns anyelement language sql as $BODY$ SELECT CASE WHEN $2 is NULL THEN $1 ELSE $2 END; $BODY$; CREATE AGGREGATE mylastval(anyelement) ( SFUNC = lastval_sfunc, STYPE = anyelement ); Outputs: select myfirstval(b), mylastval(b) from unnest(array[3,2,null,12,-1]::int[]) b; myfirstval | mylastval +--- 3 |-1 select myfirstval(b order by b), mylastval(b order by b) from unnest(array[3,2,null,12,-1]::int[]) b; myfirstval | mylastval +--- -1 |12 select myfirstval(b), mylastval(b) from generate_series(10,2) as b; myfirstval | mylastval +--- 10 | 2 select myfirstval(b), mylastval(b) from unnest(array['c','b','t','x']::text[]) b; myfirstval | mylastval +--- c | x Bye, Matija Lesar
Re: [GENERAL] TABLESAMPLE usage
On 25 January 2016 at 09:55, Tom Smith wrote: > Thanks, the solution would work for fixed interval timestamp. > But the data I am dealing with has irregular timestamp so can not be > generated with exact steps. > > I would consider this a special case/method of random sampling, evenly > distributed sampling according to the defined timestamp index. > > On Mon, Jan 25, 2016 at 3:48 AM, Vik Fearing wrote: > >> On 01/25/2016 05:09 AM, Tom Smith wrote: >> > Hello: >> > >> > I have a big table with that is always appended with new data with a >> unique >> > sequence id (always incremented, or timestamp as unique index) each >> row. >> > I'd like to sample, say 100 rows out of say 1000 rows evently across all >> > the rows, >> > so that it would return rows of1, 101, 201, 301you get idea. >> > can TABLESAMPLEget one row for every 100 rows, based on the order >> > of the rows added to table using the timestamp as already indexed/sorted >> > sequence >> >> No, TABLESAMPLE is intended to take a random sampling of the data using >> various methods. >> >> You're looking for something more like this: >> >> select t.* >> from generate_series(1, (select max(id) from t), 100) g >> join t on t.id = g; >> -- >> Vik Fearing +33 6 46 75 15 36 >> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support >> > > Hi, you can accomplish this with row_number() <http://www.postgresql.org/docs/8.4/static/functions-window.html#FUNCTIONS-WINDOW-TABLE> : WITH data_cte as ( SELECT id, clock_timestamp() as ctimestamp FROM generate_series(1,1000) as id ) SELECT * FROM (SELECT id, ctimestamp, row_number() OVER (ORDER BY ctimestamp) as rownum FROM data_cte ) as data_withrownumbers WHERE rownum%100=1; Bye, Matija Lesar
Re: [GENERAL] Unexpected array_remove results
On 20 March 2015 at 14:58, Tom Lane wrote: > AFAICS, array_remove keeps the existing lower bound number. > Thank you for explanation. This is not specified in http://www.postgresql.org/docs/current/static/functions-array.html#ARRAY-FUNCTIONS-TABLE so I was not sure. Regards, Matija Lesar
[GENERAL] Unexpected array_remove results
Hi,
should not in example below array_remove return same results?
test1=# create temp table tmptest (trid text[]);
CREATE TABLE
test1=# insert into tmptest values(null::text[]);
INSERT 0 1
test1=# update tmptest set trid[2:4]='{b,NULL,d}';
UPDATE 1
test1=# select trid,array_remove(trid,
NULL::text),array_lower(array_remove(trid, NULL::text),
1),array_upper(array_remove(trid, NULL::text), 1) from tmptest;
trid | array_remove | array_lower | array_upper
--+--+-+-
[2:4]={b,NULL,d} | [2:3]={b,d} | 2 | 3
(1 row)
test1=# update tmptest set trid='{NULL,b,NULL,d}';
UPDATE 1
test1=# select trid,array_remove(trid,
NULL::text),array_lower(array_remove(trid, NULL::text),
1),array_upper(array_remove(trid, NULL::text), 1) from tmptest;
trid | array_remove | array_lower | array_upper
-+--+-+-
{NULL,b,NULL,d} | {b,d}| 1 | 2
(1 row)
I expected that in both results values will start from index 1.
Regards,
Matija Lesar
