[SQL] Indices and time spans
I have this: SELECT ... FROM trade, entry WHERE AGE( CURRENT_TIMESTAMP, posted ) <= '5 minutes'::TIMESPAN AND trade.entryId = entry.entryId That is, fetch all trades executed in the last 5 minutes. This query seems to run pretty slowly when trade is filled. Putting an index on trade ( posted ) doesn't seem to help any (the same query plan is generated). Any suggestions? -itai
Re: [SQL] Indices and time spans
Itai Zukerman <[EMAIL PROTECTED]> writes: > I have this: > SELECT ... > FROM trade, entry > WHERE AGE( CURRENT_TIMESTAMP, posted ) <= '5 minutes'::TIMESPAN > AND trade.entryId = entry.entryId > That is, fetch all trades executed in the last 5 minutes. > This query seems to run pretty slowly when trade is filled. Putting > an index on trade ( posted ) doesn't seem to help any No, it wouldn't, since the indexscan machinery can only deal with WHERE clauses that look like "indexed_column relational_op constant". You need to recast the clause as something like WHERE posted >= (CURRENT_TIMESTAMP - '5 minutes'::TIMESPAN) Then you still have the problem of persuading Postgres that it should treat the right side of this as a constant and not something to re-evaluate at each row. In 7.0 and later it's possible to do that with creative use of a user-defined function marked "iscachable" (for details see the archives for the last time this question came up, a few months back). But the lowest-tech solution may be to calculate the cutoff time on the application side, so you can just send it as a constant to begin with. regards, tom lane
[SQL] result in a variable?
In a program writes in C. I want to store the result, of a count(*), in a variable. can I do, and How ? Thanks.
Re: [SQL] Indices and time spans
> You need to recast the clause as something like > > WHERE posted >= (CURRENT_TIMESTAMP - '5 minutes'::TIMESPAN) > > Then you still have the problem of persuading Postgres that it should > treat the right side of this as a constant and not something to > re-evaluate at each row. In 7.0 and later it's possible to do that > with creative use of a user-defined function marked "iscachable" > (for details see the archives for the last time this question came up, > a few months back). That did the trick, thanks! Just for reference: CREATE FUNCTION time_machine ( TIMESPAN ) RETURNS TIMESTAMP AS 'SELECT CURRENT_TIMESTAMP - $1' LANGUAGE 'sql' WITH (iscachable); seems to work. Perhaps it's more complicated than that, though? -itai
[SQL] Simple concatenation in select query
Hello, Sorry for the stupid posting, but.. There was a string concatenation function in MySQL: SELECT CONCAT(first_name, " ", last_name) FROM table; Is there a similar function in Postgres? Certainly, it's possible to live without it, but i'd like to write as above, doing concatenation in place. Ok, it seems i found it now: SELECT TEXTCAT(first_name, last_name) FROM table; but it allows only 2 arguments, inserting " " or ' ' (space) causes an error: attribute ' ' not found! Why postgres doesnt see it as string? Ok, i got it, after all! It took > 30 min to write this query. :( SELECT textcat(textcat(first_name,text ' '),last_name) from table; It kind of strange, but i found this function not in "user manual", but doing "\df text".. There is some differences between function descriptions in manual and those that \df returns.. Virtually \df returns much more functions than in manual, and there is some differences in argument types. May be someone knows a better, complete manual with ALL function described and code samples? [EMAIL PROTECTED] www.mediaparks.lv
RE: [SQL] Simple concatenation in select query
PostgreSQL has a string concatenation operator (see operators in the manual): SELECT last_name||', '||first_name FROM ... Here's a ref: http://www.postgresql.org/docs/user/x2129.htm Henry -Original Message- From: Sandis [mailto:[EMAIL PROTECTED]] Sent: Monday, July 31, 2000 2:48 PM To: [EMAIL PROTECTED] Subject: [SQL] Simple concatenation in select query Hello, Sorry for the stupid posting, but.. There was a string concatenation function in MySQL: SELECT CONCAT(first_name, " ", last_name) FROM table; Is there a similar function in Postgres? Certainly, it's possible to live without it, but i'd like to write as above, doing concatenation in place. Ok, it seems i found it now: SELECT TEXTCAT(first_name, last_name) FROM table; but it allows only 2 arguments, inserting " " or ' ' (space) causes an error: attribute ' ' not found! Why postgres doesnt see it as string? Ok, i got it, after all! It took > 30 min to write this query. :( SELECT textcat(textcat(first_name,text ' '),last_name) from table; It kind of strange, but i found this function not in "user manual", but doing "\df text".. There is some differences between function descriptions in manual and those that \df returns.. Virtually \df returns much more functions than in manual, and there is some differences in argument types. May be someone knows a better, complete manual with ALL function described and code samples? [EMAIL PROTECTED] www.mediaparks.lv
Re: [SQL] Simple concatenation in select query
"Sandis" <[EMAIL PROTECTED]> writes: > Ok, i got it, after all! It took > 30 min to write this query. :( > SELECT textcat(textcat(first_name,text ' '),last_name) from table; It's a lot easier if you use the SQL-standard concatenation operator: regression=# select 'foo' || 'bar'; ?column? -- foobar (1 row) textcat() just exists to implement the operator, which is why it's not documented separately. Dunno why you'd need to specify the type of the constant explicitly in this context --- there is only one textcat function, so the system ought to be able to figure it out. regards, tom lane
[SQL] What's ETA for read/write Views?
I'm working on a project and using postgres 7.0.2 with phpPgAdmin and php3.0.16. Anyhow, the more I learn about relations database systems, the more orthogonal my data becomes. However, I then need to have read/write views so that "ordinary" humans can make sense of the data While, I'm at it. I've always disliked MySQL because it wasn't GPL'd nor was it a real RDBMS. Anyhow, from what I can tell, they went GPL because that was the only way to move forward due to their BDB kludge.Just my 2 cents TIA. tim [EMAIL PROTECTED]
Re: [SQL] What's ETA for read/write Views?
Read-write views exist now: you just have to write ON INSERT, ON UPDATE, ON DELETE rules that show what you think should happen. Some people seem to think that the system should try to intuit those rules for them, but I don't believe that's either possible or desirable. The entire point of a view is that it's not an exact image of the underlying data, so how is a machine going to figure out what you want an update on the view to do? regards, tom lane
