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
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
Thank you Victor Will experiment with this over the next couple of days.
On 7 August 2016 at 21:41, Victor Yegorov <vyego...@gmail.com> wrote:
> 2016-08-07 22:23 GMT+03:00 Tim Smith <randomdev4+postg...@gmail.com>:
>>
>> create table test (
>> when date,
>>
Thank you Sándor. Will experiment with this over the next couple of days.
On 7 August 2016 at 21:05, Sándor Daku <daku.san...@gmail.com> wrote:
> On 7 August 2016 at 21:23, Tim Smith <randomdev4+postg...@gmail.com> wrote:
>>
>> Hi,
>>
>> Let's say I'v
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
resql.org
> <javascript:_e(%7B%7D,'cvml','pgsql-general-ow...@postgresql.org');>
> [mailto:pgsql-general-ow...@postgresql.org
> <javascript:_e(%7B%7D,'cvml','pgsql-general-ow...@postgresql.org');>] *On
> Behalf Of *David G. Johnston
> *Sent:* Thursday, 14 July, 2016 08:23
> *T
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');
Thanks for that, looks like something to sink my teeth into !
On 21 June 2016 at 13:29, Alban Hertroys <haram...@gmail.com> wrote:
>
>> On 19 Jun 2016, at 10:58, Tim Smith <randomdev4+postg...@gmail.com> wrote:
>>
>> Hi,
>>
>> My postgresql-fu i
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
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 <adrian.kla...@aklaver.com> 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 re
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 <laurenz.a...@wien.gv.at> 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 b
0.0.0.0/0 to be returned if there is no more specific
match.
On 9 December 2015 at 12:45, Albe Laurenz <laurenz.a...@wien.gv.at> wrote:
> Tim Smith wrote:
>> create table test(a inet);
>> insert into test values ('0.0.0.0/0');
>> insert into test values (
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)
Great, thanks!
On 4 December 2015 at 12:17, Bill Moran <wmo...@potentialtech.com> wrote:
> On Fri, 4 Dec 2015 09:41:24 +0000
> Tim Smith <randomdev4+postg...@gmail.com> wrote:
>
>> When I use "preparedStatement.setString(5,ip);" to send values to a
>&
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
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 scott.marl...@gmail.com 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
On 24 July 2015 at 01:37, Rob Sargent robjsarg...@gmail.com 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.
wrote:
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 triggers. TRUNCATE was added (I
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
at 14:19, Adrian Klaver adrian.kla...@aklaver.com 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 unqualified DELETE on each table
Thus, if you are telling
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 randomdev4+postg...@gmail.com
wrote:
Hi,
I very
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
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
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
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.
--
Sent via
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
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
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
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
changes
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
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
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
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
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 database
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
Will take a look. Thanks steve.
On 24 February 2015 at 23:57, Steve Atkins st...@blighty.com wrote:
On Feb 24, 2015, at 3:50 PM, Tim Smith randomdev4+postg...@gmail.com wrote:
The goal being to match the longest prefix given a full phone number, e.g.
61234567890 would match australia
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
)
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
...@aklaver.com 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 error messages.
Somewhere it is seeing a duplicate column.
How about you tell me where you see these duplicate
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 m.ma...@intershop.de 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. Februar 2015 00:38
An: Tim Smith
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 message
Thank you Adrian. Will give this a go over the weekend.
On 6 February 2015 at 17:23, Adrian Klaver adrian.kla...@aklaver.com 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.
To figure this out we
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 ?
Klaver adrian.kla...@aklaver.com 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_view%ROWTYPE;
BEGIN
select * into strict v_row from my_view where foo=bar;
select row_to_json
|
tenant_lastupdate | bigint|
On 5 February 2015 at 23:19, David Johnston david.g.johns...@gmail.com wrote:
On Thu, Feb 5, 2015 at 4:01 PM, Tim Smith randomdev4+postg...@gmail.com
wrote:
returning more than one row? v_row can only hold one row at a time.
Absolutley not. (a) My
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,
February 2015 at 23:38, David Johnston david.g.johns...@gmail.com wrote:
On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith randomdev4+postg...@gmail.com
wrote:
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
tenant_lastupdate
On 5 February 2015 at 23:38, David Johnston david.g.johns...@gmail.com wrote:
On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith randomdev4+postg...@gmail.com
wrote:
You're most welcome to look at my view definition view if you don't
believe me
View definition:
SELECT a.session_id
wrote:
On Mon, Jan 26, 2015 at 2:38 PM, Tim Smith randomdev4+postg...@gmail.com
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);
Would I need to resort to using a CTE
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 mmonc...@gmail.com wrote:
On Sun, Jan 25, 2015 at 6:50 AM, Tim Smith
randomdev4+postg
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
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 i...@2ndquadrant.com wrote:
On 26/01/15 20:32, Tim Smith wrote:
Hi,
Is there a more efficient way to pattern match integer columns other
than
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
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 x...@thebuild.com wrote:
On Jan 23, 2015, at 12:20 PM, Tim Smith randomdev4+postg...@gmail.com wrote
:3119}';
?column?
--
(0 rows)
On 23 January 2015 at 15:50, Adrian Klaver adrian.kla...@aklaver.com wrote:
On 01/23/2015 07:40 AM, Tim Smith wrote:
re: (a)
see the documentation pertaining to 'jsonb indexing', to wit:
-- Find documents in which the key company has value Magnafone
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 adrian.kla...@aklaver.com wrote:
On 01/23/2015 10:15 AM, Tim Smith wrote:
How does it not work
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, there are a few
January 2015 at 15:08, Merlin Moncure mmonc...@gmail.com wrote:
On Fri, Jan 23, 2015 at 8:00 AM, Tim Smith
randomdev4+postg...@gmail.com wrote:
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
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 a...@squeakycode.net wrote:
On 1/6/2015 12:02 PM, Tim Smith wrote:
Hi,
I'm probably being incredibly stupid
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 vi...@khera.org wrote:
On Sat, Dec 13, 2014 at 1:55 PM, Edson Carlos Ericksson Richter
edsonrich...@hotmail.com
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
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
Tim Smith gb10hkzo-postg...@yahoo.co.uk:
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
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
72 matches
Mail list logo
