Re: [SQL] Double query (limit and offset)
Am Dienstag, 11. Oktober 2005 17:11 schrieb Michael Landin Hostbaek: > List, > > I'm using the OFFSET / LIMIT combo in order to split up my query, so it > only parses 20 rows at a time (for my php-scripted webpage). > > I'm using two queries; the first basically doing a select count(*) from > [bla bla]; the second grabbing the actual data while setting LIMIT and > OFFSET. > > In addition, I'm using the first query plus some calculations to parse > total hits to the query, and number of pages etc etc. > > Now, my problem is this, the first query is simply counting all rows > from the main table, whereas the second query has plenty of JOINS, and a > GROUB BY statement - it's a fairly heavy query. The total (reported by > the first query), it not at all the same as the amount of rows returned > by the second query. I'd like to avoid having to run the "heavy" query > twice, just in order to get the number of rows. > > Is there a smarter way of doing it ? There is a smarter way of asking: Show us the queries! But it also depends on what you expect the user to do. Some hints: In generell if you count table A and afterwards you join and group your tables A,B,C,D the number of rows in the resultset may vary, of course. - You could fetch ALL rows with the second query, count them (pg_numrows), show the first ten results and keep all other results in cache for the next webpage. (if we are talking about a smal set of rows not if we are talking about 1 billion rows, of course) - You can rewrite your first query to return the correct number and see if it has a real performance impact. Optimize our query and you will be fine. Postgresql is very fast. - You can show the user an estimated count, if the correct number isn't of any interest (like google) - If you ever look at the CURSOR thing in postgresql and it looks attractive to you ( http://www.postgresql.org/docs/8.0/static/sql-fetch.html ): I think it isn't useful in a normal web environment, but it could be nice together with AJAX scripting. kind regards, janning ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Double query (limit and offset)
Michael Landin Hostbaek wrote: List, I'm using the OFFSET / LIMIT combo in order to split up my query, so it only parses 20 rows at a time (for my php-scripted webpage). The best way to do it is to have a layer between your application and the database that can cache the results of your query. Unfortunately PHP is not ideal for this - you might want to google for "php memcache" and "pgmemcache" though. Once you can cache the query, you run it once, use the row-count and then fetch the rows from cache. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Query information needed
On Thu, 2005-10-13 at 05:50 -0700, [EMAIL PROTECTED] wrote:
> Dear all,
>
> I have a table created with this specifications:
>
> CREATE TABLE cdr (
> calldate timestamp with time zone NOT NULL default now(),
> clid varchar(80) NOT NULL default '',
> src varchar(80) NOT NULL default '',
> dst varchar(80) NOT NULL default '',
> dcontext varchar(80) NOT NULL default '',
> channel varchar(80) NOT NULL default '',
> dstchannel varchar(80) NOT NULL default '',
> lastapp varchar(80) NOT NULL default '',
> lastdata varchar(80) NOT NULL default '',
> duration bigint NOT NULL default '0',
> billsec bigint NOT NULL default '0',
> disposition varchar(45) NOT NULL default '',
> amaflags bigint NOT NULL default '0',
> accountcode varchar(20) NOT NULL default '',
> uniqueid varchar(32) NOT NULL default '',
> userfield varchar(255) NOT NULL default ''
> );
>
> I want to extract the number of calls placed in 1 hour and the average
> call duration
>
> I'm working with this query:
>
> SELECT date_trunc('hour',calldate), duration FROM cdr WHERE src=601
> ORDER BY calldate;
>
> i tried several other queries but i'm not able to count the number of
> calls in an hour (better in a time interval) and calculate the average
> duration.
For any particular interval:
SELECT COUNT(*), AVG(duration)
FROM cdr
WHERE src='601' AND
calldate BETWEEN CAST ('2005-10-17 10:00:00' AS TIMESTAMP WITH
TIMEZONE) AND
CAST ('2005-10-17 10:59:59' AS TIMESTAMP WITH
TIMEZONE);
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
Do you want to know God? http://www.lfix.co.uk/knowing_god.html
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Problem while using start transaction ans commit;
On Mon, 2005-10-17 at 12:53 +0530, Sri wrote: > Hi All, > > I have a small problem in using nested transactions while working on > Postgres 8.0. > > Ex: I have a function A() which in turn calls functions b() and c() , > if i want commit something in b or c. i have to use You cannot start or commit a transaction inside a function. You can use savepoints. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Field Separator not working?
I'm having troubles setting the field separator. I tried this on pg7.4, 8.0 and 8.1beta, and I always get the same results. So, from within psql i do: pulitzer2=# select * from pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig ---+--+-+--+---+--+--+--- mario |1 | t | t| t | | | postgres | 100 | t | t| t | | | pulitzer2 | 102 | f | f| f | | infinity | 101 | 101 | f | f| f | | infinity | (4 rows) pulitzer2=# \t Showing only tuples. pulitzer2=# \f# Field separator is "#". pulitzer2=# select * from pg_user; mario |1 | t | t| t | | | postgres | 100 | t | t| t | | | pulitzer2 | 102 | f | f| f | | infinity | 101 | 101 | f | f| f | | infinity | pulitzer2=# psql does tell me that I changed the field separator to "#", but it still uses "|" as separator. I'm parsing the script from bash, using psql, and setting the field separator options on the command line, but I get similair results, the field separator is always "|", so I need to awk-it with -F, but sometimes I have problems because data in my tables often contain "|" charachter. I've been looking trough the psql source code, but just like that, it's too big hassle for me now :) Am I doing something wrong? Mike -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SEVEN cross joins?!?!?
At 09:04 AM 10/13/05, Daryl Richter wrote: Frank Bax wrote: [snip] Richard, you've summed it up nicely. Splitting locations into subsets (like 2,2,3) doesn't work because it is possible that low values in one location can be offset by high values in another location, and still result in an excellent combo. The good news is these suggestions got me thinking outside the box. I think I can program a modified brute-force that bypasses large numbers of combos early. It might still be too large/slow, so I'd be interested in finding more info about these "smarter algorithms" in option 2. Where do I look? If you're mathematically inclined, I would first look at using Lagrangian Relexation, it may be appropriate for your problem: http://www.2112fx.com/lagrange.html Thanks, but that's a little too complex for me to turn into code! I did rewrite my code from a simple cross join SQL in PHP to custom searching in perl. I sucked subselects into arrays and then looked at all possible combinations. For those that forgot/missed the background, my table has 514 rows. Using subselect, this table is split into 7 subtables. These seven subtables are cross joined with each other to produce 770 billion rows that need to be searched (to assemble a 'made-to-order' suit of armour). By using more intelligent code (and not simple brute-force), I was able to analyse a complete set of 770 billion states in just under 70 hours on a P4-2.8Ghz system, which is fast enough for today. A faster cpu will help, since process does no i/o except at beginning and end of script. I realised that if I am ever able to figure out coding for multi-processor or systems (even remote like [EMAIL PROTECTED]), I can exploit either/both of these for this problem by slitting problem on items in first subtable into 50-60 subtasks, then merging results from each of those subtasks. This might become a requirement if the underlying table grows to be quite large. Thanks for pointing me in the right direction, it's been an interesting week. Frank ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Field Separator not working?
Mario Splivalo wrote: pulitzer2=# \t Showing only tuples. pulitzer2=# \f# Field separator is "#". pulitzer2=# select * from pg_user; mario |1 | t | t| t | | psql does tell me that I changed the field separator to "#", but it still uses "|" as separator. I'm parsing the script from bash, using psql, and setting the field separator options on the command line, but I get similair results, the field separator is always "|", so I need to awk-it with -F, but sometimes I have problems because data in my tables often contain "|" charachter. You'll want this too: \pset format unaligned man psql or see the manual for full details -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Field Separator not working?
On Wed, 2005-10-19 at 11:43 +0100, Richard Huxton wrote: > Mario Splivalo wrote: > > pulitzer2=# \t > > Showing only tuples. > > pulitzer2=# \f# > > Field separator is "#". > > pulitzer2=# select * from pg_user; > > mario |1 | t | t| t | | > > > psql does tell me that I changed the field separator to "#", but it > > still uses "|" as separator. I'm parsing the script from bash, using > > psql, and setting the field separator options on the command line, but I > > get similair results, the field separator is always "|", so I need to > > awk-it with -F, but sometimes I have problems because data in my tables > > often contain "|" charachter. > > You'll want this too: >\pset format unaligned > > man psql or see the manual for full details Hopla. It works perfetcly, thnx. Sometimes we're lazy to trough-read the manuals :) Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Sql - Error en ejecución de función Post gres desde cliente VFoxPro
Hola a todos!! Muchas gracias a Mario Splivalo por responder mi mail anterior!!! Tengo otro problema. Yo ejecuto una función en Postgresql desde una aplicación realizada en VFoxPro. La función crea una tabla temporal con la sintaxis CREATE TEMPORAY TABLE tmp_datos (...) ON COMMIT DROP; Si ejecuto esta función varias veces desde el pgAdmin, por ej, no tiene problemas. Pero si intento ejecutarla más de una vez desde VFoxPro a través de sql = "select * from f_rep_datos_lab( 'USUARIO') aaa=sqlexec(db_cone,sql, "repo") da el error "Relation tmp_datos already exists" Alguien me puede decir porqué o cómo se soluciona? Desde ya, muchas gracias!! Atte. Adriana 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo Abrí tu cuenta aquí
[SQL] Character set error
Good Day. I´m working with postgres 7.4. on Win XP and when I do a query like select* from table it returns me this error message: Invalid character data was found. This is most likely caused by stored data containing characters that are invalid for the character set the database was created in. The most common example of this is storing 8bit data in a SQL_ASCII database. I know it´s a character set problem, I really have SQL_ASCII encoding but I don´t know how to fix it Any idea will be welcome Thanks Mauricio Fernández A. Ingeniero de Sistemas U. Autónoma de Manizales ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problem -Postgre sql
COALESCE( , ) Vikas J <[EMAIL PROTECTED]> wrote: Hi I want to know substitute function for sql server ISNULL() function in postgre Regards,Vikas Jadhav Codec Communication Pvt. Ltd.Swargate, Pune.Ph: 020-2422 1460/70 (Ext 37)Email: [EMAIL PROTECTED] Yahoo! India Matrimony: Find your partner online.
[SQL] NULL in IN clause
Hi, I have just run this command on 8.0.4 : SELECT 'foo' WHERE 0 NOT IN (NULL, 1); And it resulted is zero rows. Without NULL it is OK. Is this a bug, or the standard has such a rule? Best Regards, Otto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] NULL in IN clause
Havasvölgyi Ottó wrote: Hi, I have just run this command on 8.0.4 : SELECT 'foo' WHERE 0 NOT IN (NULL, 1); 0 <> NULL (Indeed nothing equals NULL, other then sometimes NULL itself) 0 <> 1 Therefore, the statement: 0 NOT IN (NULL, 1) Should always equate to false. Therefore No rows returned. Ever. Terry And it resulted is zero rows. Without NULL it is OK. Is this a bug, or the standard has such a rule? Best Regards, Otto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Terry Fielder [EMAIL PROTECTED] Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] NULL in IN clause
As i understand it, the use of NULL in SQL means the value of the column is unknown. Therefore that result would seem fair. Havasvölgyi Ottó wrote: Hi, I have just run this command on 8.0.4 : SELECT 'foo' WHERE 0 NOT IN (NULL, 1); And it resulted is zero rows. Without NULL it is OK. Is this a bug, or the standard has such a rule? Best Regards, Otto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] NULL in IN clause
=?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <[EMAIL PROTECTED]> writes: > I have just run this command on 8.0.4 : > SELECT 'foo' WHERE 0 NOT IN (NULL, 1); > And it resulted is zero rows. > Without NULL it is OK. > Is this a bug, or the standard has such a rule? This is per spec. The computation is effectively NOT (0 = NULL OR 0 = 1) NOT (NULL OR FALSE) NOT NULL NULL ie, the result is UNKNOWN, which WHERE treats the same as FALSE. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] NULL in IN clause
On Wed, 19 Oct 2005, [iso-8859-2] Havasv?lgyi Ott? wrote: > Hi, > > I have just run this command on 8.0.4 : > > SELECT 'foo' WHERE 0 NOT IN (NULL, 1); > > And it resulted is zero rows. > Without NULL it is OK. > Is this a bug, or the standard has such a rule? This is standard behavior. Seeing if I can do this from memory... a NOT IN b is equivalent in the spec to NOT(a IN b). a IN b is equivalent to a =ANY b. a =ANY b returns true if a = x is true for any x in b. a =ANY b returns false if a = x is false for all x in b. Otherwise it returns unknown. 0 = NULL returns unknown 0 = 1 returns false So, 0 IN (NULL,1) returns unknown. NOT(unknown) is unknown. WHERE clauses only return rows for which the search condition is true, so a row is not returned. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] casting character varying to integer - order by numeric sort
How can I force a character field to sort as a numeric field? I've got something like this: Postgres=> SELECT username,last_name FROM eg_member ORDER BY username; --+--- 0120 | Foley 1| Sullivan 10 | Guest 11 | User (5 rows) (I can't change the field type). I tried: SELECT username,last_name FROM eg_member ORDER BY username::integer; But postgres 7 rejects this with "ERROR: cannot cast type character varying to integer". Is there a way to force numeric sort order? I tried a variety of functions, such as to_char() and convert() without any luck. Thanks for your insight! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] casting character varying to integer - order by numeric
Check out the function to_number()
In particular here's an example...
If a field named section is text containing numbers:
ORDER BY to_number(t.section, text())
If the field can also contain non-numerals such as 3a, 3b, and you want
3a to show first then do this:
ORDER BY to_number(t.section, text()), t.section
And if the field section can actually START with an alpha, then to
prevent to_number from failing do this:
to_number(textcat('0', t.section), text()), t.section
Terry
Bryce W Nesbitt wrote:
How can I force a character field to sort as a numeric field?
I've got something like this:
Postgres=> SELECT username,last_name
FROM eg_member ORDER BY username;
--+---
0120 | Foley
1| Sullivan
10 | Guest
11 | User
(5 rows)
(I can't change the field type). I tried:
SELECT username,last_name
FROM eg_member ORDER BY username::integer;
But postgres 7 rejects this with "ERROR: cannot cast type character
varying to integer". Is there a way to force numeric sort order? I
tried a variety of functions, such as to_char() and convert() without
any luck. Thanks for your insight!
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
Terry Fielder
[EMAIL PROTECTED]
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] casting character varying to integer - order by numeric sort
Bryce W Nesbitt <[EMAIL PROTECTED]> writes: > SELECT username,last_name > FROM eg_member ORDER BY username::integer; > But postgres 7 rejects this with "ERROR: cannot cast type character > varying to integer". As a general rule, you need to be more specific than that about which version you are working with ;-) You may find that username::text::integer will work, depending on which 7.x this actually is. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
