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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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?
>
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
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?
--
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
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
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
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
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
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
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
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
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'
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
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
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
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
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
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
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
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.
>
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
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
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
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
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
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.
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
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
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 ...)
&
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
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
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
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:
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
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
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
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
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
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
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
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
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
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.
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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:
> >
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
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
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().
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:
>
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
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
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
Is there a transitive closure (or equivalent) operator in Postgres (or
extension)?
Anything like CONNECT BY?
Or any recommended way of querying hiearchial data?
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
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
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
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 "
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
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
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
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
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
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
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
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
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 - 100 of 123 matches
Mail list logo
