[GENERAL] Build in function to verify email addresses

I beleieve that every information system has the needs to send emails. Currently PostgreSQL doesn't have a function which gets TEXT and return true if it's valid email address (x...@yyy.com / .co.ZZ) Do you believe such function should exist in PostgreSQL or it's best to let every user to

Re: [GENERAL] Indexes being ignored after upgrade to 9.5

report. Best regards Nick > On 27 Jul 2017, at 00:40, Peter Geoghegan <p...@bowt.ie> wrote: > >> On Wed, Jul 26, 2017 at 2:05 PM, Peter Geoghegan <p...@bowt.ie> wrote: >>> On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan <nbrenna...@gmail.com> wrote: >>

Re: [GENERAL] Interesting streaming replication issue

(sorry for the top post, bitchy K9 Mail) James, are you sure you're scp'ing from the archive, not from pg_xlog? Regards, Gunnar "Nick" Bluth Am 27. Juli 2017 05:00:17 MESZ schrieb James Sewell <james.sew...@jirotech.com>: >Hi all, > >I've got two ser

[GENERAL] Indexes being ignored after upgrade to 9.5

. All indexes show valid in pg_indexes. We have tried increasing effective_cache_size but no effect (the queries appear to go slower). The DB is 24x7 so we cannot reindex the tables/ partitions. Can anyone suggest why this would be happening? Many thanks Nick

Re: [GENERAL] Coditional join of query using PostgreSQL

This is a join in a middle of query. How can I use dynamic SQL in the middle of query?ב מאי 15, 2017 20:26, David G. Johnston כתב:On Mon, May 15, 2017 at 10:02 AM, Nick Dro <postgre...@walla.co.il> wrote: Hi, I'm new to postgresql and couldn't find answer to this situation anywhere. I

[GENERAL] Coditional join of query using PostgreSQL

Hi, I'm new to postgresql and couldn't find answer to this situation anywhere. I asked this here: http://stackoverflow.com/questions/43984208/coditional-join-of-query-using-postgresqlI hope there is a better solution rather than creating two separated functions :(

Re: [GENERAL] Streaming Replication Without Downtime

(sorry for the toppost, mobile device) What you're looking for is pg_basebackup with - - xlog=stream, I guess. Regards, Nick Am 17. Februar 2017 14:06:36 MEZ schrieb Gabriel Ortiz Lour <ortiz.ad...@gmail.com>: >Hi all, >I've been searching for a way to initialize a new Hot

Re: [GENERAL] pglogical cascading replication (chaining replication)

e is: on p2: select * from pg_replication_origin; will show all origins on p2, find the origin for p1; on p3: select pg_replication_origin_create('[origin name]'); Discussed here: https://github.com/2ndQuadrant/pglogical/issues/23 Please ignore previous message; Regards, Nick. -- Se

Re: [GENERAL] pglogical cascading replication (chaining replication)

The solution was found thanks to Petr Jelinek from 2ndQ. > Cascading wasn't much of a priority so far. > Currently you have to create the origin manually using > pg_replication_origin_create(). > I plan to make this work seamlessly in the future release. So whats needed to be done is: on p1:

Re: [GENERAL] Merging timeseries in postgres

Nevermind, I misunderstood your question. The answer is an outer join and if you want the exact output you provided then you can use the following clause. coalesce(dx, dx1) as date Is there any reason why these are two different tables? I'd consider changing data structure. - Original

Re: [GENERAL] Merging timeseries in postgres

Whats exactly is wrong with the following query? select dx date, nx, nx1 from test t join test1 t1 on t.dx=t1.dx1 ; - Original Message - From: "Tim Smith" To: "pgsql-general"

Re: [GENERAL] pglogical cascading replication (chaining replication)

I already tried to contact them, but no luck so far. Although it seems to me that the change-set is actually forwarded, its the decoding that is failing on the receiving host. Check the log output for p2 and p3 in my previous message. Regards, Nick. - Original Message - From: "J

Re: [GENERAL] pglogical cascading replication (chaining replication)

pglogical apply 13294:1876007473 (PID 14180) exited with >exit code 1 >LOG: starting apply for subscription sub_p2_to_p3_insert_only >ERROR: cache lookup failed for replication origin >'pgl_test_node_p1_provider_sub_p1_t06410f8' >LOG: worker process: pglogical apply 13294

Re: [GENERAL] Replication with non-read-only standby.

Thanks. I ended up using pglogical, since I don't really need Bi-directional replication and docs for UDR suggest using pglogical instead. Although I ran into a problem there, but pglogical seems to be the answer. Regards, Nick. - Original Message - From: "Sylvain Mar

[GENERAL] pglogical cascading replication (chaining replication)

I apologize if this is wrong place to ask the question. A quote from pglogical FAQ: > Q. Does pglogical support cascaded replication? > Subscribers can be configured as publishers as well thus cascaded replication > can be achieved > by forwarding/chaining (again no failover though). The only

[GENERAL] Replication with non-read-only standby.

to this? Regards, Nick. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Slave claims requested WAL segment already removed - but it wasn't

On 2 June 2016 at 02:43, Jeff Beck wrote: > Hi- > We have a master (pg 9.4.4 on Ubuntu 14.10) and a slave (pg 9.4.8 on > Centos 7). During a period of heavy use, the slave began complaining > that the “requested WAL segment xx has already been removed”. But > the WAL

[GENERAL] Unexpected function behaviour with NULL and/or default NULL parameters

(userid:=null, sessionid:=null::uuid, locale:='en'); get_current_tac - NULL (1 row) I'm completely puzzled by this behaviour; at least it should cast the locale_specific value into the JSON output, shouldn't it? What am I missing? Any hints appreciated! - -- Gunnar Nick

Re: [GENERAL] Unexpected function behaviour with NULL and/or default NULL parameters

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 06.05.2015 um 09:57 schrieb David G. Johnston: Ooops, accidentaly replied to David directly... Wednesday, May 6, 2015, Gunnar Nick Bluth gunnar.bl...@pro-open.de mailto:gunnar.bl...@pro-open.de wrote: -BEGIN PGP SIGNED MESSAGE

Re: [GENERAL] Collation problem?

to get the correct sort order without recreating all my databases or initialize PGSQL? Sure (i.e., if you're on a half recent version): ALTER TABLE yourtable ALTER COLUMN yourcolumn COLLATE collation http://www.postgresql.org/docs/9.4/static/sql-altertable.html - -- Gunnar Nick Bluth RHCE/SCLA

Re: [GENERAL] FK check implementation

On Sat, Oct 11, 2014 at 5:01 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 10/10/2014 10:41 AM, Nick Barnes wrote: I understand why the FK insert needs to lock on the PK row. But why is the PK delete trying to lock the FK row? If it finds one, won't the delete fail anyway

[GENERAL] FK check implementation

I'm looking at the code behind the foreign key checks in ri_triggers.c, and something's got me a little confused. In both cases (FK insert/update checking the PK, and PK update/delete checking the FK) the check is done with a SELECT ... FOR KEY SHARE. This makes perfect sense for PK checks, but

Re: [GENERAL] FK check implementation

On Sat, Oct 11, 2014 at 1:32 AM, Tom Lane t...@sss.pgh.pa.us wrote: Nick Barnes nickbarne...@gmail.com writes: I'm looking at the code behind the foreign key checks in ri_triggers.c, and something's got me a little confused. In both cases (FK insert/update checking the PK, and PK update

Re: [GENERAL] table versioning approach (not auditing)

On September 29, 2014 11:08:55 AM EDT, Jonathan Vanasco postg...@2xlp.com wrote: - use a transaction log. every write session gets logged into the transaction table (serial, timestamp, user_id). all updates to the recorded tables include the transaction's serial. then there is a

Re: [GENERAL] Where art thou, plpython2.dll? (EDB installer)

/postgresql/plpython2.so) = 4dd79641cbad3f71466648559d74e6b0c4f174a3 Any other OpenBSD users that have run into this, here? -- Nick Guenther 4B Stats/CS University of Waterloo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Fwd: Re: [GENERAL] Where art thou, plpython2.dll? (EDB installer)

On September 23, 2014 2:27:29 PM EDT, Adrian Klaver adrian.kla...@aklaver.com wrote: On 09/23/2014 11:05 AM, Nick Guenther wrote: Quoting Seref Arikan serefari...@gmail.com: On Tue, Sep 23, 2014 at 9:36 AM, Craig Ringer cr...@2ndquadrant.com wrote: Hi all I've had some issues

Re: [GENERAL] Watching Views

Ah! Your reply was excellent, David. I only found it now, cleaning out my inbox. Comments inline! Quoting David G Johnston david.g.johns...@gmail.com: Nick Guenther wrote As you said, attaching the trigger to a view is useless (for BEFORE/AFTER, which I'm interested in, also only works

Re: [GENERAL] Feature request: temporary schemas

On September 14, 2014 5:01:54 PM EDT, cowwoc cow...@bbs.darktech.org wrote: Hi, I'd like to propose the ability to create temporary schemas. This would facilitate running unit tests, where developers would like to run the same creation script for unit tests and production code but do not What

Re: [GENERAL] Feature request: temporary schemas

On September 14, 2014 6:01:15 PM EDT, cowwoc cow...@bbs.darktech.org wrote: On 14/09/2014 5:52 PM, Nick Guenther [via PostgreSQL] wrote: On September 14, 2014 5:01:54 PM EDT, cowwoc [hidden email] /user/SendEmail.jtp?type=nodenode=5819003i=0 wrote: Hi, I'd like to propose the ability

Re: [GENERAL] change the value of unix_socket_directories , must used -h /xx/xx to use the Unix domain socket

On August 16, 2014 11:41:02 AM EDT, lin jlu...@163.com wrote: Hi all, I change the value of unix_socket_directories in postgresql.conf , then restart the database, but it cannot connect the database used like this psql -d postgres -p 5432 , it must given the parameter -h /xx/xx to use the

Re: [GENERAL] Watching Views

Quoting David G Johnston david.g.johns...@gmail.com: Nick Guenther wrote Dear List, In principle, I am looking for some way to say ``` CREATE VIEW view13131 AS select (id, name, bank_account) from actors where age 22; WATCH view13131; ``` and get output to stdout like ``` INSERT

[GENERAL] Watching Views

I even set triggers on views? Thanks in advance -Nick Guenther 4B Stats/CS University of Waterloo [1] Cubes http://cubes.databrewery.org/ [2] CouchDB. Filtered Replication. http://couchdb.readthedocs.org/en/latest/replication/protocol.html#filter-replication [3] https://github.com/maxogden

[GENERAL] Why does autovacuum clean fewer rows than I expect?

Hi, I'm having trouble making sense of the these two autovacuum log entries. I'm running PostgreSQL 8.4. [2014-05-22 04:56:43.486 EST] {537cf2c6.30f9} LOG: automatic vacuum of table postgres.globaldicom.requests: index scans: 1 pages: 0 removed, 163600 remain tuples: 5916

Re: [GENERAL] Why does autovacuum clean fewer rows than I expect?

at 5:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Nick Cabatoff nick.cabat...@gmail.com writes: I'm having trouble making sense of the these two autovacuum log entries. I'm running PostgreSQL 8.4. 8.4.what? It'd probably be a good idea to monitor the counts in pg_stat_all_tables

Re: [GENERAL] Why does autovacuum clean fewer rows than I expect?

We've been running 9.2 on our later branches for a while now. We're overdue to bump the older branches to a later 8.4 though, you're right. Thanks for the reminder. On Thu, Jul 3, 2014 at 7:46 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Fri, Jul 4, 2014 at 7:41 AM, Nick Cabatoff

Re: [GENERAL] Patched odbc_dfw for PG = 9.2 - compiles works but has issues with NULL representation in ODBC?!?

Am 19.04.2014 12:44, schrieb Gunnar Nick Bluth: Heillo, after stumbling across compile errors for odbc_fdw I realised it was still on the 9.1. level. I've patched it, based on the tds_fdw code (so it's still read-only). It does compile, and I think it is generally sane, however I'm

[GENERAL] Patched odbc_dfw for PG = 9.2

According notes have been added to the Foreign_data_wrappers WIKI page as well (no need for more people to try compiling it ;-). Cheers, -- Gunnar Nick Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bl...@pro-open.de

[GENERAL] Inserting boolean types as an alias?

Hello I am new to this site and also a student. I am working on an assignment and was wondering if there is a way to make an alias for a boolean? For example when I am making a table called club_games and it must contain the memberID, gameID, count, and status as column fields. When I enter the

Re: [GENERAL] High RAM usage on postgres

, but it would get sent to psql of course. Regards, -- Gunnar Nick Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bl...@pro-open.de __ In 1984 mainstream users were choosing VMS over UNIX. Ten years later

Re: [GENERAL] PostgreSQL and a clustered file system

you too much, as the relevant I/O PG does is sync anyway. Just my 2p, -- Gunnar Nick Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bl...@pro-open.de __ In 1984 mainstream users were choosing VMS over UNIX. Ten years

[GENERAL] At what point does a big table start becoming too big?

I have a table with 40 million rows and haven't had any performance issues yet. Are there any rules of thumb as to when a table starts getting too big? For example, maybe if the index size is 6x the amount of ram, if the table is 10% of total disk space, etc? -- Sent via pgsql-general

Re: [GENERAL] High CPU on read-only streaming replication server

On Jul 19, 1:23 pm, Nick nboutel...@gmail.com wrote: My read-only streaming replication servers are showing a much higher CPU load than I would expect. None of my queries are longer than 10ms. My application server is connecting to this server via pgbouncer. Other than that, its a pretty

Re: [GENERAL] High CPU on read-only streaming replication server

BTW, Its version 9.1.4 on a 2.2Ghz dual processor with 17GB of ram. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] High CPU on read-only streaming replication server

My read-only streaming replication servers are showing a much higher CPU load than I would expect. None of my queries are longer than 10ms. My application server is connecting to this server via pgbouncer. Other than that, its a pretty standard setup. I haven't toyed with too many settings

Re: [GENERAL] Return equal number of rows with same column value

On Jun 4, 5:27 pm, Nick nboutel...@gmail.com wrote: For the data... INSERT INTO test (id,val) VALUES (1,a); INSERT INTO test (id,val) VALUES (2,a); INSERT INTO test (id,val) VALUES (3,a); INSERT INTO test (id,val) VALUES (4,a); INSERT INTO test (id,val) VALUES (5,b); INSERT INTO test (id

[GENERAL] Return equal number of rows with same column value

For the data... INSERT INTO test (id,val) VALUES (1,a); INSERT INTO test (id,val) VALUES (2,a); INSERT INTO test (id,val) VALUES (3,a); INSERT INTO test (id,val) VALUES (4,a); INSERT INTO test (id,val) VALUES (5,b); INSERT INTO test (id,val) VALUES (6,b); How could I return an even amount of

[GENERAL] Chaining inserts ... This would be cool

There are obviously workarounds for this, but I'm wondering why the following query shouldn't work. It seems like it should. With MVCC already present on the back-end, I can't see any reason other than additional parsing routines that this couldn't work: INSERT INTO

Re: [GENERAL] Chaining inserts ... This would be cool

the syntax or equivalent to what I originally posted. Thanks for the help anyway. On Mon, Apr 23, 2012 at 4:19 PM, Thom Brown t...@linux.com wrote: On 23 April 2012 21:49, Nick Apperson apper...@gmail.com wrote: There are obviously workarounds for this, but I'm wondering why the following

[GENERAL] Error trying to set up streaming replication

I followed the instructions from http://packetcloud.net/2010/12/09/setting-up-streaming-replication-in-postgresql-9-0/ When I start the standby I get this... LOG: database system was shut down in recovery at 2012-03-26 17:36:32 PDT LOG: entering standby mode cp: cannot stat

[GENERAL] Yearly date comparison?

What is the best way to find an event with a yearly occurrence? CREATE TABLE events ( start_date DATE, end_date DATE, recurring TEXT ); INSERT INTO events (start_date, end_date, recurring) VALUES ('2010-02-28','2010-03-01','yearly'); SELECT * FROM events WHERE (start_date+'2

[GENERAL] Help speeding up a left join aggregate

I have a pretty well tuned setup, with appropriate indexes and 16GB of available RAM. Should this be taking this long? I forced it to not use a sequential scan and that only knocked a second off the plan. QUERY PLAN

[GENERAL] How to know if update is from a foreign key cascade in plperl?

Is it possible (in a plperl function) to know if an update is from a foreign key cascade, rather than just a user submitted update statement? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] Whats the most efficient query for this result?

I have three tables (users, books, pencils) and would like to get a list of all users with a count and total price of their books and pencils for 2012-01-01... So with this data... users (user_id) 1 2 3 books (user_id, price, created) 1 | $10 | 2012-01-01 1 | $10 | 2012-01-01 3 | $10 |

Re: [GENERAL] Whats the most efficient query for this result?

On Jan 17, 3:33 am, t...@audioboundary.com (Tom Molesworth) wrote: Hi Nick, On 17/01/12 00:18, Nick wrote: I have three tables (users, books, pencils) and would like to get a list of all users with a count and total price of their books and pencils for 2012-01-01... So

Re: [GENERAL] Whats the most efficient query for this result?

On Jan 17, 3:33 am, t...@audioboundary.com (Tom Molesworth) wrote: Hi Nick, On 17/01/12 00:18, Nick wrote: I have three tables (users, books, pencils) and would like to get a list of all users with a count and total price of their books and pencils for 2012-01-01... So

Re: [GENERAL] Whats the most efficient query for this result?

On Jan 17, 3:33 am, t...@audioboundary.com (Tom Molesworth) wrote: Hi Nick, On 17/01/12 00:18, Nick wrote: I have three tables (users, books, pencils) and would like to get a list of all users with a count and total price of their books and pencils for 2012-01-01... So

Re: [GENERAL] Postgresql allow connections without password [SOLVED]

Thanks raymond, you were right, i never think that psql was using .pgpass file. When a delete it psql ask me for the password. THANKS I’ll never figure that out. 2012/1/13 Raymond O'Donnell r...@iol.ie On 12/01/2012 05:16, debian nick wrote: I have postgresql 8.4.9 installed now, my problem

[GENERAL] Postgresql allow connections without password

I have postgresql 8.4.9 installed now, my problem is that from time to time my postgresql let psql version 8.4.9 access the database without asking for password (psql -d mydatabase -h myhost -U myuser), and the connection attempts from psql 8.3 are not allowed no matter what i got time out

[GENERAL] Pl/pgsql function

I am implementing some pl/pgsql functions. Is there any way to change the input for example- I got some value by $1. I want to modify this value (means split that value), Can we do this and how? Second thing, Suppose i defined a function test as select test('geom',the_geom,time) from tablename

[GENERAL] Index Size

index is becoming so large? How to compress or reduce its size? Thanks Nick

Re: [GENERAL] Index Size

On Tue, May 31, 2011 at 8:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: Craig Ringer cr...@postnewspapers.com.au writes: On 05/30/2011 08:53 PM, Nick Raj wrote: Cube code provided by postgres contrib folder. It uses the NDBOX structure. On creating index, it's size increase at a high rate

Re: [GENERAL] disable seqscan

On Mon, May 23, 2011 at 7:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Nick Raj nickrajj...@gmail.com writes: Andrew Sullivan a...@crankycanuck.ca wrote: It sounds like your index can't actually be used to satisfy your query. Without seeing the table definition, index definition, and query

[GENERAL] disable seqscan

postgres to use index scan? Thanks Nick

Re: [GENERAL] disable seqscan

On Mon, May 23, 2011 at 5:44 PM, Andreas Kretschmer akretsch...@spamfence.net wrote: Andrew Sullivan a...@crankycanuck.ca wrote: On Mon, May 23, 2011 at 05:31:04PM +0530, Nick Raj wrote: Hi, I have build an index. When, i execute the query, it gives the result by sequential scan

[GENERAL] Toast datum

Hi, #define DatumGetNDBOX(x)((NDBOX*)DatumGetPointer(x)) #define PG_GETARG_NDBOX(x)DatumGetNDBOX( PG_DETOAST_DATUM(PG_GETARG_DATUM(x)) ) Now i have to define #define NDBOXGetDatum(x) ()PointerGetDatum(x) Is there any need to replace this ?? with some toastable thing or is it

[GENERAL] arguments are not toastable

Hi, I have defined some function and also used NDBOX structure that having variable length. typedef struct NDBOX { int32vl_len_;/* varlena length */ unsigned int dim; doublex[1]; } NDBOX; When i called my function, it gives NDBOX to be null On debugging, i

Re: [GENERAL] Debug Contrib/cube code

, 2011 at 6:42 AM, Joshua Tolley eggyk...@gmail.com wrote: On Fri, May 06, 2011 at 10:43:23AM +0530, Nick Raj wrote: Hi, I am using postgresql-8.4.6. I want to debug the contrib/cube code. Can we able to debug that cube code? Because there is no .configure file to enable debug

[GENERAL] Debug Contrib/cube code

Hi, I am using postgresql-8.4.6. I want to debug the contrib/cube code. Can we able to debug that cube code? Because there is no .configure file to enable debug. Is there is any way to change make file to enable debug? Thanks Nick

[GENERAL] Defining input function for new datatype

from integer without a cast ../../../include/utils/timestamp.h:205: note: expected ‘FunctionCallInfo’ but argument is of type ‘unsigned int’ Can anybody figure out what kind of mistake i am doing? Also, why it got related to 'FunctionCallInfo' ? Thanks Nick

Re: [GENERAL] Typecast

Thanks dude On Mon, Apr 18, 2011 at 2:25 PM, Chetan Suttraway chetan.suttra...@enterprisedb.com wrote: On Fri, Apr 15, 2011 at 10:29 PM, Nick Raj nickrajj...@gmail.com wrote: Hi, Can anybody tell me how to typecast data type Point into Datum? Thanks Nick Assuming you are referring

[GENERAL] Typecast

Hi, Can anybody tell me how to typecast data type Point into Datum? Thanks Nick

[GENERAL] Global Variables in plpgsql

Hi, Can anyone know how to define global variable in plpgsql? Thanks Regards, Raj

[GENERAL] Integrating New Data Type

Hi all, I have defined a new data type. I have defined in and out function for that data type. But i want to know how to integrate this data type with postgres (how postgres compile my code or know my datatype) ? Thanks, Nirmesh

[GENERAL] Understanding Datum

Hi, I am understanding the postgres code. In code, i just want to see what are values that are passing through the variables? Can you please tell me if the variable is of type Datum, then how to print its value? Because i dont the variable v type. And also what the structure of Datum? Thanks,

Re: [GENERAL] Understanding Datum

?? How to find out what type of pointer argument is PG_GETARG_POINTER(1)?? Thanks, Nirmesh On Wed, Mar 23, 2011 at 11:40 PM, Radosław Smogura m...@smogura.eu wrote: Nick Raj nickrajj...@gmail.com Wednesday 23 March 2011 18:45:41 Hi, I am understanding the postgres code. In code, i just want

Re: [GENERAL] Understanding Datum

in postgres? On Thu, Mar 24, 2011 at 2:35 AM, Tom Lane t...@sss.pgh.pa.us wrote: Nick Raj nickrajj...@gmail.com writes: In postgres, typedef uintptr_t Datum Datum is getting value from PG_GETARG_POINTER(1); But, now problem is how would i know the type of PG_GETARG_POINTER(1) (postgres

[GENERAL] OT: Oleg Bartunov in Himalaya...

reject the reflex to post this here, wow... Please excuse all the best, Nick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Composite index structure

Hi all, I want to construct an Composite Index Structure i.e. a combination of gist and btree. What i am thinking is that first creating a Rtree structure that is pointing to another Btree structure. For example, Suppose i want to find vehicles between 2 to 4 pm on 14/2/2011 on X road. I am

[GENERAL] data type

Hi, I am writing some function in postgres pl/sql. My function is of type St_ABC((select obj_geom from XYZ),(select boundary_geom from boundary)) I have table XYZ with 20,000 tuples and in boundary, i have only one geometry. In postgres, ST_intersects(obj_geom, boundary_geom) checks each

[GENERAL] Are there any projects interested in object functionality? (+ rule bases)

constrain this in regard of performance? Thanks a lot in advance, Nick

Re: [GENERAL] Are there any projects interested in object functionality? (+ rule bases)

allow them to use PostgreSQL for that sake. So I am meaning OO in a very broad sense. All the best, Nick On 01/21/2011 04:10 PM, Andy Colson wrote: Short answer: no. Here are some counter questions for you: Have you ever seen any actual real world usage of OORDBMS? Are there any products

Re: [GENERAL] Are there any projects interested in object functionality? (+ rule bases)

architecture will do really significantly slower than OODBMS/ORDBMS more specialized for that sake (the same with rules...)?? Or is it worth to give it a try? This would be my last resort, Nick Although postgres at one time had ORDBMS as a goal, I haven't seen any real interest or work

[GENERAL] Thoughts on a surrogate key lookup function?

Are there any existing trigger functions (preferably C) that could retrieve a missing value for a compound foreign key on insert or update? If this overall sounds like a really bad idea, please let me know as well. This functionality could really speed my project up though. For example, CREATE

Re: [GENERAL] Thoughts on a surrogate key lookup function?

or insert) I can bypass a much more complex insert statement. Is this frowned upon? I havent had many issues (but some ive been able to work around) with this as a plperl trigger and am pleased with how much easier it makes my inserts (besides the execution speed). -Nick On Nov 6, 6:28 am, mmonc

Re: [GENERAL] It it possible to get this result in one query?

Thanks Guy, is it possible to get the 3rd column result as an array instead of string? -Nick On Oct 14, 9:27 pm, guyr-...@burntmail.com (Guy Rouillier) wrote: Sure: select     t3.id,     coalesce        (        t1.title,        t2.title,        t3.title        ),     coalesce

Re: [GENERAL] It it possible to get this result in one query?

... 1 | new one | [table_one,table_three] 2 | new two | [table_two,table_three] 3 | three | [table_three] On Oct 14, 4:49 pm, Nick nboutel...@gmail.com wrote: Is it possible to get the results of this snip of a function without using a function? All tables include an id and title column. tables

[GENERAL] It it possible to get this result in one query?

Is it possible to get the results of this snip of a function without using a function? All tables include an id and title column. tables := ARRAY[table_one,table_two,table_three]::VARCHAR; CREATE TEMP TABLE final_results (id INTEGER, title VARCHAR, r_types VARCHAR[]); FOR t IN

Re: [GENERAL] It it possible to get this result in one query?

Found a solution for what I need. Please let me know if you know of something better/faster. -Nick CREATE AGGREGATE array_accum (anyelement) ( sfunc = array_append, stype = anyarray, initcond = '{}' ); SELECT id, title, array_accum(t) AS ts FROM ( SELECT 'table_one' AS t, id, title

Re: [GENERAL] Is there a bug in FOR i IN 1..10 LOOP (8.4.4)?

Woops, figured it out. Just needed to declare the num_var := ''; On Sep 11, 10:45 pm, Nick nboutel...@gmail.com wrote: CREATE FUNCTION test() RETURNS text     LANGUAGE plpgsql     AS $$DECLARE   num_var TEXT; BEGIN   FOR i IN 1..10 LOOP     num_var := num_var || ',' || i;   END LOOP

[GENERAL] Is there a bug in FOR i IN 1..10 LOOP (8.4.4)?

CREATE FUNCTION test() RETURNS text LANGUAGE plpgsql AS $$DECLARE num_var TEXT; BEGIN FOR i IN 1..10 LOOP num_var := num_var || ',' || i; END LOOP; RETURN num_var; END;$$; SELECT test(); returns NULL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

[GENERAL] Regular expression that splits CSV string into table

What would be the regexp_split_to_table pattern that splits a comma separated string into a table? Im having trouble when a string contains commas or there are commas at the beginning or end String ',one,two,''three,four'',five,six,' Should return ,one two three,four five six, -- Sent via

Re: [GENERAL] Regular expression that splits CSV string into table

Yes, that gets down to the root of my question... what is the expression that would properly split the values? -Nick On Sep 10, 4:43 pm, brick...@gmail.com (bricklen) wrote: On Fri, Sep 10, 2010 at 3:43 PM, Nick nboutel...@gmail.com wrote: What would be the regexp_split_to_table pattern

Re: [GENERAL] Regular expression that splits CSV string into table

I dont mind if the commas are at the beginning and end, im more concerned about three,four staying in one row because its surrounded by quotes. -Nick On Sep 10, 6:03 pm, alvhe...@commandprompt.com (Alvaro Herrera) wrote: Excerpts from Nick's message of vie sep 10 20:36:24 -0400 2010: Yes

Re: [GENERAL] Dynamically update NEW columns in plpgsql trigger

On Sep 9, 2:21 am, dmit...@gmail.com (Dmitriy Igrishin) wrote: Hey Nick, You may do it with PL/pgSQL more easily with hstore module. Please, refer tohttp://www.postgresql.org/docs/9.0/static/hstore.html Please, look at the hstore(record) and populate_record(record, hstore) function. Hope

[GENERAL] Dynamically update NEW columns in plpgsql trigger

I need to dynamically update NEW columns. Ive been inserting the NEW values into a temp table, updating them, then passing the temp table values back to NEW (is there a better way?). Ive had success with this method unless there is a null value... EXECUTE 'CREATE TEMP TABLE new AS SELECT $1.*'

Re: [GENERAL] On insert duplicate row, return existing key

Anyone? Please On Jul 31, 12:36 pm, Nick nboutel...@gmail.com wrote: If I insert a duplicate row into a table, id like to return the existing key. I tried creating a rule for this... CREATE RULE no_duplicates AS ON INSERT TO names WHERE EXISTS (SELECT 1 FROM names WHERE new.name = name) DO

[GENERAL] Find users that have ALL categories

Is this the most efficient way to write this query? Id like to get a list of users that have the categories 1, 2, and 3? SELECT user_id FROM user_categories WHERE category_id IN (1,2,3) GROUP BY user_id HAVING COUNT(*) = 3 users_categories (user_id, category_id) 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 3 |

Re: [GENERAL] Problem with query using ST_Dwithin

On Jan 28, 4:32 pm, Nick nick.uebel...@noaa.gov wrote: The following query's all work fine, select distinct zoa_metar_xml.stn_id, zoa_metar_xml.metar_txt, zoa_metar_xml.time, zoa_metar_xml.flt_cat, zoa_metar_xml.cld_cvr, zoa_metar_xml.cld_base, zoa_metar_xml.lonlat, zoa_metar_xml.geom from

[GENERAL] Problem with query using ST_Dwithin

with the zoa_sectors_basetop.geom. So looks like the problem may lie in the geometry in the table zoa_metar_xml. But can't figure why? Hope this is enough info, can anyone help? Thanks, Nick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

[GENERAL] Index not used when using a function

SELECT * FROM locations WHERE id = 12345 LIMIT 1 uses the primary key (id) index, but... SELECT * FROM locations WHERE id = get_location_id_from_ip(641923892) LIMIT 1 does not and is verrry slow. Any ideas why? Whats weird is that it works (uses index) on a previous db, but when I copied

  1   2   >