On Thu, Mar 25, 2010 at 2:35 PM, Tom Lane wrote:
> When you're intending to have a SQL function be inlined, it's probably
> best not to mark it as either IMMUTABLE or STRICT --- that doesn't buy
> anything and it can complicate matters as to whether inlining is legal.
I'm confused, I thought it w
The immutable property had nothing to do with caching results. Postgres
never caches the results of functions. The immutable property is used top
determine if it's safe to use indexes or other plans that avoid evaluating
an expression repeatedly.
On 6 Mar 2010 02:45, "Petru Ghita" wrote:
-BE
SELECT col FROM tab ORDER BY col DESC OFFSET 1 LIMIT 1
In 8.4 OLAP window functions provide more standard and flexibility
method but in this case it wouldn't perform as well:
postgres=# select i from (select i, rank() over (order by i desc) as r
from i) as x where r = 2;
i
99
(1 row)
post
On Tue, Sep 1, 2009 at 11:31 PM, Rob Sargent wrote:
> How many ways might one accidentally do that I wonder.
Well most operating system distributions ask you when you install them
what region you're in and use a collation for that region.
In 8.4 you can check what collation a database is set to u
On Wed, Aug 12, 2009 at 7:52 PM, Thomas Kellerer wrote:
> Ray Stell wrote on 12.08.2009 20:19:
> I would probably do it this way:
>
> SELECT tt. *
> FROM testtable tt
> WHERE create_date = (SELECT MAX(create_date)
> FROM testtable tt2
> WHERE tt.id = tt2.id);
On Tue, Jul 7, 2009 at 11:33 PM, wrote:
> After some investigation it seems that the new server is refusing to use the
> index's but if I
> limit the number of arguments in the latter part of the statement to 100 then
> it works as
> expected in the expected amount of time using the indexs.
Ugh
On Tue, Jul 7, 2009 at 10:17 AM, Simon Riggs wrote:
>
>
> Integer works best since it converts easily to boolean
>
> mybool smallint check (mybool in (0, 1))
>
> You can use "char" also, but the syntax is less clear.
Hm, I was going to suggest using boolean in postgres and making a
"boolean" domai
On Thu, Jul 2, 2009 at 3:48 PM, Jasmin
Dizdarevic wrote:
> customer ; seg
> 111 ; L1
> 111 ; L2
> 111 ; L1
> 222 ; L3
> 222 ; L3
> 222 ; L2
>
> the result should look like this:
>
> 111: L1 - because L1 is higher than L2 and ratio of L1 : L2 is 2 : 1
> 222: L3 - because L3 is higher than L2 and rat
On Sat, Jun 13, 2009 at 12:12 AM, Erik Jones wrote:
>
> On Jun 9, 2009, at 10:51 AM, Rob Sargent wrote:
>
>> Caching helps a *lot* and I'm thankful for that but I would like to take
>> it out of the picture as I massage my queries for better performance.
>> Naturally the first invocation of the qu
Huh, I didn't realize that ever worked in the past. I thought the way
to do what the op describes was to cast it to text[] or whatever
datatype you from out-of-band knowledge to expect.
--
Greg
On 13 Dec 2008, at 19:38, Tom Lane wrote:
Corey Horton writes:
I'm trying to use array_to_st
"Aaron Bono" <[EMAIL PROTECTED]> writes:
> I haven't stared at your query as long as you have so I may have missed
> something
Likewise I'm perhaps speaking too quickly, but at the risk of making a fool of
myself: you should perhaps realize that UNION has to do a fair amount of work
to eliminate
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Miroslav ?ulc wrote:
> > Well, "key" is not primary key from another table. It is just a column
> > in pair "key" => "value".
> > The structure of the table is this:
> >
> > Id (primary key)
> > MRTPContactId (id of contact from table MRTPContact)
> >
"David Clarke" <[EMAIL PROTECTED]> writes:
> is it really that big an issue these days to have a 100 character primary
> key? Are there postgres-specific implications for either approach?
It's exactly the same size issue as ever. A 20% increase in space usage is a
20% performance hit in certain
"A. Kretschmer" <[EMAIL PROTECTED]> writes:
> *untested*
> DELETE FROM partner_zu using partner
> WHERE partner_zu.pa_id = partner.id
> ...
> The point is the 'using ...'
You can also just set the add_missing_from to true for that one session if you
prefer. I don't think there's any plans to remo
Tom Lane <[EMAIL PROTECTED]> writes:
> A SQL-language function like this should get inlined into the query,
> so that you don't lose any performance compared to writing out the
> full expression each time.
I think what's going on here is that he doesn't really want a function in the
programming s
"Alfred" <[EMAIL PROTECTED]> writes:
> CO| N
> --+---
> 0 | 15-59
> 1 | 0, 16-59
> 2 | 0-1, 17-59
> 15| 0-14, 30-59
> 16| 0-15, 31-59
> 30| 0-29, 45-59
> 31| 0-30, 46-59
> 45| 0-44
> 46| 1-45
>
"Ding Xiangguang" <[EMAIL PROTECTED]> writes:
> Hi, friend,
>
> Suppose there is table of daily transaction data with 5 fields,
>
> time(date), open(float8), high(float8), low(float8), close(float8)
>
> Is it possible to create a view of weekly data, i.e. open is the first
> day'open, high is t
Jan Danielsson <[EMAIL PROTECTED]> writes:
> select from_ip, count(from_ip) as entries, count(select * from log where
> ...) as tot_entries, max(ts)::timestamp(0) as last_access from log where
> to_port=22 and direction='in' group by from_ip
select from_ip,
count(from_ip) as entries,
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > "Jesper K. Pedersen" <[EMAIL PROTECTED]> writes:
> >> Having checked the I/O format it seems that MS Access exports the
> >> values of a YESNO field as 0 and 1
"Jesper K. Pedersen" <[EMAIL PROTECTED]> writes:
> Having checked the I/O format it seems that MS Access exports the
> values of a YESNO field as 0 and 1
If only Postgres's boolean type were as helpful.
--
greg
---(end of broadcast)---
TIP 3: H
elein <[EMAIL PROTECTED]> writes:
> > Note that the above are not inverses because you changed the lefthand
> > input. You do get consistent results when you just add or omit NOT:
> Yes, you are right. I skipped the permutations to get down to the point.
Remember that NULL means "unknown". So "1
Rob <[EMAIL PROTECTED]> writes:
> I would like complete control over this information -- so if sometime in the
> future it's decided to totally redesign the layout. Also, at some point a
> tool will be created so novice computer users can enter nicely formatted
> markup -- meaning you won't have
"Anthony Molinaro" <[EMAIL PROTECTED]> writes:
> More awkward? What *you're* suggesting is more awkward. You realize that
> right? How can syntax that is understood and accepted for years be more
> awkward?
Well gosh, I would say that that's something only a newbie could say about
SQL of all thi
Scott Marlowe <[EMAIL PROTECTED]> writes:
> Sorry, but it's worse than that. It is quite possible that two people
> could run this query at the same time and get different data from the
> same set and the same point in time. That shouldn't happen accidentally
> in SQL, you should know it's comi
"Anthony Molinaro" <[EMAIL PROTECTED]> writes:
> Greg,
> You'll have to pardon me...
>
> I saw this comment:
>
> "I don't see why you think people stumble on this by accident.
> I think it's actually an extremely common need."
>
> Which, if referring to the ability to have items in the sele
"Anthony Molinaro" <[EMAIL PROTECTED]> writes:
> By changing the values in the select/group by you are changing
> Group! How can you arbitrarily add or exclude a column?
> You can't do it.
Go back and reread the previous posts again. You missed the whole point.
--
greg
--
Scott Marlowe <[EMAIL PROTECTED]> writes:
> Hehe. When I turn on my windshield wipers and my airbag deploys, is it
> a documented "feature" if the dealership told me about this behaviour
> ahead of time?
Well it's more like my car where the dashboard dims when I turn on my
headlights which ann
Collin Peters <[EMAIL PROTECTED]> writes:
> I have a table that has some columns which store 'custom' fields so the
> content varies according to the user that the row belongs to. For one
> of the groups of users the field is a date (the type of the field is
> 'text' though). I'm trying to perfo
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Tue, 11 Oct 2005, Rick Schumeyer wrote:
>
> > I'm not sure what I was thinking, but I tried the following query in pg:
> >
> > SELECT * FROM t GROUP BY state;
> >
> > pg returns an error.
> >
> > Mysql, OTOH, returns the first row for each state. (T
Tom Lane <[EMAIL PROTECTED]> writes:
> Richard Huxton writes:
> > Ah, now I see what you're saying. You're quite right in your suspicions,
> > "MOVE..." isn't supported for plpgsql cursors. You could probably do
> > something with EXECUTE and returning a refcursor from a previous
> > function,
Josh Berkus writes:
> Mark, Nathan,
>
> I'm moving this over to the PGSQL-SQL list, away from -hackers, as it's no
> longer a -hackers type discussion. Hope you don't mind!
>
> > On Wed, Sep 07, 2005 at 11:31:16AM -0700, Josh Berkus wrote:
> > > I'm also a little baffled to come up with any
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> In PostgreSQL, as everyone knows, a QUERY == a transaction, unless wrap'd in a
> BEGIN/END explicitly ... how does that work with a function? is there an
> implicit BEGIN/END around the whole transaction, or each QUERY within the
> function itself
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> SELECT * FROM customers WHERE monthly_balance[6] = 0.00;
This, like the other poster said, can be accomplished with a set of simple
expression indexes.
> As an example ... or
>
> SELECT * FROM customers WHERE 0.00 = any (monthly_balance);
This w
Markus Bertheau <[EMAIL PROTECTED]> writes:
> Offset for negative numbers means 0, as it seems. I think there is a
> sensible meaning for negative offset numbers and wondered, what
> arguments led to negative offsets being processed as 0 offset.
Frankly I'm surprised it's not a syntax error.
--
Nick Fankhauser <[EMAIL PROTECTED]> writes:
> Alvaro Herrera wrote:
>
> > The order is not really guaranteed, though if this is a one-shot thing,
> > you may get away with turning off hashed aggregates.
> >
>
> When I read this, I assumed there was a runtime parameter I could set that was
> sim
Tom Lane <[EMAIL PROTECTED]> writes:
> We have to start the transaction no later than event #2 since there has
> to be something to hold the lock. But it'd be easy enough to decouple
> this from BEGIN, and it'd be good enough to solve the "COMMIT;BEGIN"
> problem.
Oh I think I finally figured ou
Andrew Sullivan <[EMAIL PROTECTED]> writes:
> On Thu, Apr 14, 2005 at 07:21:38PM +0200, Andreas Joseph Krogh wrote:
> >
> > So, what you're suggesting is that a restart of the webapp should make
> > vacuum
> > able to delete those dead rows?
>
> Yes, but that'll only solve your problem for now
[EMAIL PROTECTED] writes:
> So basically I want to ignore a single character (the apostrophe
> character), anywhere in the middle of my search word, in selecting
> results. How can I do this?
WHERE replace(name,,'') like '%dont%'
Beware of quoting issues if "dont" is coming from user suppl
James G Wilkinson <[EMAIL PROTECTED]> writes:
> I hope that this is some silly beginner's mistake. I have spent quite a bit
> of
> time
> reading the PostgreSQL documentation and cannot find my error. I have also
> scanned the PostgreSQL archive and the web for help, but I have not found
> anyt
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> It does look like you can only ADD and DROP constraints, not directly
> alter or replace them. So making a reference deferable is go to require
> a DROP and ADD which will need to recheck the constraint.
I asked the same question a few days ago on pgs
Leon Stringer <[EMAIL PROTECTED]> writes:
> Hi,
>
> I wondered if anyone could answer the following question:
>
> If I have a table such as the one below:
>
> col1 col_order
> ---
> Apple 1
> Apple 2
> Orange 3
> Banana 4
> Apple 5
>
> Is there a way I can get the following result
Gary Stainburn <[EMAIL PROTECTED]> writes:
> > Alternatively: (a<>6),(a<>4),a
>
> Although this does exactly what I want, at first glance it should do
> exactly the opposite.
>
> I'm guessing that for each line it evaluates
> not (a=6) 0 for true else 1
Not really, "not a=6" is an expression t
"Joel Fradkin" <[EMAIL PROTECTED]> writes:
> I tried the SET ENABLE_SEQSCAN=FALSE;
> And the result took 29 secs instead of 117.
>
> After playing around with the cache and buffers etc I see I am no longer
> doing any swapping (not sure how I got the 100 sec response might have been
> shared buff
"Joel Fradkin" <[EMAIL PROTECTED]> writes:
> QUERY PLAN
> "Seq Scan on tblcase (cost=0.00..30066.21 rows=37401 width=996) (actual
> time=0.344..962.260 rows=22636 loops=1)"
> " Filter: ((clientnum)::text = 'SAKS'::text)"
> "Total runtime: 1034.434 ms"
Well that says it only took 1s. So it seems
"Joel Fradkin" <[EMAIL PROTECTED]> writes:
> I also tried a simple select * from tblcase where clientum = 'SAKS'
Try:
explain analyze select * from tblcase where clientum = 'SAKS'
Send the output.
--
greg
---(end of broadcast)---
TIP 4: Don't
Alex Turner <[EMAIL PROTECTED]> writes:
> I am also very interesting in this very question.. Is there any way to
> declare a persistant cursor that remains open between pg sessions?
> This would be better than a temp table because you would not have to
> do the initial select and insert into a f
"Andrei Bintintan" <[EMAIL PROTECTED]> writes:
> > If you're using this to provide "pages" of results, could you use a cursor?
> What do you mean by that? Cursor?
>
> Yes I'm using this to provide "pages", but If I jump to the last pages it goes
> very slow.
The best way to do pages for is not t
Andrew Sullivan <[EMAIL PROTECTED]> writes:
> You can set the sequence up to cycle (so once it gets to the end, it
> wraps around to the beginning again). The keyword is CYCLE at CREATE
> SEQUENCE time. It defaults to NO CYCLE.
>
> One potential problem, of course, are collisions on the table,
Ian Barwick <[EMAIL PROTECTED]> writes:
> What I still don't quite understand is why IN in a CHECK context is
> handled differently to say: select 1 where 'x' in (null,'a','b','c') ?
> This could be a bit of a gotcha for anyone constructing a constraint
> similar to the original poster's and not
[EMAIL PROTECTED] writes:
> But not possible for real at the moment?
>
> So, summarising:
> - Nested transactions is not (yet) supported
> - READ UNCOMMITTED isolation level is not (yet) supported
> - the EXECUTE plpgsql construct does not circumvent the transaction
Well nested transactions are
"Thomas F.O'Connell" <[EMAIL PROTECTED]> writes:
> select 2004-06-08;
> ?column?
> --
> 1990
>
> I'm not exactly sure how the bare string is converted internally, but it's
> clearly not a complete date like you're expecting.
What string? That's just integer arithmetic.
--
greg
Tomasz Myrta <[EMAIL PROTECTED]> writes:
> > select * from table1 LIMIT x
> > gives me the first x row of the result.
> > After that, I save the last value, and next time, I adjust
> > the query as
> > select * from table1 where itemkey>:lastvalue LIMIT x
>
> Why do you complicate it so much? Ev
patrick ~ <[EMAIL PROTECTED]> writes:
> I noticed that a freshly created db with freshly inserted data (from
> a previous pg_dump) would result in quite fast results. However,
> after running 'vacuum analyze' the very same query slowed down about
> 1250x (Time: 1080688.921 ms vs Time: 864.522 ms
"Muhyiddin A.M Hayat" <[EMAIL PROTECTED]> writes:
> Dear all,
>
> I need to do something similar to a cross tabulation, but without any
> aggregation.
join your table to itself four times:
select *
from (select check_time::date as date, employee_id, check_time-check_time::date as
in from te
"C. Bensend" <[EMAIL PROTECTED]> writes:
> The risk of a cron gone wild is acceptable to me at this moment.
Gee, now I have images of late-night advertisements for bofh-porn video tapes
of Cron Jobs Gone Wild(tm) dancing through my head... thanks.
--
greg
---(end of b
"C. Bensend" <[EMAIL PROTECTED]> writes:
> INSERT INTO table ( column1, column2, column3 )
>SELECT column1, column2, column3
>WHERE NOT EXISTS (
> SELECT column1, column2, column3 FROM table WHERE
> column1 = $column1 AND
> column2 = $column2 AND
> column3
Michelle Murrain <[EMAIL PROTECTED]> writes:
> The OUTER JOIN version is quite a bit more efficient (by an order of magnitude)
> than the option with WHERE NOT EXISTS subquery.
This is going to be heavily dependent on the version of postgres. IN/NOT IN
execution has improved a lot in 7.4 and lat
Tom Lane <[EMAIL PROTECTED]> writes:
> Karsten Hilbert <[EMAIL PROTECTED]> writes:
> > I am not convinced I'll need a SRF. I am not trying to
> > calculate something that isn't there yet. I am just trying to
> > join two views appropriately. I might have to employ some
> > variant of Celko's integ
Tom Lane <[EMAIL PROTECTED]> writes:
> Fixing this properly is a research project, and I haven't thought of any
> quick-and-dirty hacks that aren't too ugly to consider :-(
Just thinking out loud here. Instead of trying to peek inside the CASE
couldn't the optimizer just wrap the non-strict expr
T E Schmitz <[EMAIL PROTECTED]> writes:
> ) as somealias on (model_fk = model_pk)
>
> (subquery in FROM must have an alias)
ARGH! This is one of the most annoying things about postgres! It bites me all
the time. Obviously it's totally insignificant since it's easy for my to just
throw an "AS x"
T E Schmitz <[EMAIL PROTECTED]> writes:
> SELECT
> BRAND_NAME,MODEL_NAME
...
> intersect
...
Huh, I never think of the set operation solutions. I'm curious how it compares
speed-wise.
--
greg
---(end of broadcast)---
TIP 5: Have you checked our
T E Schmitz <[EMAIL PROTECTED]> writes:
> I want to select only those BRAND/MODEL combinations, where the MODEL has more
> than one TYPE, but only where one of those has TYPE_NAME='xyz'.
> I am not interested in MODELs with multiple TYPEs where none of them are called
> 'xyz'.
There are lots of
Tom Lane <[EMAIL PROTECTED]> writes:
> > The paragraph continues:
> > "If the SELECT command included the clause WHERE phone NOT NULL,
> > PostgreSQL could use the index to satisfy the ORDER BY clause.
> > An index that covers optional (NOT NULL) columns will not be used to
> > speed table join
T E Schmitz <[EMAIL PROTECTED]> writes:
> I just dug out the PostgreSQL book again because I thought I might've garbled
> it:
>
> Quote: "PostgreSQL will not index NULL values. Because an index will never
> include NULL values, it cannot be used to satisfy the ORDER BY clause of a
> query that r
stig erikson <[EMAIL PROTECTED]> writes:
> how can i specify an integer to be one byte byte or even 4 bits long?
> int1, int(1), tinyint are nowhere to be seen.
> smallest i can find is smallint that is 2 bytes.
There's a type called "char" (the double quotes are needed). It's used by
postgres s
"Iain" <[EMAIL PROTECTED]> writes:
> Though, as far as I can tell, there is no way to have the notify activate a
> pl/pgsql function directly. I'll still need to write a client program to
> create a session and actually do the listening, that is if I havn't missed
> anything else...
Right, presu
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > That's all well and good. But when I tried to make a version of your
> > situation that used a function I found it doesn't work so well with
> > functional indexes:
Greg Stark <[EMAIL PROTECTED]> writes:
> Theo Galanakis <[EMAIL PROTECTED]> writes:
>
> > I created the Index you specified, however it chooses to run a seq scan on
> > the column rather than a Index scan. How can you force it to use that
> > Index..
> >
Theo Galanakis <[EMAIL PROTECTED]> writes:
> I created the Index you specified, however it chooses to run a seq scan on
> the column rather than a Index scan. How can you force it to use that
> Index..
>
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$
Theo Galanakis <[EMAIL PROTECTED]> writes:
> error: btree item size 2744 exceeds maximum 2713.
>
> I assume I had to change some server settings to extend the maximum, however
I would guess the block size. But I'm just guessing.
> in the end this column holds content, and even applying a
Scott Gerhardt <[EMAIL PROTECTED]> writes:
> Hello, I am new to the list, my apology if this question is beyond the scope or
> charter of this list.
>
> My questions is:
> What is the best method to perform an aggregate query to calculate sum() values
> for each distinct wid as in the example be
"Riccardo G. Facchini" <[EMAIL PROTECTED]> writes:
> After searching throught the list, I assume you mean this link:
> http://www.rbt.ca/autodoc/index.html
> by Rod Taylor.
>
> Looks promising, but still what I need is a proper CVS output, as I
> need to review the changes made to the specific da
Dino Vliet <[EMAIL PROTECTED]> writes:
> x,0 and y,4 but how do I manage this in sql? I was
> hoping for a keyword LAST or so, where I can specify
> that when I've ordered my results with order by, I
> could only get the last of the subgroups (the first
> one is easy because I could use limit 1)
John DeSoi <[EMAIL PROTECTED]> writes:
> On Aug 10, 2004, at 10:57 AM, Bruce Momjian wrote:
>
> > I can't think of one, no. I think you will have to use one of the
> > server-side languages and call a sleep in there.
>
> This is no good in the real world since it pounds the CPU, but it worked
Joe Conway <[EMAIL PROTECTED]> writes:
> This is very true. In fact, I get mildly annoyed when people *don't* include
> the direct reply to me, because I very actively filter/redirect my mail.
> Replies directly to me are pretty much guaranteed to be seen quickly, but the
> ones that go to the li
Ray Aspeitia <[EMAIL PROTECTED]> writes:
> I also would like to pass the delimiter to the aggregate as a parameter and
> I am not sure if it can handle that.
Currently aggregates that take multiple parameters are just not supported.
--
greg
---(end of broadcast)--
Stephan Szabo <[EMAIL PROTECTED]> writes:
> IS TRUE and IS FALSE have a different effect from =true and =false when
> the left hand side is NULL. The former will return false, the latter will
> return NULL.
No, actually they both return false.
(But thanks, I didn't even realize they were specia
Rich Hall <[EMAIL PROTECTED]> writes:
> "(anything) = NULL" is always Null, this cannot be what the coder intended.
I often have such things in my SQL. Consider what happens when you have SQL
constructed dynamically. Or more frequently, consider that many drivers still
don't use the new binary pl
Markus Bertheau <[EMAIL PROTECTED]> writes:
> oocms=# SELECT ARRAY(SELECT 1 WHERE FALSE);
> ?column?
> --
>
This one seems strange to me. Shouldn't it result in an empty array?
--
greg
---(end of broadcast)---
TIP 9: the planner wi
> Joseph Turner <[EMAIL PROTECTED]> writes:
> > I have a table with a decent number of rows (let's say for example a
> > billion rows). I am trying to construct a graph that displays the
> > distribution of that data. However, I don't want to read in the
> > complete data set (as reading a billi
Marco Lazzeri <[EMAIL PROTECTED]> writes:
> Hi!
> I'm searching a better (quicker) way to retrieve data as I used to do
> using the following query...
>
> ==
>
> SELECT main.codice,
>other.value AS value_one,
>other.value AS value_two
> FROM main
> LEFT OUTER JOIN other
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> On Thu, May 13, 2004 at 18:13:23 +,
> Jaime Casanova <[EMAIL PROTECTED]> wrote:
> > Hi all, is there a way to set the isolation level to something like the
> > sql standard dirty read.
>
> No. There will be a way to use the standard name in a
Christoph Haller <[EMAIL PROTECTED]> writes:
> Interesting feature, but I cannot find function array_append:
> ERROR: AggregateCreate: function array_append(integer[], integer) does not exist
It's new in Postgres 7.4
I think you could do this in 7.3 though, it would just be more awkward. Try |
Marco Lazzeri <[EMAIL PROTECTED]> writes:
> SELECT
> p.name, ARRAY(SELECT nicknames FROM people WHERE people.id = p.id)
> FROM people AS p
> Any suggestions?
Something like:
db=> create aggregate array_aggregate (basetype = integer, sfunc = array_append, stype
= integer[], initcond = '{}');
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > Is there a reason postgres goes out of its way to pick names that
> > will be harder to work with than necessary?
>
> If we use ordinary identifiers for system-generated names the
Tom Lane <[EMAIL PROTECTED]> writes:
> Bret Hughes <[EMAIL PROTECTED]> writes:
> > FWIW I tried to use alter table but could never get the parser to accept
> > $1 as a constraint name. I used single and double quotes as well as a
> > lame attempt \$1.
>
> Hm, "$1" works for me ...
Hm, this rem
elein <[EMAIL PROTECTED]> writes:
> create or replace function pycounter(integer)
> returns integer as
> '
>if args[0] == 0:
> SD["nextno"] = 1
> return SD["nextno"]
>try:
> SD["nextno"] += 1
>except:
> SD["nextno"] = 1
>return SD["nextno"]
> ' language 'pl
Josh Berkus <[EMAIL PROTECTED]> writes:
> Rod,
>
> > Something along the lines of the below would accomplish what you want
> > according to spec. ROW_NUMBER() is a spec defined function. (6.10 of
> > SQL200N)
>
> Great leaping little gods! They added something called "row number" to the
> spe
Jeff Boes <[EMAIL PROTECTED]> writes:
> I headed off in the direction of groups of SELECTs and UNIONs, and quit when I
> got to something like four levels of "SELECT ... AS FOO" ...
four? wimp, that's nothing!
ok, seriously I think there's no way to do this directly with straight SQL.
You would
Josh Berkus <[EMAIL PROTECTED]> writes:
> Max() and Count() cannot use indexes for technical reasons. Browse through
> the archives of SQL, PERFORM, and HACKERS for about 12,000 discussions on the
> subject.
Please don't confuse the issue by throwing Max() and Count() into the same
basket.
Christoph Haller <[EMAIL PROTECTED]> writes:
> Why appear? If the Index Scan has a Total runtime: 2.46 msec and the Seq Scan
> a Total runtime: 46.19 msec, then the Index Scan is much faster.
> Or am I completely off the track reading the explain analyze output?
To estimate the relative costs
"Alexandra Birch" <[EMAIL PROTECTED]> writes:
> It works perfectly - thanks a million!
> Strangely the offset 0 does not seem to make any difference.
> Gotta read up more about subqueries :)
>
> explain analyze
> select code,order_date
>from (
> select code, order_date
>
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> >QUERY PLAN
> >
> > Sort (cost=11824.16..11831.5
Tom Lane <[EMAIL PROTECTED]> writes:
> Robert Creager <[EMAIL PROTECTED]> writes:
> > ... one piece of data I need is the last value for each GROUP BY
> > period. Alas, I cannot figure out how to do this.
>
> SELECT DISTINCT ON (rather than GROUP BY) could get this done for you.
Or if you need
"Grace C. Unson" <[EMAIL PROTECTED]> writes:
> Why is it that my index for text[] data type is not recognized by the
> Planner?
>
> I did these steps:
>
> 1. create function textarr(text[]) returns text language sql as 'select
> $1[1]' strict immutable
> 2. create index org_idx on EmpData (text
[EMAIL PROTECTED] (Dmitri Bichko) writes:
> I am running in trouble with pagination here, somehow (rather naively) I
> assumed that when doing a LIMIT and OFFSET, the subselects on the records
> before the OFFSET would not be performed, which quite apparently is not the
> case. So, LIMIT 50 OFFSE
Joe Conway <[EMAIL PROTECTED]> writes:
> In 7.4 you could use an array. It would look like this:
Though note that 7.4 doesn't know how to optimize this form:
db=> explain select * from foo where foo_id in (1,2);
QUERY PLAN
Tom Lane <[EMAIL PROTECTED]> writes:
> No, because the above represents a moving cutoff; it will (and should)
> be rejected as a non-immutable predicate condition. You could do
> something like
>
> CREATE INDEX my_Nov_03_index on my_table (create_date)
> WHERE (cre
"Randolf Richardson, DevNet SysOp 29" <[EMAIL PROTECTED]> writes:
> For example, if I want to index on a date field but only have the index
> keep track of the most recent 30 days (and then create a secondary index for
> all dates) so as to improve performance on more heavily loaded syste
ow <[EMAIL PROTECTED]> writes:
> My concern though ... wouldn't pgSql server collapse when faced with
> transaction spawning across 100M+ records?
The number of records involved really doesn't faze Postgres at all. However
the amount of time spent in the transaction could be an issue if there is
1 - 100 of 116 matches
Mail list logo
