Re: [SQL] lost password
Well, the username and password are the same, but the md5 is different. But it doesn't seem to matter because either one works the same. The fact that you can have 2 different md5's yet still have the same user name and password and have logins work is what I found to be interesting. But my original problem is solved in either case, so it doesn't really matter to me. I just found it to be ... interesting. Edward W. Rouse -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Tom Lane Sent: Tuesday, November 17, 2009 6:57 PM To: Edward W. Rouse Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] lost password "Edward W. Rouse" writes: > After I copied the md5 over I played around a bit and found the for the > process user. I then used the alter user sql statement and rechecked the > md5. It was different, but I could still use the same password to log in. > Needless to say, I am a bit confused by that, but it works and that's what I > need. The md5 will depend on both the actual password and the user's name ... does that help? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] need nelp with aggregate functions
The DB structure is in attachment. I with the number of clients and the number of computers that have processors with "manufacturer" = "INTEL" and "speed" = "2GB" I am trying: select count(c) as qtd_client, count(cm) as qtd_computers from cliente c inner JOIN computer cm on (c.cliente_id = cm.cliente_id) inner join processor p on (cm.processor_id = p.processor_id) inner join speed s on (s.speed_id = p.speed_id) INNER JOIN manufacturer m on (p.manufacturer_id=m.manufacturer_id) where m.manufacturer = 'INTEL' and s.speed = '2GB' but is not working anyone can help me? <> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need nelp with aggregate functions
On Wed, Nov 18, 2009 at 9:55 AM, Another Trad wrote: > The DB structure is in attachment. > I with the number of clients and the number of computers that have > processors with "manufacturer" = "INTEL" and "speed" = "2GB" > I am trying: > > select count(c) as qtd_client, count(cm) as qtd_computers > > from cliente c > inner JOIN computer cm on (c.cliente_id = cm.cliente_id) > inner join processor p on (cm.processor_id = p.processor_id) > inner join speed s on (s.speed_id = p.speed_id) > INNER JOIN manufacturer m on (p.manufacturer_id=m.manufacturer_id) > > where m.manufacturer = 'INTEL' > and s.speed = '2GB' > > but is not working > anyone can help me? If you are getting an error message it's a good idea to post it as well. However, first problem is that c and cm are tables, not fields. You need to count some field from those tables. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need nelp with aggregate functions
Try substituting the SELECT count(c) as qtd_client,count(cm) as qtd_computers by SELECT count( DISTINCT c.cliente_id) as qtd_client,count(/* put here the primary key of the computer table */ ) as qtd_computers Then tell me if it output what you want Best, Oliveiros - Original Message - From: Another Trad To: pgsql-sql@postgresql.org Sent: Wednesday, November 18, 2009 4:55 PM Subject: [SQL] need nelp with aggregate functions The DB structure is in attachment. I with the number of clients and the number of computers that have processors with "manufacturer" = "INTEL" and "speed" = "2GB" I am trying: select count(c) as qtd_client, count(cm) as qtd_computers from cliente c inner JOIN computer cm on (c.cliente_id = cm.cliente_id) inner join processor p on (cm.processor_id = p.processor_id)inner join speed s on (s.speed_id = p.speed_id)INNER JOIN manufacturer m on (p.manufacturer_id=m.manufacturer_id) where m.manufacturer = 'INTEL'and s.speed = '2GB'but is not working anyone can help me? -- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need nelp with aggregate functions
ok, I did: SELECT count(DISTINCT c.cliente_id) as qtd_client,count(cm.cm_id) as qtd_computers GREAT. It works. Please, explain me why and how it works, I wanna learn and do by myself next time :) 2009/11/18 Oliveiros C, > Try substituting the SELECT count(c) as qtd_client,count(cm) as > qtd_computers > > by > > SELECT count( DISTINCT c.cliente_id) as qtd_client,count(/* put here the > primary key of the computer table */ ) as qtd_computers > > Then tell me if it output what you want > > Best, > Oliveiros > > > - Original Message - > *From:* Another Trad > *To:* pgsql-sql@postgresql.org > *Sent:* Wednesday, November 18, 2009 4:55 PM > *Subject:* [SQL] need nelp with aggregate functions > > The DB structure is in attachment. > I with the number of clients and the number of computers that have > processors with "manufacturer" = "INTEL" and "speed" = "2GB" > I am trying: > > select count(c) as qtd_client, count(cm) as qtd_computers > > from cliente c > inner JOIN computer cm on (c.cliente_id = cm.cliente_id) > inner join processor p on (cm.processor_id = p.processor_id) > inner join speed s on (s.speed_id = p.speed_id) > INNER JOIN manufacturer m on (p.manufacturer_id=m.manufacturer_id) > > where m.manufacturer = 'INTEL' > and s.speed = '2GB' > > but is not working > anyone can help me? > > -- > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > >
[SQL] LIMIT BASED ON PERCENT
My question is quite simple: I want to select all the records from my table, but I want apply a LIMIT of 20% in the lines. like: select * from client limit 20% I have tried (of course, with no success) this: select * from client limit ((select count(*) from client)*20/100)
Re: [SQL] LIMIT BASED ON PERCENT
Your SQL works for me exactly as it is (substituting a table in my database). What error are you getting? On Wed, Nov 18, 2009 at 2:12 PM, Another Trad wrote: > My question is quite simple: I want to select all the records from my table, > but I want apply a LIMIT of 20% in the lines. like: > select * from client limit 20% > I have tried (of course, with no success) this: > select * from client limit ((select count(*) from client)*20/100) > -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT BASED ON PERCENT
No, It doesn't. In my machine: First select ERROR: syntax error at end of input LINE 1: select * from rapadura.cliente limit 20% ^ Second one: ERROR: argument of LIMIT must not contain subqueries Postgres 8.3 2009/11/18 Lee Hachadoorian > Your SQL works for me exactly as it is (substituting a table in my > database). What error are you getting? > > On Wed, Nov 18, 2009 at 2:12 PM, Another Trad > wrote: > > My question is quite simple: I want to select all the records from my > table, > > but I want apply a LIMIT of 20% in the lines. like: > > select * from client limit 20% > > I have tried (of course, with no success) this: > > select * from client limit ((select count(*) from client)*20/100) > > > > > > -- > Lee Hachadoorian > PhD Student, Geography > Program in Earth & Environmental Sciences > CUNY Graduate Center >
Re: [SQL] LIMIT BASED ON PERCENT
Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : > No, It doesn't. > In my machine: > > First select > ERROR: syntax error at end of input > LINE 1: select * from rapadura.cliente limit 20% > ^ > Second one: > ERROR: argument of LIMIT must not contain subqueries > > Postgres 8.3 > It works for Lee because obviously he's working on a 8.4 server. You can use subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For earlier releases, there's no way to do this in a single query. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT BASED ON PERCENT
But there is any way to do it? 2009/11/18 Guillaume Lelarge > Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : > > No, It doesn't. > > In my machine: > > > > First select > > ERROR: syntax error at end of input > > LINE 1: select * from rapadura.cliente limit 20% > > ^ > > Second one: > > ERROR: argument of LIMIT must not contain subqueries > > > > Postgres 8.3 > > > > It works for Lee because obviously he's working on a 8.4 server. You can > use > subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For > earlier > releases, there's no way to do this in a single query. > > > -- > Guillaume. > http://www.postgresqlfr.org > http://dalibo.com >
Re: [SQL] LIMIT BASED ON PERCENT
2009/11/18 Guillaume Lelarge : > Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : >> No, It doesn't. >> In my machine: >> >> First select >> ERROR: syntax error at end of input >> LINE 1: select * from rapadura.cliente limit 20% >> ^ >> Second one: >> ERROR: argument of LIMIT must not contain subqueries >> >> Postgres 8.3 >> > > It works for Lee because obviously he's working on a 8.4 server. You can use > subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For earlier > releases, there's no way to do this in a single query. > yes, and don't use 20%. select * from foo order by somecol limit (select (count(*)*0.2)::int from foo) Regards Pavel > > -- > Guillaume. > http://www.postgresqlfr.org > http://dalibo.com > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT BASED ON PERCENT
On Wed, Nov 18, 2009 at 2:30 PM, Pavel Stehule wrote: > yes, and don't use 20%. > > select * from foo order by somecol limit (select (count(*)*0.2)::int from foo) > > Regards > Pavel Is this faster on a large table? Because (select (count(*)*20/100)) worked fine. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT BASED ON PERCENT
2009/11/18 Another Trad : > But there is any way to do it? CREATE OR REPLACE twenty() RETURNS SETOF foo AS $$ DECLARE rows int; r record; BEGIN rows := (SELECT count(*) FROM foo); FOR r IN EXECUTE 'SELECT * FROM r ORDER BY some col LIMIT ' || (rows * 0.2)::int LOOP RETURN NEXT r; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; this code should to work in 8.3 regards Pavel Stehule > > 2009/11/18 Guillaume Lelarge >> >> Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : >> > No, It doesn't. >> > In my machine: >> > >> > First select >> > ERROR: syntax error at end of input >> > LINE 1: select * from rapadura.cliente limit 20% >> > ^ >> > Second one: >> > ERROR: argument of LIMIT must not contain subqueries >> > >> > Postgres 8.3 >> > >> >> It works for Lee because obviously he's working on a 8.4 server. You can >> use >> subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For >> earlier >> releases, there's no way to do this in a single query. >> >> >> -- >> Guillaume. >> http://www.postgresqlfr.org >> http://dalibo.com > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT BASED ON PERCENT
2009/11/18 Lee Hachadoorian : > On Wed, Nov 18, 2009 at 2:30 PM, Pavel Stehule > wrote: >> yes, and don't use 20%. >> >> select * from foo order by somecol limit (select (count(*)*0.2)::int from >> foo) >> >> Regards >> Pavel > > Is this faster on a large table? Because (select (count(*)*20/100)) worked > fine. > this is +/- same - 20/100 is maybe about 0.01% faster - you don't need one float to query cast, but this operation is only one pqr query. The problem is two full scan of table. Pavel -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT BASED ON PERCENT
Could you not create a function to do this instead? Set var_limit = 20% of row count Replace subquery with var_limit Kris On 18-Nov-09, at 14:27, Guillaume Lelarge wrote: Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : No, It doesn't. In my machine: First select ERROR: syntax error at end of input LINE 1: select * from rapadura.cliente limit 20% ^ Second one: ERROR: argument of LIMIT must not contain subqueries Postgres 8.3 It works for Lee because obviously he's working on a 8.4 server. You can use subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For earlier releases, there's no way to do this in a single query. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT BASED ON PERCENT
Le jeudi 19 novembre 2009 à 01:20:24, Kris Kewley a écrit : > Could you not create a function to do this instead? > > Set var_limit = 20% of row count > Replace subquery with var_limit > Sure, see the previous mails from Pavel. You can also put the percent as a parameter of the function. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
