Re: [GENERAL] How do I calculate the sum of a field filtered by multiple windows defined by another field?

2015-03-15 Thread Robert James
Version 9.2.4 On 3/15/15, David G. Johnston wrote: > On Sunday, March 15, 2015, Robert James wrote: > >> How do I calculate the sum of a field filtered by multiple windows >> defined by another field? >> >> I have table event with fields event_date, num_events

[GENERAL] Preserving formatting and comments in a VIEW definition

2015-03-15 Thread Robert James
When I save a VIEW, Postgres seems to convert it to a different format, functionally equivalent but unrecognizable (whitespace, comments, adds lots of casts, etc.) Is there any simple way to preserve my original code? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

[GENERAL] How do I calculate the sum of a field filtered by multiple windows defined by another field?

2015-03-15 Thread Robert James
How do I calculate the sum of a field filtered by multiple windows defined by another field? I have table event with fields event_date, num_events, site_id. I can easily use aggregate SQL to do SELECT SUM(num_events) GROUP BY site_id. But I also have another table site with fields site_id, target

[GENERAL] Understanding setof

2014-04-04 Thread Robert James
I'm having trouble with setof returning functions Let's say I have function x() returning setof integers. I want to do SELECT x(), but only keep the values which meet a criteria. Something like: SELECT x() as xval WHERE xval = 10. How can I do that? In general, is there a way to "unroll" a set

[GENERAL] pg_dump of only range of tables

2013-10-28 Thread Robert James
Is there any way to do a pg_dump (or equivalent) of only part of a table? Say I want to send data to someone for only part of the table (expressable with a WHERE clause). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgr

Re: [GENERAL] Work table

2013-10-28 Thread Robert James
o set $1 and $2. I get the same behavior when I try just its SQL, no function. On 10/27/13, Adrian Klaver wrote: > On 10/27/2013 02:48 PM, Robert James wrote: >> On 10/27/13, Adrian Klaver wrote: > > >>>> Is there another problem here? Perhaps something to do with

Re: [GENERAL] Work table

2013-10-27 Thread Robert James
On 10/27/13, Adrian Klaver wrote: > On 10/27/2013 02:23 PM, Robert James wrote: >> On 10/27/13, Thomas Kellerer wrote: >>> Robert James wrote on 27.10.2013 20:47: >>>> I'm using Postgres for data analysis (interactive and batch). I need >>>> to f

Re: [GENERAL] Work table

2013-10-27 Thread Robert James
On 10/27/13, Thomas Kellerer wrote: > Robert James wrote on 27.10.2013 20:47: >> I'm using Postgres for data analysis (interactive and batch). I need >> to focus the analysis on a subset of one table, and, for both >> performance and simplicity, have a function which

[GENERAL] Work table

2013-10-27 Thread Robert James
I'm using Postgres for data analysis (interactive and batch). I need to focus the analysis on a subset of one table, and, for both performance and simplicity, have a function which loads that subset into another table (DELETE FROM another_table; INSERT INTO another_table SELECT ...). Oddly enough

[GENERAL] Unique - first

2013-10-27 Thread Robert James
I have a table (x,y,z) - I'd like to take the rows with unique x values - but, when more than one row have the same x value, I want the one with the minimal z value. How can I do that? I can imagine doing it with window functions, but also that regular SQL should be able to do it too. -- Sent v

Re: [GENERAL] Detecting change in event properties

2013-10-25 Thread Robert James
On 10/25/13, Tom Lane wrote: > Robert James writes: >>> (To elaborate, I'm interested in: >>> * Finding field x of the _previous_ row >>> * Finding field x of the _next_ row >>> * Finding field x of the _previous_ row that meets a certain criteria &g

Re: [GENERAL] Detecting change in event properties

2013-10-25 Thread Robert James
On 10/25/13, Robert James wrote: > I have a table of (timed) events, and I'm interested in marking events > whose properties have changed from the previous event. > > I believe this can be done with window functions, but I'm not sure > how. What window function can

[GENERAL] Window functions and relational algebra

2013-10-25 Thread Robert James
I'm just discovering the power of window functions. Is there any mathematical formalism for them, similar to relational algebra? It would seem to me that window functions aren't expressable in pure relational algebra, but that a well defined extension to it would be possible to express them; at

[GENERAL] Detecting change in event properties

2013-10-25 Thread Robert James
I have a table of (timed) events, and I'm interested in marking events whose properties have changed from the previous event. I believe this can be done with window functions, but I'm not sure how. What window function can give me a field from the _previous_ row? (To elaborate, I'm interested in

Re: [GENERAL] Count of records in a row

2013-10-25 Thread Robert James
Ingenious! I actually think, however, there was a subtle bug in, though I see you fixed it. The line: - row_number() over () as d needs to be: - row_number() over (order by i asc) as d I discovered this when working your code into my application. I got very, very wei

Re: [GENERAL] Count of records in a row

2013-10-24 Thread Robert James
On 10/22/13, Elliot wrote: > It looks like you already found a solution, but here's one with a CTE. I > cobbled this together from an older query I had for doing something > similar, for which I unfortunately lost the original source of this > approach. Also, this implies that there is something t

Re: [GENERAL] Count of records in a row

2013-10-22 Thread Robert James
Wow, this is an excellent discussion - and I must admit, a bit beyond my abilities. Is there a consensus as to the best approach to adopt? Is Elliot's the best? On 10/22/13, Rémi Cura wrote: > OK, > just out of pure curiosity, > is it always the case or is it due to this particular aggregate? >

Re: [GENERAL] Count of records in a row

2013-10-22 Thread Robert James
On 10/22/13, Rémi Cura wrote: > But it is immensely easier and sometimes mandatory to use instead > a plpgsql function using cursor (or cursors). > > It would be something like that in plpgsql : > > cursor on table of letter ordered > accum = 0; > loop on rows of table ordered > > if letter = prev

[GENERAL] Count of records in a row

2013-10-21 Thread Robert James
I have a table of event_id, event_time. Many times, several events happen in a row. I'd like a query which replaces all of those events with a single record, showing the count. Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1; D,1; A,2; D,2; B,1; C,2 How can I do that? --

[GENERAL] Column names for INSERT with query

2013-08-22 Thread Robert James
I would like to: INSERT INTO t SELECT * FROM r, (x + y) AS field3 How do I correlate the names of the fields? That is, how do I indicate which fields from r or field3 should be inserted into the right columns in t? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make c

[GENERAL] Denormalized field

2013-08-17 Thread Robert James
I have a slow_function. My table has field f, and since slow_function is slow, I need to denormalize and store slow_function(f) as a field. What's the best way to do this automatically? Can this be done with triggers? (On UPDATE or INSERT, SET slow_function_f = slow_function(new_f) ) How? Will c

[GENERAL] Postgres cron job

2013-08-17 Thread Robert James
Is there a way to do a Postgres internal cron job? That is, do something every X minutes? Yes: Of course I can do this using cron. But I'd like to be able to manage this from within Postgres. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscrip

[GENERAL] Does string a begin with string b?

2013-08-16 Thread Robert James
What's the best way to check if string a begins with string b? Both a and b are coming from fields in a table. Requirements: * Either a or b might have special chars (such as '%') in them which should NOT do anything special - they're just plain strings, not regular expressions. * a and b can be

[GENERAL] Using an index to materialize a function

2013-08-16 Thread Robert James
If I have a slow_function(), and I create an index of slow_function(field), will Postgres use that index to avoid having to recompute the function? Example: SELECT slow_function(field1) FROM table1 WHERE id = 5 It won't use the index on field1 to _find_ the record. Can it use it to compute the

[GENERAL] Forcing materialize in the planner

2013-08-15 Thread Robert James
I have a query which, when I materialize by hand some of its components, runs 10x faster (including the time needed to materialize). Is there any way to force Postgres to do that? Or do I need to do this by hand using temp tables? -- Sent via pgsql-general mailing list (pgsql-general@postgresql

Re: [GENERAL] Escape string for LIKE op

2013-08-15 Thread Robert James
On 8/15/13, Jeff Janes wrote: > On Thu, Aug 15, 2013 at 1:16 PM, Robert James > wrote: >> How can I escape a string for LIKE operations? >> >> I want to do: >> >> SELECT * FROM t WHERE a LIKE b || '%' >> >> But I want be to interpreted l

[GENERAL] Escape string for LIKE op

2013-08-15 Thread Robert James
How can I escape a string for LIKE operations? I want to do: SELECT * FROM t WHERE a LIKE b || '%' But I want be to interpreted literally. If b is 'The 7% Solution', I don't want that '%' to be wildcard. I can't find an appropriate function to escape it and any other potential wildcards for LI

Re: [GENERAL] What type of index do I need for this JOIN?

2013-08-14 Thread Robert James
On 8/14/13, Kevin Grittner wrote: > Robert James wrote: > >> I'm confused: What's the difference between >> col LIKE 'foo%' >> and >> col LIKE f1 || '%' >> ? > > The planner knows that 'foo%' doesn'

Re: [GENERAL] What type of index do I need for this JOIN?

2013-08-13 Thread Robert James
On 8/13/13, Craig Ringer wrote: > On 08/14/2013 06:05 AM, Robert James wrote: >> I'm doing a JOIN which is very slow: >> >> JOIN t ON t.f1 LIKE (q.f1 || '%') >> >> t1 has an INDEX on (f1, f2) which I thought would help for this. But >> Postgr

Re: [GENERAL] SORT and Merge Join via Index

2013-08-13 Thread Robert James
On 8/13/13, Robert James wrote: > I noticed that when I have an index on (a,b) of table t, and I do an > SELECT * FROM t ORDER BY a ASC, it doesn't use the index. When I > create a new index of only a, it does use the index. Why is that? > > And, more importantly, when I do

[GENERAL] SORT and Merge Join via Index

2013-08-13 Thread Robert James
I noticed that when I have an index on (a,b) of table t, and I do an SELECT * FROM t ORDER BY a ASC, it doesn't use the index. When I create a new index of only a, it does use the index. Why is that? And, more importantly, when I do a query involving a merge join of table t, which requires sorti

[GENERAL] What type of index do I need for this JOIN?

2013-08-13 Thread Robert James
I'm doing a JOIN which is very slow: JOIN t ON t.f1 LIKE (q.f1 || '%') t1 has an INDEX on (f1, f2) which I thought would help for this. But Postgres seems to still use a (very slow) Nested Loop. What type of index would be appropriate for this? (My goal is to join on a substring starting from

[GENERAL] Materializing a view by hand

2013-08-13 Thread Robert James
I have a view which is very slow to computer, but doesn't change often. I'd like to materialize it. I thought I'd do a simple poor man's materialize by: 1) ALTER VIEW myview RENAME to _myview 2) SELECT * INTO myview FROM _myview The only problem is that all my other views, which are dependent on

[GENERAL] Parameter for query

2013-07-16 Thread Robert James
Is there any way to set a variable or parameter for a query? I have a long query where a certain variable needs to be easy to change. I'd like to do something like: threshold = 10.3 SELECT... WHERE x > $threshold... AND y * 1.3 > $threshold... Currently, I need to do this in a scripting langua

[GENERAL] Return cols and rows via stored procedure

2013-07-14 Thread Robert James
I'd like a stored procedure which does something like: INSERT INTO... SELECT... -- This should be returned as multicolumn, multifield - just like a table or view When I run it, though, instead of getting a table, I get one field with all the data in it as a compound type. I'd like to return the

Re: [GENERAL] Longest Common Subsequence in Postgres - Algorithm Challenge

2013-07-08 Thread Robert James
On 7/8/13, hubert depesz lubaczewski wrote: > On Mon, Jul 08, 2013 at 09:09:26AM -0400, Robert James wrote: >> I have two relations, where each relation has two fields, one >> indicating a name and one indicating a position. That is, each >> relation defines a sequence. >

[GENERAL] Longest Common Subsequence in Postgres - Algorithm Challenge

2013-07-08 Thread Robert James
I have two relations, where each relation has two fields, one indicating a name and one indicating a position. That is, each relation defines a sequence. I need to determine their longest common subsequence. Yes, I can do this by fetching all the data into Java (or any other language) and comput

[GENERAL] Computing count of intersection of two queries (Relational Algebra --> SQL)

2013-07-07 Thread Robert James
In relational algebra, I have relation R and relation S, and want to find the cardinality of R, of S, and of R-intersect-S. I know the SQL for R and S. What's the best way to compute the cardinality of each relation (query) and of their intersection? -- Sent via pgsql-general mailing list (pgs

Re: [GENERAL] Understanding TIMESTAMP WITH TIME ZONE

2013-01-20 Thread Robert James
On 1/18/13, Steve Crawford wrote: > On 01/18/2013 09:31 AM, Robert James wrote: >> I'd like to better understand TIMESTAMP WITH TIME ZONE. >> >> My understanding is that, contrary to what the name sounds like, the >> time zone is never stored. It simply stores

Re: [GENERAL] Temp table's effect on performance

2013-01-18 Thread Robert James
On 1/18/13, Tom Lane wrote: > Jeff Janes writes: >> On Fri, Jan 18, 2013 at 9:29 AM, Robert James >> wrote: >>> In other words: Since my query is 100% identical algebraicly to not >>> using a temp table, why is it so much faster? Why can't the pl

[GENERAL] Understanding TIMESTAMP WITH TIME ZONE

2013-01-18 Thread Robert James
I'd like to better understand TIMESTAMP WITH TIME ZONE. My understanding is that, contrary to what the name sounds like, the time zone is never stored. It simply stores a UTC timestamp, identical to what TIMESTAMP WITHOUT TIME ZONE stores. And then the only difference is that WITH TIME ZONE will

[GENERAL] Temp table's effect on performance

2013-01-18 Thread Robert James
I'd like to understand better why manually using a temp table can improve performance so much. I had one complicated query that performed well. I replaced a table in it with a reference to a view, which was really just the table with an inner join, and performance worsened by 2000x. Literally.

[GENERAL] argument of AND must not return a set when using regexp_matches

2013-01-16 Thread Robert James
I've been getting a funny SQL error, which I've boiled down to this case. SELECT (regexp_matches('abc', '(.)b(.)'))[1] IS NOT NULL -- Returns true, as expected SELECT (regexp_matches('abc', '(.)b(.)'))[1] IS NOT NULL AND true -- Gives this error: ERROR: argument of AND must not return a set SQL s

[GENERAL] Independent backups of subdatabases

2013-01-15 Thread Robert James
I'd like to organize tables into different groups. This is for 3 reasons: 1. Each group needs to be backed up and restored independently from the others 2. Each group has different permissions for particular users 3. As an aid to human understanding organization I would do this as actual database

Re: [GENERAL] INSERT... WHERE

2013-01-14 Thread Robert James
On 1/13/13, Chris Angelico wrote: > On Mon, Jan 14, 2013 at 3:37 PM, Robert James > wrote: >> Thanks. But how do I do that where I have many literals? Something like: >> >> INSERT INTO seltest (id, a, b) SELECT (1,2,3),(4,5,6),(7,8,9) WHERE b >> IN (SELECT ...) &

Re: [GENERAL] INSERT... WHERE

2013-01-13 Thread Robert James
On 1/13/13, Ian Lawrence Barwick wrote: > 2013/1/14 Robert James : >> I have a lot of VALUES I want to INSERT. But only a subset of them - >> only those that meet a JOIN criteria involving another table. >> >> I could INSERT them into a temp table, and then do a SELECT

[GENERAL] Bulk INSERT with individual failure

2013-01-13 Thread Robert James
I need to INSERT a large number of records. For performance reasons, I'd rather send them to Postgres in one giant INSERT. However, if there's a problem in one record (eg one row doesn't meet a constraint), I'd still like the others saved. That is, I specifically DO NOT want atomic behavior. It

[GENERAL] Combining validation into main query

2013-01-12 Thread Robert James
Typically, my web application does some initial validation, then, if it passes, does the actual query. For both performance and simplicity, I'd like to combine these all into one trip to Postgres. Ideally, I'd like to do this in SQL. If that's not possible, I could use PL/pgsql, though I'm not ad

Re: [GENERAL] Picking the first of an order in an aggregate query

2012-12-31 Thread Robert James
On 12/31/12, François Beausoleil wrote: > > Le 2012-12-31 à 15:38, Robert James a écrit : > >> DISTINCT is a very simple solution! >> But I have one problem: In addition to the FIRST fields, I also do >> want some aggregate functions. More accurately, it would be:

Re: [GENERAL] Picking the first of an order in an aggregate query

2012-12-31 Thread Robert James
with a join on the grouping field? Or is there a more direct way? On 12/31/12, Jack Christensen wrote: > On 12/31/2012 8:33 AM, Robert James wrote: >> I have a query >> >>SELECT grouping_field, MIN(field_a), MIN(field_b) >>FROM ... >>GROUP BY gro

[GENERAL] Picking the first of an order in an aggregate query

2012-12-31 Thread Robert James
I have a query SELECT grouping_field, MIN(field_a), MIN(field_b) FROM ... GROUP BY grouping_field But, instead of picking the MIN field_a and MIN field_b, I'd like to pick field_a and field_b from the first record, according to an order I'll specify. In pseudo-SQL, it would be something li

Re: [GENERAL] DONT_CARE Aggregate

2012-12-20 Thread Robert James
I see. What if I need to do this along with an Aggregate Query. Eg something like: SELECT x,y,z, MAX(a), MAX(b), DONT_CARE_AS_LONG_AS_NOT_NULL(c), DONT_CAR_AS_LONG_AS_P_IS_TRUE(d,p) ... GROUP BY x,y,z On 12/20/12, Richard Broersma wrote: > On Thu, Dec 20, 2012 at 5:45 AM, Robert Ja

Re: [GENERAL] DONT_CARE Aggregate

2012-12-20 Thread Robert James
Sergey - That's an interesting option, but I'm not sure how to use it as an aggregate. Could you give an example? On 12/20/12, Sergey Konoplev wrote: > On Wed, Dec 19, 2012 at 5:28 PM, Robert James > wrote: >> And even better: >> An aggregate which will return the

[GENERAL] Moving a database to a new TABLESPACE in Postgres 8.3

2012-12-19 Thread Robert James
In Postgres 8.3, how can I move a database to a different TABLESPACE? My goal is to move rarely used databases off of the SSD and onto the HDD. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-gen

[GENERAL] Moving some of Postgres off a SSD

2012-12-18 Thread Robert James
I have Postgres running on a SSD. The data is now almost 50GB, which is filling up the drive. How can I move some of the data to my HDD? My priorities are, in this order: 1. Reliable - I don't want anything that will corrupt the data 2. Easy - I have a few dozen databases, I don't want to do too

Re: [GENERAL] Running CREATE only on certain Postgres versions

2012-09-24 Thread Robert James
act version number programatically (ie not just a long string) On 9/24/12, Igor Neyman wrote: >> -Original Message----- >> From: Robert James [mailto:srobertja...@gmail.com] >> Sent: Monday, September 24, 2012 9:33 AM >> To: Postgres General >> Subject: Running CREATE only on

[GENERAL] Running CREATE only on certain Postgres versions

2012-09-24 Thread Robert James
I have some code which creates a function in Postgres, taken from http://wiki.postgresql.org/wiki/Array_agg . DROP AGGREGATE IF EXISTS array_agg(anyelement); CREATE AGGREGATE array_agg(anyelement) ( SFUNC=array_append, STYPE=anyarray, INITCOND='{}' ); The function was added in 8.4, and so the cod

Re: [GENERAL] Disadvantage to CLUSTER?

2012-05-15 Thread Robert James
On 5/15/12, Steve Crawford wrote: > On 05/15/2012 02:02 PM, Robert James wrote: >> Besides the one time spent CLUSTERing, do I loose anything by doing it >> for every table? Does a CLUSTER slow anything down? > Cluster should have better performance but it depends on the

[GENERAL] Disadvantage to CLUSTER?

2012-05-15 Thread Robert James
Besides the one time spent CLUSTERing, do I loose anything by doing it for every table? Does a CLUSTER slow anything down? It would seem to me that a) a CLUSTER should never have worse performance than a random order b) may have better performance and c) has the benefits of a VACUUM and REINDEX.

[GENERAL] Is there a benefit to CLUSTER when retrieving individual records?

2012-05-15 Thread Robert James
I see how CLUSTER can speed up a range query (eg WHERE val < 30), because it groups those records in contiguous pages. What about where I'm only pulling one record back? Eg WHERE user_id = 100. Is there any benefit to a CLUSTER in that case? Is there anything lost if I CLUSTER on a different ind

[GENERAL] Analyze all from command line

2012-05-15 Thread Robert James
I can run clusterdb -a from the command line to cluster all databases. After clustering, its recommended to run ANALYZE. But there doesn't seem to be any way to do this from the command line, and even in SQL, there doesn't seem to be any way to do this for all databases. 1. What's the recommende

Re: [GENERAL] Memory tuning Postgres

2012-02-15 Thread Robert James
On 2/15/12, Tomas Vondra wrote: > On 15 Únor 2012, 15:20, Robert James wrote: >> What parameters should I change to use the server best? What are good >> starting points or them? What type of performance increase should I >> see? ... > But you haven't > mentione

Re: [GENERAL] Rules of Thumb for Autovaccum

2012-02-15 Thread Robert James
Thanks. What about auto-analyze? When will they be analyzed by default? And what actions generally require new analyze? On 2/15/12, Bruce Momjian wrote: > On Wed, Feb 15, 2012 at 09:14:34AM -0500, Robert James wrote: >> What rules of thumb exist for: >> * How often a table needs

[GENERAL] Memory tuning Postgres

2012-02-15 Thread Robert James
I have a 4 core, 4 GB server dedicated to running Postgres (only other thing on it are monitoring, backup, and maintenance programs). It runs about 5 databases, backing up an app, mainly ORM queries, but some reporting and more complicated SQL JOINs as well. I'm currently using the out-of-the box

[GENERAL] Index for low selectivity field

2012-02-15 Thread Robert James
A table has a column "obj_type" which has very low selectivity (let's say 5 choices, with the top choice making up 50% of records). Is there any sense in indexing that column? B-trees won't be that useful, and the docs discourage other index types/ -- Sent via pgsql-general mailing list (pgsql-g

[GENERAL] Rules of Thumb for Autovaccum

2012-02-15 Thread Robert James
What rules of thumb exist for: * How often a table needs to be vacuumed? * How often a table needs to be analyzed? * How to tune Autovacuum? I have a large DB server, and I'm concerned that it's not being autovaccumed and autoanalyzed frequently enough. But I have no idea what proper values shoul

Re: [GENERAL] Logical Aggregate Functions (eg ANY())

2011-12-17 Thread Robert James
On 12/15/11, Marti Raudsepp wrote: > On Thu, Dec 15, 2011 at 18:10, Robert James wrote: >> How do I do the equivalent of an ANY() or ALL() in PG Aggregate SQL? > > Note that in many cases, writing an EXISTS(SELECT ...) or NOT > EXISTS(...) subquery is faster, since the planner

Re: [GENERAL] CREATE OR REPLACE AGGREGATE

2011-12-17 Thread Robert James
On 12/15/11, Merlin Moncure wrote: > On Thu, Dec 15, 2011 at 11:29 AM, Robert James > wrote: >> Is there anyway to do the equivalent of CREATE OR REPLACE AGGREGATE? >> Or - maybe even better - CREATE AGGREGATE if it doesn't already exist? > > Well, you have DROP [IF

[GENERAL] CREATE OR REPLACE AGGREGATE

2011-12-15 Thread Robert James
Is there anyway to do the equivalent of CREATE OR REPLACE AGGREGATE? Or - maybe even better - CREATE AGGREGATE if it doesn't already exist? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Double Denormalizing in Postgres

2011-12-15 Thread Robert James
To match the heavily denormalized format of a legacy app, I need to take a query which gives this: name | product | rent | own Bob | Car | true | false Bob | Car | false | true Bob | Bike | false | true Bob | Truck | true | true and denormalize it into this: name | rented_products | owned_produc

[GENERAL] Logical Aggregate Functions (eg ANY())

2011-12-15 Thread Robert James
I see Postgres (I'm using 8.3) has bitwise aggregate functions (bit_or), but doesn't seem to have logical aggregate functions. How do I do the equivalent of an ANY() or ALL() in PG Aggregate SQL? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscr

[GENERAL] Controlling complexity in queries

2011-12-11 Thread Robert James
I have a very long query. Due to the planner and good indexing, it runs quite fast. But it's so long, it's quite hard to follow. I'm trying to break it up into pieces, but am running up against limits of SQL. Can you help me with any of these problems? 1. SELECT AS A, AS C, AS D ... I'd li

[GENERAL] Denormalizing via SQL

2011-12-11 Thread Robert James
I have a query returning: name | product_id Bob | 1 Bob | 2 Charles | 1 Charles | 4 To make it compatible with a legacy app, I need to convert it to this form: name | product_ids Bob | "1,2" Charles | "1,4" (Before you jump "That's not normal!" - I know. I didn't write the app. I just need to

Re: [GENERAL] Behavior of negative OFFSET

2011-11-07 Thread Robert James
On 11/7/11, Merlin Moncure wrote: > On Mon, Nov 7, 2011 at 3:47 PM, Robert James wrote: >> I've been using a query on Postgres 8.4 with a negative OFFSET, which >> works fine: >> >> SELECT DISTINCT s.* FROM s WHERE ... ORDER BY s.bday ASC, s.name >> ASC L

[GENERAL] Behavior of negative OFFSET

2011-11-07 Thread Robert James
I've been using a query on Postgres 8.4 with a negative OFFSET, which works fine: SELECT DISTINCT s.* FROM s WHERE ... ORDER BY s.bday ASC, s.name ASC LIMIT 15 OFFSET -15 When I run the same query on Postgres 9.1, I get an error: ERROR: OFFSET must not be negative Question: 1. Was this ch

[GENERAL] UTF-8 for bytea

2011-11-02 Thread Robert James
When trying to INSERT on Postgres (9.1) to a bytea column, via E'' escaped strings, I get the strings rejected because they're not UTF8. I'm confused, since bytea isn't for strings but for binary. What causes this? How do I fix this? (I know that escaped strings is not the best way for binary data

[GENERAL] Table with active and historical data

2011-06-01 Thread Robert James
I have a table with a little active data and a lot of historical data. I'd like to be able to access the active data very quickly - quicker than an index. Here are the details: 1. Table has about 1 million records 2. Has a column active_date - on a given date, only about 1% are active. active_d

[GENERAL] Postgres standard versus Postgres Plus Advanced Server

2010-02-11 Thread Robert James
Hi. I'm setting up a new workstation (Win7 64bit Quad Core 4GB) with Postgres, for development work, and trying to pick which version I should install. Most of the time, Postgres is dormant - I'm not using it all - but when I do use it, the load can be high, and I want maximum performance. Is th

Re: [GENERAL] Relational Algebra and Aggregate Functions

2009-07-28 Thread Robert James
On Tue, Jul 28, 2009 at 9:47 AM, Sam Mason wrote: > On Tue, Jul 28, 2009 at 09:14:38AM -0400, Robert James wrote: > > Many wrote that the functional programming 'fold' is a good model for > > relational aggregate functions. I have a few difficulties with this: > >

Re: [GENERAL] Relational Algebra and Aggregate Functions

2009-07-28 Thread Robert James
Thanks! "SQL and Relational Theory: How to Write Accurate SQL Code" looks like the best pick of the bunch. On Tue, Jul 28, 2009 at 10:08 AM, Michael Glaesemann wrote: > > On Jul 27, 2009, at 21:05 , Robert James wrote: > > 2) Database in Depth: Relational Theory for Pr

Re: [GENERAL] Clients disconnect but query still runs

2009-07-28 Thread Robert James
this would be a major boon to high volume servers, at least in the usage patterns I've worked with. On Mon, Jul 27, 2009 at 9:49 PM, Tom Lane wrote: > Robert James writes: > > Hi. I noticed that when clients (both psql and pgAdmin) disconnect or > > cancel, queries are of

Re: [GENERAL] Relational Algebra and Aggregate Functions

2009-07-28 Thread Robert James
Many wrote that the functional programming 'fold' is a good model for relational aggregate functions. I have a few difficulties with this: 1. fold doesn't offer any type of GROUP BY, which is an essential component of aggregation. 2. I don't believe fold can handle things like AVG() or STDDEV().

Re: [GENERAL] Relational Algebra and Aggregate Functions

2009-07-27 Thread Robert James
e inclined towards the second, but not if the first is better. One thing I'm not interested in is polemics against SQL and lamentations on how ignorant all practitioners are. On Mon, Jul 27, 2009 at 2:45 PM, Jeff Davis wrote: > On Sun, 2009-07-26 at 15:36 -0400, Robert James wrote: >

[GENERAL] Clients disconnect but query still runs

2009-07-27 Thread Robert James
Hi. I noticed that when clients (both psql and pgAdmin) disconnect or cancel, queries are often still running on the server. A few questions: 1) Is there a way to reconnect and get the results? 2) Is there a way to tell postgres to automatically stop all queries when the client who queried them d

[GENERAL] Ruuning two instances of Postgres on the same machine

2009-07-26 Thread Robert James
I'm currently running Postgres 8.2 on Windows XP. I would like to use some 8.4 features, but I don't want to migrate my 8.2. Is there any way to run both instances together? Are there any problems with that? Alternatively, is the procedure to move from 8.2 to 8.4 without data or function loss doc

[GENERAL] Relational Algebra and Aggregate Functions

2009-07-26 Thread Robert James
I'm working on improving my background database theory, to aid in practice. I've found learning relational algebra to be very helpful. One thing which relational algebra doesn't cover is aggregate functions. Can anyone recommend any papers or web pages which provide some good theoretical backgro

[GENERAL] Transitive Closure and CONNECT BY

2009-07-26 Thread Robert James
Is there a transitive closure (or equivalent) operator in Postgres (or extension)? Anything like CONNECT BY? Or any recommended way of querying hiearchial data?

[GENERAL] Help using SELECT INTO to make schema

2009-07-22 Thread Robert James
I'd like to SELECT INTO one table into another one. However, I'd like to do two things that I don't know how to do using SELECT INTO: 1. Copy over the indexes and constraints of the first table into the second 2. Do SELECT INTO even if the second table already exists. Is there anyway to do eith

Re: [GENERAL] Can LIKE under utf8 use INDEXes?

2009-07-22 Thread Robert James
Thanks - I don't show any locale: rbt_development=> \l List of databases Name | Owner | Encoding +-+-- rbt_development | rbt | UTF8 ... On Wed, Jul 22, 2009 at 6:45 PM, Andreas Wenk < a.w...@netzmeister-st-pauli.de> wr

Re: [GENERAL] Can LIKE under utf8 use INDEXes?

2009-07-22 Thread Robert James
PM, Tom Lane wrote: > Robert James writes: > > Hi. I'm confused about the behavior of LIKE under utf8 locale. > > UTF8 is not a locale, it's an encoding. If you're using C locale then > LIKE can use indexes, regardless of the encoding. If you're u

Re: [GENERAL] Documentation Improvement suggestions

2009-07-22 Thread Robert James
On Mon, Jul 20, 2009 at 11:37 AM, Martijn van Oosterhout wrote: > I know it's not easy, but a nice option to me would be if the 8.1 docs > page linked to the equivalent page in the other versions. That would > avoid the need to manually edit the URL after a google search. > > Oh, and +10 for the "

[GENERAL] Can LIKE under utf8 use INDEXes?

2009-07-22 Thread Robert James
Hi. I'm confused about the behavior of LIKE under utf8 locale. Accoding to the docs ( http://www.postgresql.org/docs/8.2/interactive/locale.html - excerpted below), it seems that LIKE ignores locale and hence can't use indexes. Yet, EXPLAIN clearly shows it using indexes. The docs suggest a worka

[GENERAL] Understanding INNER JOIN versus IN subquery

2009-07-20 Thread Robert James
I have two queries which should be equivalent. The Planner plans them differently, although they are both about the same time. Can someone explain why? select word from dict where word in (select substr('moon', 0, generate_series(3,length('moon' select * from dict inner join (select substr

[GENERAL] Documentation Improvement suggestions

2009-07-20 Thread Robert James
Two small suggestions that might make it easier for newcomers to take advantage of the wonderful database: 1. Googling Postgres docs produces links for all different versions. This is because incoming links are to different versions. Besides being confusing, it pushes the pages lower in Google, a

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-20 Thread Robert James
PM, Scott Marlowe wrote: > On Sun, Jul 19, 2009 at 6:10 PM, Robert James > wrote: > > UNION was better, but still 5 times as slow as either query done > > individually. > > set enable_seqscan=off didn't help at all - it was totally ignored > > Is there anything e

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-19 Thread Robert James
l 19, 2009 at 8:10 PM, Robert James wrote: > UNION was better, but still 5 times as slow as either query done > individually. > set enable_seqscan=off didn't help at all - it was totally ignored > Is there anything else I can do? > > On Sun, Jul 19, 2009 at 7:47 PM, Tom

Re: [GENERAL] Should I CLUSTER on PRIMARY KEY

2009-07-19 Thread Robert James
Thanks, Chris. Is there a way to do this deterministically, or at least programatically? I have code to create the tables and cluster them automatically? On Sun, Jul 19, 2009 at 8:21 PM, Chris wrote: > Robert James wrote: > >> I would like to CLUSTER a table on its PRIMARY KEY. No

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-19 Thread Robert James
UNION was better, but still 5 times as slow as either query done individually. set enable_seqscan=off didn't help at all - it was totally ignored Is there anything else I can do? On Sun, Jul 19, 2009 at 7:47 PM, Tom Lane wrote: > Robert James writes: > > Hi. I notice that when

[GENERAL] Should I CLUSTER on PRIMARY KEY

2009-07-19 Thread Robert James
I would like to CLUSTER a table on its PRIMARY KEY. Now, I haven't explicitly defined and named an index for this table - but the primary key defines one. How can I tell Postgres to CLUSTER on it? Also: If I define an index on a PK, will Postgres make a second one, or realize its redundnant? Tha

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-19 Thread Robert James
PS Running "PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" On Sun, Jul 19, 2009 at 6:58 PM, Robert James wrote: > Hi. I notice that when I do a WHERE x, Postgres uses an index, and when I > do WHERE y, it does so as well, but when I do

  1   2   >