[GENERAL] JDBC and array of points

Hi, I've posted this to the postgis user group as well, but I think the issue may just be with my inability to understand the explicit casting in 9.2. I have code that generates a PreparedStatement that accepts an array of points in an ANY clause. I convert the point objects in java to a string "

Re: [GENERAL] Need help extripating plpgsql

On 02/22/2013 02:25 PM, Kevin Grittner wrote: To get owner to be bob you need to do: \c bob set role bob; create extension plpgsql; Good point, I forgot that the user was reset by \c. Either way you still get the error on the COMMENT which is what is tripping up the OP. The good news is

Re: [GENERAL] Need help extripating plpgsql

Adrian Klaver wrote: > On 02/22/2013 11:59 AM, Kevin Grittner wrote: >> Adrian Klaver wrote: >> >>> At this point I am not sure how to do this with out creating role >>> that has superuser privileges. >> >> Something like this?: >> >> -- Set up the template using database superuser. >> create dat

Re: [GENERAL] Need help extripating plpgsql

On 02/22/2013 11:59 AM, Kevin Grittner wrote: Adrian Klaver wrote: At this point I am not sure how to do this with out creating role that has superuser privileges. Something like this?: -- Set up the template using database superuser. create database template2; \c template2 drop extension p

Re: [GENERAL] Dumb question involving to_tsvector and a view

Raymond C. Rodgers wrote: > As I went to add a tsvector column, it occurred to me that it > might be possible to add a dynamic tsvector column through the > use of a view, so I created a temporary view with a command along > the lines of: > > CREATE TEMPORARY VIEW ftstest AS SELECT id, field1

[GENERAL] Spellcheck function

Hi, I need to implement a spell cheker for a client-server application with a shared dictionary. I was thinking of implementing it as a Postgres function, but, before that I would like to know if it or something similar is already implemented. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.

Re: [GENERAL] confirming security.

Good tip! Thank you. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Friday, February 22, 2013 2:35 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] confirming security. On 2/22/2013 8:1

[GENERAL] Dumb question involving to_tsvector and a view

Hi folks, I'm building a PHP script for a web site I'm developing. At the moment, there is absolutely no real data in the database, so obviously performance is pretty good right now. I'm in the midst of developing an administration page for the site, which will do a full text search on sev

Re: [GENERAL] Need help extripating plpgsql

Adrian Klaver wrote: > At this point I am not sure how to do this with out creating role > that has superuser privileges. Something like this?: -- Set up the template using database superuser. create database template2; \c template2 drop extension plpgsql; vacuum freeze analyze; \c postgres upd

Re: [GENERAL] confirming security.

On 2/22/2013 8:13 AM, Maz Mohammadi wrote: Ahhh yesit is now... === # TYPE DATABASEUSERADDRESS METHOD # "local" is for Unix domain socket connections only #local all all trust # IPv4 local connecti

Re: [GENERAL] autoanalyze criteria

On Fri, Feb 22, 2013 at 5:57 AM, Stefan Andreatta wrote: > Hi, > > If I understand http://www.postgresql.org/**docs/9.1/static/routine-** > vacuuming.htmlcorrectly, > the autovacuum threshold in could be estimated like this in > Po

Re: [GENERAL] Access a window's frame_end row from a window function

Clodoaldo Neto writes: > Suppose there is the need to get the average of a value v over a 6 hours > time window starting 7 hours before the current row's time. > ... > But if I could access a window's frame_end row as a record from a window > function: > select ts, > avg(case when ts

Re: [GENERAL] limit based on count(*)

On 02/22/2013 11:14 AM, Russell Keane wrote: > select serial_no, count(*) as "restarts" from event_log where event_mesg ilike 'system sta%' and event_date > current_date - 7 > group by serial_no > having count(*) > X > order by restarts I think having is the better option. Thanks all, did

Re: [GENERAL] limit based on count(*)

> select serial_no, count(*) as "restarts" from event_log where event_mesg > ilike 'system sta%' and event_date > current_date - 7 > group by serial_no > having count(*) > X > order by restarts I think having is the better option.

Re: [GENERAL] Need help extripating plpgsql

On 02/22/2013 07:25 AM, James B. Byrne wrote: On Thu, February 21, 2013 20:27, Adrian Klaver wrote: My previous not withstanding there is a reason I can see why this not so. Just because a user does not own an object does not mean they cannot use it. This allows a DBA to set up a template wit

Re: [GENERAL] confirming security.

Ahhh yesit is now... === # TYPE DATABASEUSERADDRESS METHOD # "local" is for Unix domain socket connections only #local all all trust # IPv4 local connections: #hostall all

Re: [GENERAL] limit based on count(*)

> Hello List, > > I have a query that counts how many time an event occurs in our event_log > these are grouped by the serial number of the device that created the event. > I would like to show only the rows where the number of events exceeds some > threshold. > > simplified query: > select ser

Re: [GENERAL] limit based on count(*)

On 22 February 2013 17:01, Steve Clark wrote: > select serial_no, count(*) as "restarts" from event_log where event_mesg > ilike 'system sta%' and event_date > current_date - 7 > group by serial_no order by restarts > select serial_no, count(*) as "restarts" from event_log where event_mesg ilik

Re: [GENERAL] Access a window's frame_end row from a window function

On Fri, Feb 22, 2013 at 8:26 AM, Clodoaldo Neto wrote: > Suppose there is the need to get the average of a value v over a 6 hours > time window starting 7 hours before the current row's time. > > create table t (ts timestamp, v integer); > insert into t (ts, v) values > ('2013-01-01 00

[GENERAL] limit based on count(*)

Hello List, I have a query that counts how many time an event occurs in our event_log these are grouped by the serial number of the device that created the event. I would like to show only the rows where the number of events exceeds some threshold. simplified query: select serial_no, count(*) as

Re: [GENERAL] confirming security.

On 02/22/2013 07:50 AM, Maz Mohammadi wrote: Thx John, It got me a long way. I actually have a more complex installation (I think) that I originally thought on my test linux box. Looks like all the files that I modify are under /var/lib/post../coord. I added the line.. to pg_hba.conf hostssl

Re: [GENERAL] confirming security.

Thx John, It got me a long way. I actually have a more complex installation (I think) that I originally thought on my test linux box. Looks like all the files that I modify are under /var/lib/post../coord. I added the line.. to pg_hba.conf hostssl all all 127.0.0.1/32

Re: [GENERAL] Need help extripating plpgsql

On Thu, February 21, 2013 20:27, Adrian Klaver wrote: > > My previous not withstanding there is a reason I can see why this not > so. Just because a user does not own an object does not mean they > cannot use it. This allows a DBA to set up a template with a > privilege scheme that suits their ne

Re: [GENERAL] Perl function leading to out of memory error

On 21.02.2013 01:02, Jeff Janes wrote: CREATE DOMAIN isin AS char(12) CHECK (isin_ok(value)); Could you give some example values that meet this criteria? Trying to reverse engineer your helper functions is no fun. A valid example is "DE000710". The same with another check digit is of cours

[GENERAL] autoanalyze criteria

Hi, If I understand http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html correctly, the autovacuum threshold in could be estimated like this in PostgreSQL 9.1: SELECT pg_stat_user_tables.relname, pg_stat_user_tables.n_dead_tup, CAST(current_setting('autovacuum_vacuum_th

Re: [GENERAL] Redefining a column within a view

Good Morning Laurenz, That worked perfectly. Thank You Jeff _ Jeffrey Schade Systems Consultant, Technology Engineering ISO 545 Washington Boulevard Jersey City, NJ 07310 Voice: (201) 469-3738   FAX: (201) 748-1500   jsch...@iso.com -Original Messag

Re: [GENERAL] Redefining a column within a view

Jeffrey Schade wrote: > We have a table which contains a 3 byte column with datatype CHAR(3) which we > want to redefine within > the view as a CHAR(1) column and a CHAR(2) column. When I code the SUBSTR > function the resulting > column datatype is TEXT. I would like to see the CHAR datatype, is

Re: [GENERAL] PostgreSQL binaries under /usr/lib, why?

* Rafael Martinez (r.m.guerr...@usit.uio.no) wrote: > i.e. pgbench, pg_archivecleanup, pg_upgrade, initdb and several others > are not available via /usr/bin and they should not be defined as low > level. initdb is wrapped through pg_createcluster and friends. pg_archivecleanup probably isn't some

Re: [GENERAL] PostgreSQL binaries under /usr/lib, why?

On Fri, Feb 22, 2013 at 4:41 PM, Rafael Martinez wrote: > Why are many postgres binaries in ubuntu under /usr/lib/postgresql? They are under /usr/lib/postgresql/${VERSION}/bin. This allows multiple PostgreSQL versions to coexist on the same Debian or Ubuntu server. > According to FHS-2.3, /usr/

Re: [GENERAL] PostgreSQL binaries under /usr/lib, why?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/22/2013 12:40 PM, Stuart Bishop wrote: > On Fri, Feb 22, 2013 at 4:41 PM, Rafael Martinez > wrote: >> According to FHS-2.3, /usr/lib includes object files, libraries, >> and internal binaries that are not intended to be executed >> directly by

Re: [GENERAL] PostgreSQL binaries under /usr/lib, why?

On 22/02/2013 09:41, Rafael Martinez wrote: > Hello > > Why are many postgres binaries in ubuntu under > /usr/lib/postgresql? > > According to FHS-2.3, /usr/lib includes object files, libraries, > and internal binaries that are not intended to be executed directly > by users or shell scripts. >

[GENERAL] Redefining a column within a view

We have a table which contains a 3 byte column with datatype CHAR(3) which we want to redefine within the view as a CHAR(1) column and a CHAR(2) column. When I code the SUBSTR function the resulting column datatype is TEXT. I would like to see the CHAR datatype, is there anything I can do to set th

[GENERAL] Redefining a column within a view

We have a table which contains a 3 byte column with datatype CHAR(3) which we want to redefine within the view as a CHAR(1) column and a CHAR(2) column. When I code the SUBSTR function the resulting column datatype is TEXT. I would like to see the CHAR datatype, is there anything I can do to set th

[GENERAL] PostgreSQL binaries under /usr/lib, why?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Why are many postgres binaries in ubuntu under /usr/lib/postgresql? According to FHS-2.3, /usr/lib includes object files, libraries, and internal binaries that are not intended to be executed directly by users or shell scripts. Almost all post

Re: [GENERAL] Need help extripating plpgsql

"James B. Byrne" writes: > If all the elements contained in the standard templates had their > ownerships changed to that of the owner of the new database then my > problem would never have arisen. I do not understand why this is not > the case. Is there a reason why this is so? I don't see why