Hi,
I've been trying various GROUP BY but these all end up erroring out,
so maybe I'm using the wrong tool for the job (or, more likely, the
required query is beyond my level of SQL-fu !).
CREATE TABLE IF NOT EXISTS names (
main_id bigint,
sub_id bigint,
name text
);
create unique index IF NOT E
Hi,
I'm curious as to what the current advice would be in relation to
auto-complete type applications (e.g. "AJAX" type java-script "guess
as you type" applicatoins).
In relation to text fields, I know the general suggestion is gin_trgm_ops.
Is there much point even thinking about using gin_trgm
Thank you Victor Will experiment with this over the next couple of days.
On 7 August 2016 at 21:41, Victor Yegorov wrote:
> 2016-08-07 22:23 GMT+03:00 Tim Smith :
>>
>> create table test (
>> when date,
>> foo numeric,
>> bar numeric,
>> alice numeric,
Thank you Sándor. Will experiment with this over the next couple of days.
On 7 August 2016 at 21:05, Sándor Daku wrote:
> On 7 August 2016 at 21:23, Tim Smith wrote:
>>
>> Hi,
>>
>> Let's say I've got a table :
>>
>> create table test (
>&
Hi,
Let's say I've got a table :
create table test (
when date,
foo numeric,
bar numeric,
alice numeric,
bob numeric);
insert into test values ('2016-01-01',1,2,3,4);
insert into test values ('2016-01-02',5,6,7,8);
insert into test values ('2016-01-03',9,10,11,12);
insert into test values ('2016
eneral-ow...@postgresql.org
> ] *On
> Behalf Of *David G. Johnston
> *Sent:* Thursday, 14 July, 2016 08:23
> *To:* Nick Babadzhanian
> *Cc:* Tim Smith; pgsql-general
> *Subject:* Re: [GENERAL] Merging timeseries in postgres
>
>
>
> On Thu, Jul 14, 2016 at 8:18 AM, Nick B
Hi,
I've got a bit of query-writers block ! I've tried various join styles
but can't get it to do what I want to achieve.
Assume I have a bunch of time-series tables :
create table test(dx date,n numeric);
create table test1(dx1 date,nx1 numeric);
insert into test values('2000-01-01','0.001');
i
Thanks for that, looks like something to sink my teeth into !
On 21 June 2016 at 13:29, Alban Hertroys wrote:
>
>> On 19 Jun 2016, at 10:58, Tim Smith wrote:
>>
>> Hi,
>>
>> My postgresql-fu is not good enough to write a query to achieve this
>> (some ma
Hi,
My postgresql-fu is not good enough to write a query to achieve this
(some may well say r is a better suited tool to achieve this !).
I need to calculate what I would call a correlation window on a time
series of data, my table looks like this :
create table data(data_date date,data_measurem
Adrian
Any chance you could answer my original question now that I have
demonstrated to you what I meant by the PG repo even if I committed the
cardinal sin of not pointing you to the exact page on the PG website ?
Apologies for the tone, but I did ask a very simple question !
On Friday, 8 January 2016, Adrian Klaver wrote:
> On 01/08/2016 07:43 AM, Tim Smith wrote:
>
>> Hi,
>>
>> Many apologies if I missed some announcement anywhere, but there
>> appears to be no postgresql-9.5-prefix in the Postgres repository ?
>>
>
> W
Hi,
Many apologies if I missed some announcement anywhere, but there
appears to be no postgresql-9.5-prefix in the Postgres repository ?
Is this a deliberate omission or is it "coming real soon now" ?
Thanks !
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
Fabuous ! Thank you !
On 14 December 2015 at 07:52, Albe Laurenz wrote:
> Tim Smith wrote:
>> Re: I am surprised that you don't expect "0.0.0.0/0" to be returned by the
>> first
>> query if you expect it to be returned by the second.
>> Is that an ove
for 0.0.0.0/0 to be returned if there is no more specific
match.
On 9 December 2015 at 12:45, Albe Laurenz wrote:
> Tim Smith wrote:
>> create table test(a inet);
>> insert into test values ('0.0.0.0/0');
>> insert into test values ('10.1.2.3');
>
Hi,
create table test(a inet);
insert into test values ('0.0.0.0/0');
insert into test values ('10.1.2.3');
=> select * from test;
a
---
0.0.0.0/0
10.1.2.3
(2 rows)
This works as expected .
=> select * from test where a <<= '10.1.2.3';
a
--
10.1.2.3
(1 row)
This
Great, thanks!
On 4 December 2015 at 12:17, Bill Moran wrote:
> On Fri, 4 Dec 2015 09:41:24 +
> Tim Smith wrote:
>
>> When I use "preparedStatement.setString(5,ip);" to send values to a
>> stored function, it obviously gets sent to postgres as "c
Hi,
When I use "preparedStatement.setString(5,ip);" to send values to a
stored function, it obviously gets sent to postgres as "character
varying".
Postgres obviously complains loudly and says " Hint: No function
matches the given name and argument types. You might need to add
explicit type casts
>
> Just in case it has not been made obvious yet, rules are silently
> deprecated. They still exist because views depend on them, but it is
> generally considered best practices to not use them outside that realm.
Well, the manual doesn't seem to reflect that fact.
If that's how the developers
On 23 July 2015 at 19:25, Scott Marlowe wrote:
> stick to triggers, they're faster
Erm, not according to your beloved manual !!!
38.7. Rules Versus Triggers
" a rule issuing one extra command is likely to be faster than a trigger"
"The summary is, rules will only be significantly slower than t
On 24 July 2015 at 01:37, Rob Sargent wrote:
> Fair enough but both blackhats and the authorized are just as likely to drop
> the database as truncate something (intentionally or not) and backups
> stashed everywhere is the first order of business.
That's not the point. Backups are important,
te:
> On Thu, Jul 23, 2015 at 08:06:19AM +0100, Tim Smith wrote:
> > What exactly is was the design decision that lead to TRUNCATE being
> > supported by triggers but not by rules ?
>
> There are two things. First, probably the design decision was, "I
> care about tri
So tell me guys, instead of bashing away at the fact I only quoted
half a sentence or whatever, how about you answer the following :
What exactly is was the design decision that lead to TRUNCATE being
supported by triggers but not by rules ?
I suspect that TRUNCATE was added to triggers because s
e mentality !
On 22 July 2015 at 14:19, Adrian Klaver wrote:
> On 07/22/2015 06:13 AM, Tim Smith wrote:
>>
>> Melvin,
>>
>> May I point out that the manual states :
>> "TRUNCATE quickly removes all rows from a set of tables. It has the same
>> effect as an u
ual states event can be:
>
> INSERT
> UPDATE [ OF column_name [, ... ] ]
> DELETE*TRUNCATE <-*
>
> http://www.postgresql.org/docs/9.4/interactive/sql-createtrigger.html
>
> I suggest you review carefully.
>
> On Wed, Jul 22, 2015 at 8:53 AM, Tim Smith
&g
Hi,
I very much hope this is an accidental bug rather than a deliberate feature !
PostgreSQL 9.4.4
create rule no_auditupd as on update to app_security.app_audit do
instead nothing;
create rule no_auditdel as on delete to app_security.app_audit do
instead nothing;
\d+ app_security.app_audit
R
>
>
> I knew I was missing something:(
> http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>
> "When an error is caught by an EXCEPTION clause, the local variables of the
> PL/pgSQL function remain as they were when the error occurred, but all
> c
>
> Did not see the strict. In any case I thought cleanSessionTable was cleaning
> out app_security.app_sessions not app_security.app_val_session_vw.
Yes. cleanSessionTable does the actual cleaning.
The point of the select from app_security.app_val_session_vw is that
if the session is valid, the
Me again, I've reworded it, but its still rolling back !!!
Using the code below, if I call :
select app_security.validateSession('XYZ','10.10.123.43','Z',5,5);
I get an error raised on the select that follows cleanSessionTable.
Which is fine.
BUT, Postgresql is still rolling back !
If I go back
> Well that was my problem, I did not know what was intended.
apt-get install mind-reading ;-)
> 1) Look before you leap
>
I'm confused by this option ?
My script reads as follows :
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
select * into strict v_row from app_s
Adrian,
"what I want" is quite simple, I want the function to work as intended. ;-)
Let's step through the function :
(1) perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
Function calls cleanSessionTable.cleanSessionTable is simple. It
calls DELETE on the session
Adrian,
Ok, let's start fresh.
app_security.validateSession() calls app_security.cleanSessionTable().
app_security.cleanSessionTable(), when called on its, own, does not
cause me any issues. It operates as designed.
I have added ROLLBACK TO SAVEPOINT to validateSession(), so that it now reads
Hi David,
I should have perhaps made clear this was a saved function, so my
understanding is ROLLBACK can't be used as its implicit.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> You need to trap exceptions and in the handler block issue a
>
> ROLLBACK TO SAVEPOINT
>
> http://www.postgresql.org/docs/9.4/static/sql-rollback-to.html
>
>
> otherwise the the ROLLBACK issued at pg-session end will simply rollback
> everything.
>
> David J.
>
Thanks, will take a look.
--
Se
> So this is in a plpgsql function?
It is yes, but I thought I would spare you a copy/paste of the entire thing.
The error trapping section currently looks like this :
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to validate
session fo
Hi,
Apologies if I am being incredibly stupid, but I just can't seem to
get this to work for me.
I have a function that validates a web session is still active, so my
code looks something like this :
BEGIN
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
SAVEPOINT sp_cle
> If you want to do that then store that in your date/timestamp data and we'll
> output it. But we're not going to silently convert infinity to anything
> else:
Just for the record, I never said I wanted to do it. I was saying it
for the benefit of those people who replied to this thread talking
> Seriously? Json not supporting infinity makes it useless. Ok, so it has
> been useless for the, I don't know, last 10 years?
Just face it Andres, it should have never been coded that way in the
first place. The fact that it appears that nobody in the last 10
years has used "infinity" in conjunc
FYI although I remain a +1 on KISS and emitting "infinity", for
those of you still yearning after a standards-based implementation,
there is a StackOverflow post which hints at sections 3.5 and 3.7 of
ISO8601:2004.
Unfortunatley I can't find a link to an ISO8601:2004 text, so you'll
have to ma
> So +1 for removing the error and emitting "infinity" suitably quoted.
> Andrew, will you do that?
>
+1 here too. Otherwise there's very little point having the
"infinity" feature in Postgres if only some of the database functions
actually support it without throwing a tantrum. If its a databas
Hi,
As far as I'm aware, JSON has no data types as such, and so why is
Postgres (9.4.1) attempting to impose its own nonsense constraints ?
Surely it should just insert the word 'infinity' into the JSON output,
just like it displays in a simple SQL query ?
create table app_test.foo(a text,b date,
Will take a look. Thanks steve.
On 24 February 2015 at 23:57, Steve Atkins wrote:
>
> On Feb 24, 2015, at 3:50 PM, Tim Smith wrote:
>
>>
>>
>> The goal being to match the longest prefix given a full phone number, e.g.
>>
>>
>> 61234567890
Have an Oracle "connect by" SQL that looks something like :
select phone, pfx, len, (select info from codes where
pfx = x.pfx) infot
from (
select :x phone, to_number(substr( :x, 1, length(:x)-level+1 )) pfx,
length(:x)-level+1 len
from dual
connect by level <= length(:x)
order by level
Thank you Adrian. Will give this a go over the weekend.
On 6 February 2015 at 17:23, Adrian Klaver wrote:
> On 02/06/2015 08:55 AM, Tim Smith wrote:
>>>
>>> Unfortunately the function definition is not given and that is where you
>>> are seeing the error.
>>
Re:So, you have an input parameter named "session_id" and a query with
a column named "session_id" - this is the problem.
Well, I'll re-try with a revised function, but surely the database
could have come up with a more meaningful and insightful message than
the coded incomprehensible error messag
>Unfortunately the function definition is not given and that is where you are
>seeing the error.
> To figure this out we will need to see the function.
Geez, there's just no satisfying some people ! ;-)
I did actually show you my function in an earlier mail but my
current bodged minimised
Nice work-around Marc. Thank you !
On 6 February 2015 at 13:01, Marc Mamin wrote:
>
>>Von: pgsql-general-ow...@postgresql.org
>> [pgsql-general-ow...@postgresql.org]" im Auftrag von "David Johnston
>> [david.g.johns...@gmail.com]
>>Gesendet: Freitag, 6. Fe
And if you want my exact version of Postgres its "PostgreSQL 9.4.0 on
x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro
4.6.3-1ubuntu5) 4.6.3, 64-bit" (taken from the Postgres APT
repository)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your s
n 5 February 2015 at 23:58, Adrian Klaver wrote:
> On 02/05/2015 03:25 PM, Tim Smith wrote:
>>>
>>> PostgreSQL doesn't lie
>>
>>
>> Well if its not lying its one big stinking bug !
>
>
> In my experience Postgres does not randomly make up
tenant_lastupdate
On 5 February 2015 at 23:38, David Johnston wrote:
> On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith
> wrote:
>>
>> You're most welcome to look at my view definition view if you don't
>> believe me
>>
>> View definition
On 5 February 2015 at 23:38, David Johnston wrote:
> On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith
> wrote:
>>
>> You're most welcome to look at my view definition view if you don't
>> believe me
>>
>> View definition:
>> SELECT a.se
You're most welcome to look at my view definition view if you don't
believe me
View definition:
SELECT a.session_id,
a.session_ip,
a.session_user_agent,
a.session_start,
a.session_lastactive,
b.user_id,
b.tenant_id,
b.reseller_id,
b.tenant_name,
b.user_fna
update| bigint|
tenant_lastupdate | bigint|
On 5 February 2015 at 23:19, David Johnston wrote:
> On Thu, Feb 5, 2015 at 4:01 PM, Tim Smith
> wrote:
>>
>> > returning more than one row? v_row can only hold one row at a time.
>>
>> Absolutley no
")
On 5 February 2015 at 21:57, Adrian Klaver wrote:
> On 02/05/2015 01:38 PM, Tim Smith wrote:
>>
>> Hi,
>>
>> I have a function that broadly looks like this :
>>
>> create function doStuff() returns json as $$
>> DECLARE
>> v_row my_vie
Hi,
I have a function that broadly looks like this :
create function doStuff() returns json as $$
DECLARE
v_row my_view%ROWTYPE;
BEGIN
select * into strict v_row from my_view where foo=bar;
select row_to_json(v_row) from v_row;
END;
$$ LANGUAGE plpgsql;
However this does not seem to work ? Wh
wrote:
> On Mon, Jan 26, 2015 at 2:38 PM, Tim Smith
> wrote:
>>
>> create table templates(
>>template_id int not null primary key,
>>template_groupid int not null,
>>template_version int not null
>>template_text text not null);
>>
>
Thanks for the extra feedback Merlin. I'll look into it a bit more,
JSONB obviously needs a bit of experimentation in the lab to get my
query syntax right !
On 27 January 2015 at 00:13, Merlin Moncure wrote:
> On Sun, Jan 25, 2015 at 6:50 AM, Tim Smith
> wrote:
>>> "D
Hi,
I've spent too long staring at code today and am facing a bit of a
block when trying to figure out how to best implement the following.
I'm basically looking at tracking versions of some configuration items
saved in a database table. My thinking of the table is something
along the following
Ian,
Re: However you might find the pg_trgm extension [1] useful:
Indeed... pretty awesome.
Thanks !
On 26 January 2015 at 12:55, Ian Barwick wrote:
> On 26/01/15 20:32, Tim Smith wrote:
>> Hi,
>>
>> Is there a more efficient way to pattern match integer columns other
&
Hi,
Is there a more efficient way to pattern match integer columns other
than something like :
where cast(mynumber as text) ~ '.*123.*'
I also seem to recall you can't create indexes on casts either ?
Thx
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make ch
> "Doesn't meet my particular use-case exactly" is not quite the same thing.
I would have thought my outlined use-case was pretty basic and common ?
On 23 January 2015 at 20:44, Christophe Pettus wrote:
>
> On Jan 23, 2015, at 12:20 PM, Tim Smith wrote:
>
>>
So basically we're saying JSON in 9.4 is still a little way from where
it needs to be in terms of real-world functionality ? Or am I being
too harsh ? ;-)
On 23 January 2015 at 18:49, Adrian Klaver wrote:
> On 01/23/2015 10:15 AM, Tim Smith wrote:
>>>
>>> How does
"bob"}]');
INSERT 0 1
select content->'Desc' from json_test where content @> '{"ID":"3119"}';
?column?
--
(0 rows)
On 23 January 2015 at 15:50, Adrian Klaver wrote:
> On 01/23/2015 07:40 AM, Tim Smith wrote:
>>
>&g
;:
> "Magnafone"}';
Nope, sorry, tried that. Doesn't work for me. Hence the question. ;-)
On 23 January 2015 at 15:08, Merlin Moncure wrote:
> On Fri, Jan 23, 2015 at 8:00 AM, Tim Smith
> wrote:
>> Hi,
>>
>> I've tried RTFMing on the wonderf
Hi,
I've tried RTFMing on the wonderful new 9.4 jsonb features, but
there's a little bit of a lack of examples as to how to do stuff.
I've got a document loaded in to a jsonb column that looks something like :
[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]
Anyway, the
Hi Andy,
Yeah, the table scan was what worried me.
As for no indexes ? I just didn't put the "create index" statements
in my post ... ;-)
Tim
On 6 January 2015 at 18:35, Andy Colson wrote:
> On 1/6/2015 12:02 PM, Tim Smith wrote:
>>
>> Hi,
>>
>>
Hi,
I'm probably being incredibly stupid and missing something incredibly
simple but I've got a bit of query-writers block here !
create table app_sessions(
session_id char(64) unique not null,
user_id char(32) unique not null,
session_start bigint not null,
session_lastactive bigint not null
);
Try http://brendangregg.com/
Lots of great tidbits there from a guy who really knows his performance
stuff (ex-Sun, now Netflix)
On Sunday, 14 December 2014, Vick Khera wrote:
>
> On Sat, Dec 13, 2014 at 1:55 PM, Edson Carlos Ericksson Richter <
> edsonrich...@hotmail.com
> > wrote:
>>
>> I've
Hi,
I'm trying to use PostgreSQL ltree to make a basic RBAC system as it
seems a sensible thing to do because of the hierarchical parsing ltree
can do !
I currently have the tables below which I've simplified as follows :
/***
create table app_users
David,
You are right of course about sample data.
I've put some data up on Pastebin here http://pastebin.com/EtVnjiYv
As for an example of an expected result from the above :
- Order individual test results asc/desc as required, so you end up for example
with for test1 as shown on paste bin
I have a table as follows :
Year (numeric)
Factor (text)
Test_1 (numeric)
Test_2 (numeric)
Test_3 (numeric)
Test_4 (numeric)
unique index(year,factor) (i.e. each factor only appears once per year)
What I need to achieve is an ordered ranking of the factors for each test, e.g.
let's say I've got
, (foxtrot - avg(foxtrot))/stddev(foxtrot) AS foxtrotNorm
> FROM t_subs
>)
>SELECT
> deltaNorm + echoNorm + foxtrotNorm AS normSum
>FROM NormCTE
>ORDER BY normSum DESC
>
>
>HTH
>
>
>
>Kind regards/met vriendelijke groet,
>
>
>Serge Fonville
>
>h
Hi,
I'm on Postgres 9.3.5, however I think my knowledge of Postgres is not deep
enough to help me with this challenge, so here I am reaching out to the
community !
Let's say I have a table as follows :
create table t_subs (alpha text,bravo text,charlie numeric,delta numeric,echo
numeric,foxtr
72 matches
Mail list logo
