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
are the first / last in your set
> based on whatever column you order on.
> On May 18, 2016 8:47 PM, "Tom Smith" wrote:
>
>> Hello:
>>
>> Is there a plan for 9.7 to enable using the two aggregate function
>> as non-window function? i.e. enabling
Hello:
Is there a plan for 9.7 to enable using the two aggregate function
as non-window function? i.e. enabling getting the first/last row
in single sql without using window features.
There is actually a C-extension for first()/last().
I am wondering if 9.7 would make them built-in function
in the storage,
all keys starting with "a" is in first segment, etc.
On Sun, May 1, 2016 at 4:14 PM, Oleg Bartunov wrote:
>
>
> On Sun, May 1, 2016 at 6:46 AM, Tom Smith
> wrote:
>
>> Hello:
>>
>> I'd like to bring this JSONB performance issue a
JSONB implementation, both common use
cases
(one is global doc indexing, the other is fast retrieval of individual
values)
would work out and make postgresql unbeatable.
On Tue, Jan 19, 2016 at 8:51 PM, Bruce Momjian wrote:
> On Mon, Jan 11, 2016 at 09:01:03PM -0500, Tom Smith wrote
exactly what I am looking for. very nice. Thx
On Sun, Mar 13, 2016 at 10:44 PM, Tom Lane wrote:
> >> On 03/13/2016 10:07 PM, Tom Smith wrote:
> >>> It would help if the resultset has some param to mark which is which
> >>> with the grouping sets index.
&
(a,b) or (c,d) group? All rows
> will contain (a,b,c,d) but (a,b) will be NULL for the (c,d) grouping
> sets, and vice-versa.
>
> Jim
>
> On 03/13/2016 09:45 PM, Tom Smith wrote:
> > Hello:
> >
> > With JDBC, how can I tell which row is for which groupi
Hello:
With JDBC, how can I tell which row is for which grouping sets or rollup
using result sets
Thanks
unique
On Thu, Feb 18, 2016 at 2:14 AM, David Rowley
wrote:
>
> On 18/02/2016 9:34 am, "Tom Smith" wrote:
> >
> > Hi:
> >
> > I feel it is a stupid question.
> >
> > Can BRIN index enforce uniqueness?
> > My issue is
> > the column I&#
Hi:
I feel it is a stupid question.
Can BRIN index enforce uniqueness?
My issue is
the column I'd like to apply BRIN index also needs to be unique
(think of timestamp as primary key).
Thanks
Using JSON/JSONB type in postgresql is usually due to the use case that the
keys (top level included) can not be predefined. this is the major
difference between NoSQL/Document and RDBMS.
Why would TOAST have to be used? Can some speciailly structured "raw"
files be used
outside current databa
Yeah. I am looking for fastest possible method that Postgresql would
use its internal data structure knowledge to walk through the timestamp
index
and resturns every "nth" row
On Mon, Jan 25, 2016 at 5:56 AM, Simon Riggs wrote:
> On 25 January 2016 at 09:44, Matija Lesar wrote:
>
>
>> you can a
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 ro
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 g
Hi,
Congrats on the official release of 9.5
And I'd like bring up the issue again about if 9.6 would address the jsonb
performance issue
with large number of top level keys.
It is true that it does not have to use JSON format. it is about
serialization and fast retrieval
of dynamic tree structure
extremely slow.
Thanks
On Sun, Nov 29, 2015 at 12:54 PM, Tom Lane wrote:
> Bill Moran writes:
> > Tom Smith wrote:
> >> Is there a plan for 9.6 to resolve the issue of very slow
> >> query/retrieval of jsonb fields when there are large number (maybe
> >>
document data.
On Sun, Nov 29, 2015 at 12:37 AM, John R Pierce wrote:
> On 11/28/2015 6:27 PM, Tom Smith wrote:
>
>> Is there a plan for 9.6 to resolve the issue of very slow query/retrieval
>> of jsonb fields
>> when there are large number (maybe several thousands) of top
-as-number-of-keys-increase
below url mentions the potential issue.
https://www.reddit.com/r/PostgreSQL/comments/36rdlr/improving_select_performance_with_jsonb_vs_hstore/
Thanks
On Sun, Nov 29, 2015 at 7:35 AM, Thomas Kellerer wrote:
> Tom Smith schrieb am 29.11.2015 um 03:27:
>
>> Hello:
>
Hello:
Is there a plan for 9.6 to resolve the issue of very slow query/retrieval
of jsonb fields
when there are large number (maybe several thousands) of top level keys.
Currently, if I save a large json document with top level keys of thousands
and query/retrieve
field values, the whole document
titioned table.
On Tue, Sep 8, 2015 at 3:51 PM, Merlin Moncure wrote:
> On Mon, Sep 7, 2015 at 12:07 AM, Tom Smith
> wrote:
> > Hi:
> >
> > I am using the wonderful lateral query feature like the following
> >
> > select * from generate_series (1,10,5)
It can be any jsonb so I am asking a general question of the implementaion
for each jsonb storage (not about GIN or table wide indexing, but only
within
a single jsonb item in a single row.
A sample would be like (no quotes)
{
a1: {b1:v1, b2:v2, b100:v100}
a3000: {c1:x1, c2: x2. c200: v200
}
yo
Hello:
Does JSONB storage has some internal indexing(like hasmap)
to fast look up a value given a key?
I have a jsonb doc with two level keys
(parentKey: {childKey:value}}
there are maybe 2000 parent keys per doc and 100 child keys per parent key
and I am trying to get value via jsonb->parentKey
Hi:
I am using the wonderful lateral query feature like the following
select * from generate_series (1,10,5) T(t),
lateral (select * from P where t between t and t + 3)
P is a parent table of a hundred partitions
the idea is to for each t value from 1 to 10 with step of 5,
get row
Got it. Thanks very much
On Sun, Sep 6, 2015 at 11:25 PM, Michael Paquier
wrote:
> On Mon, Sep 7, 2015 at 12:12 PM, Tom Smith
> wrote:
> > Hi,
> >
> > Can setting be appended to the end of the postgresql.conf
> > so that it will override whatever is already in
Hi,
Can setting be appended to the end of the postgresql.conf
so that it will override whatever is already in the previous version.
For example
the existing postgresql.conf already has setting
max_connections = 100
in the middle of file.
Now I append a line as the end of the file, regardless w
Hi:
The window function works for me (with adding limit 1 in the end to output
only one row
needed instead of many duplicate rows).
thanks very much.
On Thu, Sep 3, 2015 at 6:51 AM, Dickson S. Guedes
wrote:
> On Wed, Sep 02, 2015 at 07:14:40PM -0400, Tom Smith wrote:
> > Hi:
>
CT
> FROM
> ORDER BY offset 0 LIMIT 1) ,
> (SELECT
> FROM
> ORDER BY OFFSET (SELECT COUNT(*) ) LIMIT 1)
>FROM LIMIT 1;
>
>
> On Wed, Sep 2, 2015 at 7:27 PM, Rob Sargent wrote:
>
>> On 09/02/2015 05:14 PM, Tom Smith wrote:
>>
Hi:
I need to get the first and last tow in one sql like below
select first(col1), last(col1) from table order by col1
I saw some posting in wiki with a custom function (or C extention)
to do this. Is it widely used and reliable?
https://wiki.postgresql.org/wiki/First/last_(aggregate)
I am wo
;t return the value to your program. I keep forgetting this
>> way. I learned it the other way. Old dog + new trick == problem.
>>
>> On Sun, Aug 23, 2015 at 5:04 PM, John McKown <
>> john.archie.mck...@gmail.com> wrote:
>>
>>> On Sun, Aug 23, 2015 at 4:05
Hello:
I have a time series table,
using below sql, loop (psque code), I can get one row for each hour
for( H=1: H< 9; H++){
select * from table where t >= H and t < H+1 limit 1
}
t (time column) is indexed).
Is there a better way to use a issue a SINGLE SQL
with an array of time st
thanks. I hope a new function can be added(with high perf C function) in
new release to allow something like
json_subset(jsonb_object, [key1,key2])
On Wed, Aug 19, 2015 at 9:46 AM, Chris Mair wrote:
> On 19/08/15 13:37, Tom Smith wrote:
> > Hi:
> >
> > I have a jsonb col
Hi:
I have a jsonb columne with json object like belo
{"a": 1, "b":2, "c":3}
I'd like to get subset of the object with key list ["a","c"]
so it retruns json object of
{"a": 1, "c":3}
something like
select '{"a": 1, "b":2, "c":3}'::jsob ->'["a","c"]'
what would be the most efficient (and simp
32 matches
Mail list logo
