Re: [GENERAL] Substitutes for some Oracle packages
Sandra Arnold wrote: > We are in the process of migrating from Oracle to PostgreSQL. > One of the things that we are needing to find out is what to > use in place of Oracle supplied functionality such as > "DBMS_OUTPUT" and "UTL_FILE". We are currently using this > type of functionality in Stored Procedures and packages. > What are the options in PostgreSQL for replacing these two > packages in a stored procedure/function? RAISE NOTICE has been mentioned. Apart from the possibility to write file I/O functions in Perl, there are some functions that are already included in PostgreSQL: pg_read_file and pg_stat_file. The contrib module "adminpack" provides pg_file_write, pg_file_rename, pg_file_unlink. Together these are enough for simple file system access, although they don't provide as much as UTL_FILE. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re[3]: [GENERAL] Problems with pg_dump for PG8.4 for WinXP (MinGW build)
> Hello! > Thank you very much for your answer. I try to use gdb now. But there is a > little difficulty. I'm not quite sure it is connected with postgres build. > Probably I do something wrong using gdb but maybe you could show me the > direction for resolving the problem? > I built PostgreSQL 8.4 (WinXP, MinGW) with following configuration options: > configure --without-zlib --enable-debug --enable-cassert --enable-depend > So I supposed the result should include debugging symbols. > But I don't see the function names in the backtrace. It looks like: > (gdb) bt > #0 0x7c8106e9 in ?? () > #1 0x in ?? () > (gdb) > Maybe you could advise me how to get more detailed information? > I'll appreciate any help. Hello! Sorry for disturbing. I've found out what was the problem. I should set CFLAGS in configuration options. Now there is the following: configure --without-zlib --enable-debug --enable-cassert --enable-depend CFLAGS='-Zi -g' Regards, Marina. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Build PG 8
Лучшие предложения интернет-магазинов на Това�...@mail.ru http://r.mail.ru/cln4267/torg.mail.ru/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Build PG 8
-Original Message- From: el dorado To: pgsql-general@postgresql.org Date: Fri, 18 Sep 2009 15:24:24 +0400 Subject: [GENERAL] Build PG 8 Sorry, the previous letter was sent by mistake. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Build PG 8.4 (MinGW vs. MSVC)
Hello! I need to build PG 8.4 from sources for WinXP. There are two ways to do it: by means of MinGW of by means of MSVC. Firstly I used MinGW (gcc), but unfortunately I noticed some problems with result biniries (f.e. http://archives.postgresql.org/pgsql-general/2009-09/msg00526.php). What is recommended now for building PG 8.4 for WinXP? Is it better to use MSVC compiler? Thank in advance. Regards, Marina. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Build PG 8.4 (MinGW vs. MSVC)
2009/9/18 el dorado : > Hello! > I need to build PG 8.4 from sources for WinXP. There are two ways to do it: > by means of MinGW of by means of MSVC. Firstly I used MinGW (gcc), but > unfortunately I noticed some problems with result biniries (f.e. > http://archives.postgresql.org/pgsql-general/2009-09/msg00526.php). > What is recommended now for building PG 8.4 for WinXP? Is it better to use > MSVC compiler? > Thank in advance. MingW is perfectly supported, and we know people use it. MSVC is the recommended one I'd say, mainly because that's how the official binaries are built, so the results are much more well-tested. It also produces builds that are compatible with standard platform debugger and profiling tools. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Does PG cache results of an aggregate function, (and results of non-volatile functions)?
On Thu, Sep 3, 2009 at 3:44 AM, Allan Kamau wrote: > Hi, > I do have a query which make use of the results of an aggregate > function (for example bit_or) several times in the output column list > of the SELECT clause, does PostgreSQL simply execute the aggregate > function only once and provide the output to the other calls to the > same aggregate function. > How about the case of non volatile functions? Do they get executed as > many times as they occur in the select clause? I don't think so...could you provide a better example? It sounds like your query is one that might benefit from use of CTE... merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Does PG cache results of an aggregate function, (and results of non-volatile functions)?
Merlin Moncure writes: > On Thu, Sep 3, 2009 at 3:44 AM, Allan Kamau wrote: >> I do have a query which make use of the results of an aggregate >> function (for example bit_or) several times in the output column list >> of the SELECT clause, does PostgreSQL simply execute the aggregate >> function only once and provide the output to the other calls to the >> same aggregate function. >> How about the case of non volatile functions? Do they get executed as >> many times as they occur in the select clause? > I don't think so...could you provide a better example? It sounds like > your query is one that might benefit from use of CTE... Might be overkill --- nodeAgg.c does check for textually identical aggregate calls and avoid computing them more than once. But looking at the actual query and its explain plan would be necessary to be sure. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] gap in sequence numbers
Hi,
Please consider the following query.
The reftable inner query generates a table with 11 elements, 7 of them
having the groupkey 1, and the 4 others the groupkey 2. This set is then
grouped by the groupkey field, and after that a generate_series and a
sequence are used to create an ordered id.
My question is : why do the ids created by the sequence include a gap of
1 between the two groups ?
ie, why the id 5 is not present in the result set ?
I know I already ran into this kind of problem, and solved it by using
nextval outside of the query, but i'd like to understand the real reason
of this behaviour.
create sequence temp_seq2 start with 1;
select
nextval('temp_seq2') as id,
groupkey,
generate_series(1, groupnb) as rang
from (
select
groupkey
, count(*) as groupnb
from (
select
id
, case
when id <= 7 then 1
else 2
end as groupkey
from
generate_series(1, 11) as id
) as reftable
group by
groupkey
) as foo;
drop sequence if exists temp_seq2;
Thanks for your help,
Vincent
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgadmin is changing pgpass.conf
Raymond O'Donnell wrote: On 16/09/2009 10:55, Howard Cole wrote: Hi All, I have been having a few problems with my password file recently, causing my scheduled pg_dump to fail. The problem is that the pgpass.conf file keeps changing. Eventually I narrowed it down to using pgadmin. Every time I start pgadmin, it changes the contents of my pgpass file, even though I do not store my password in pgadmin. This only seems to have become a problem since installing 8.4 and associated version of pgadmin. Is this the desired behaviour or is it a bug? It's the desired behaviour, and is described in the pgAdmin docs. As I remember there was also a discussion (on the pgadmin-support list I think) when this feature was added, on the merits or otherwise of storing passwords in plain text on disk. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- Hi Ray, Thanks for the update. Unfortunately this behaviour has the side effect of deleting passwords that I have set up in the file manually for other applications (namely the backup), which runs under the same user account. I guess I'll just have to come up with an alternative. Howard. www.selestial.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] gap in sequence numbers
On Fri, Sep 18, 2009 at 10:00 AM, Picavet Vincent wrote: > Hi, > Please consider the following query. > The reftable inner query generates a table with 11 elements, 7 of them > having the groupkey 1, and the 4 others the groupkey 2. This set is then > grouped by the groupkey field, and after that a generate_series and a > sequence are used to create an ordered id. > > My question is : why do the ids created by the sequence include a gap of > 1 between the two groups ? > ie, why the id 5 is not present in the result set ? > > I know I already ran into this kind of problem, and solved it by using > nextval outside of the query, but i'd like to understand the real reason > of this behaviour. It's never safe to assume that functions in the select list are executed only once per returned row. You _must_ push it into a subquery if you need protection from this. Another case is like this: SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; Superficially you might assume that you will only acquire 100 locks but you never know, because there is no guarantee that the planner will not materialize the row behind the scenes based on its own needs. I'm not completely sure what _exactly_ is causing your exact query to do that...I expect it's something to do with how the SRF is expanded in-line like that (which is also kinda fringe behavior, although I use it often!). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgadmin is changing pgpass.conf
On 18/09/2009 16:07, Howard Cole wrote: > Thanks for the update. Unfortunately this behaviour has the side effect > of deleting passwords that I have set up in the file manually for other > applications (namely the backup), which runs under the same user > account. I guess I'll just have to come up with an alternative. Hi Howard, I'm afraid that's about the size of it. It's probably a good idea to have a separate account for executing the backup in any case. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgadmin is changing pgpass.conf
Raymond O'Donnell wrote: > On 18/09/2009 16:07, Howard Cole wrote: > >> Thanks for the update. Unfortunately this behaviour has the side effect >> of deleting passwords that I have set up in the file manually for other >> applications (namely the backup), which runs under the same user >> account. I guess I'll just have to come up with an alternative. > > Hi Howard, > > I'm afraid that's about the size of it. It's probably a good idea to > have a separate account for executing the backup in any case. Surely pgadmin preserves any existing entries in pgpass.conf? -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] concatenation issue ( 8.4 )
I have a table with name_first name_middle name_last if i try concatenating as such: SELECT name_first || ' ' || name_middle || ' ' || name_last FROM mytable ; I end up with NULL as the concatenated string whenever any of the referred fields contain a NULL value I tried some text conversion and explicit casting , but that didn't work What am I doing wrong ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] concatenation issue ( 8.4 )
On 18/09/2009 16:52, Jonathan Vanasco wrote: > I have a table with > name_first > name_middle > name_last > > if i try concatenating as such: > SELECT > name_first || ' ' || name_middle || ' ' || name_last > FROM > mytable > ; > > I end up with NULL as the concatenated string whenever any of the > referred fields contain a NULL value > > I tried some text conversion and explicit casting , but that didn't work > > What am I doing wrong ? Use the coalesce() function to ensure that you get non-null values, thus: select coalesce(name_first, '') || ' ' || coalesce (name_middle, '') Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re[GENERAL] petitive fields
Hello, I would like to create with several tables but each table will have 2 fields, LASTUSERID & LASTUPDATEDATETIME. My quest is: Is there any way to create these 2 fields and then use them in all tables? Please help the soonest table. Thanks in advance Elias -- View this message in context: http://www.nabble.com/Repetitive-fields-tp25511976p25511976.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] concatenation issue ( 8.4 )
On 18 Sep 2009, at 18:25, Raymond O'Donnell wrote:
On 18/09/2009 16:52, Jonathan Vanasco wrote:
I have a table with
name_first
name_middle
name_last
if i try concatenating as such:
SELECT
name_first || ' ' || name_middle || ' ' || name_last
FROM
mytable
;
I end up with NULL as the concatenated string whenever any of the
referred fields contain a NULL value
I tried some text conversion and explicit casting , but that didn't
work
What am I doing wrong ?
Use the coalesce() function to ensure that you get non-null values,
thus:
select
coalesce(name_first, '') || ' ' || coalesce (name_middle, '')
Or better yet (you won't get double spaces if any value is NULL):
select
coalesce(name_first, '') || coalesce (' ' || name_middle, '')
Alban Hertroys
--
Screwing up is the best way to attach something to the ceiling.
!DSPAM:737,4ab3c88e11681661021018!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NAS
On Thu, Sep 17, 2009 at 10:06 AM, Marco Fortina wrote: > > Hello there! > > A customer of our company would like to create a 2 nodes cluster connected > to an external NAS storage. I would like to know if PostgreSQL supports its > datafiles on this kind of storage and if this solution is certified. probably you are interested in the hot standby feature which is looking like a lock for 8.5. it's not exactly what you asked for (you need two complete copies of your database), but you get high availability, a queryable slave, and very strong data guarantees if combined with synchronous replication. As for NAS, just pretend it stands for: Nightmare (of) Administrator Scenario :-) merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Re[GENERAL] petitive fields
ekekakos wrote: Hello, I would like to create with several tables but each table will have 2 fields, LASTUSERID & LASTUPDATEDATETIME. My quest is: Is there any way to create these 2 fields and then use them in all tables? Please help the soonest table. Thanks in advance Elias No. Perhaps you were thinking you could create a table w/ these two columns and then alter your other tables to inherit that table. But I don't think that's a great idea and those columns already have to exist in the child tables in order to be able to alter the inheritance. You'll have to add the columns manually. You should be able to write a function in a few minutes that walks a cursor and adds them to each table in a given schema. Scott Bailey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NAS
On Thu, 17 Sep 2009, Marco Fortina wrote: A customer of our company would like to create a 2 nodes cluster connected to an external NAS storage. I would like to know if PostgreSQL supports its datafiles on this kind of storage and if this solution is certified. It's possible to setup this configuration with PostgreSQL, but all of the failover logic needs to be implemented with another program that does things like making sure only one copy of the database server can be active at a time. There are a couple of products that aim to handle this situation around. The FAQ for EnterpriseDB, who sells a commercial and supported version of PostgreSQL, talks about a couple of them in a helpful way: http://www.enterprisedb.com/products/allfaq.do I believe (and the FAQ suggests) they've got some setups like this among their customers already. Any of the projects they mention, Linux-HA, Red Hat Cluster, or Veritas Cluster, *could* be made to handle this goal, but it's going to require some customization to make that work for your customer. Should still be much less expensive when it's all said and done than similar offerings from the better known database vendors up-front, particularly when you consider the annual license fees of some of them for clustered solutions. Open-source projects such at PostgreSQL don't "certify" things as working; that's the sort of job better a company selling products. There are subtle hardware issues you need to be aware of with NAS to get both reliability and good performance, and as I already suggested lots of work to get the software right too. EnterpriseDB might have a certified production suggestion available, and you can find plenty of other companies who do this sort of work in the directory http://www.postgresql.org/support/professional_support ; I'd suggest you peruse that. Other companies on that list who I know do lots of custom replication consulting work include 2nd Quadrant, Command Prompt, and End Point. It would be pretty expensive to develop something in-house for this approach starting from scratch. I'd guess some number of months of full-time work for someone who was new to PostgreSQL and/or replication before they got it right. In my role as full-time pessimist, I would also suggest you definitely don't want to rely on anybody's first attempt at setting up replication, it's complicated and way to easy to screw it up in a way you won't notice until your data has already been eaten. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] haversine formula with postgreSQL
On Thu, 17 Sep 2009, Jonathan wrote:
Invalid query: ERROR: column "distance" does not exist LINE
1: ...ude ) ) ) ) AS distance FROM aaafacilities HAVING distance <...
^
You can't use distance in a HAVING clause if distance is computed as part
of the query result. You can rewrite this to use a subquery instead:
SELECT * FROM
(SELECT 'ID', 'FACILITY', 'ADDRESS', latitude, longitude,
( 3959 * acos( cos( radians('%s') ) * cos( radians
( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin
( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance
FROM aaafacilities)
HAVING distance < '%s' ORDER BY dist LIMIT 0 OFFSET 20
And I think that might work for you. The performance is going to be
miserable for large stat sets, because it's going to scan the whole
aaafacilities table every time and recompute every distance, but as an
example goes it's probably acceptable.
Be warned that this example is like a textbook example of how to introduce
a SQL injection vulnerability into your code. Once you get the syntax
right, you should be using a parameterized query here rather than
generting the query using sprintf before exposing this code to the outside
world.
--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] haversine formula with postgreSQL
And I think that might work for you. The performance is going to be miserable for large stat sets, because it's going to scan the whole aaafacilities table every time and recompute every distance, but as an example goes it's probably acceptable. Something I did when implementing haversine in Oracle was first calculate a bounding box for lat/lon and only calculate the distance for cities w/in the bounding box. Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL on Windows Server 2003 for Moodle
Trevor ,
If you have just a need to install and use Windows Server
2003 , you can do that one easily . Just install it go to data
directory modify conf files
Vikram
On Sep 14, 5:43 pm, trevor.john...@tafensw.edu.au ("Johnson, Trevor")
wrote:
> Hi
>
> I'm new to PostgreSQL and I'm looking for management guides tutorials
> etc. for running PostgreSQL on Windows Server 2003.
>
> We will be using PostgreSQL is to run Moodle, so if anyone can point me
> in the right direction for setup or recommended configuration of
> PostgreSQL for Moodle that would be great also.
>
> Regards
>
> Trevor
>
> Trevor Johnson
> Applications and Database Administrator | Information Management
> Services Division | TAFE NSW Riverina Institute
> Ph 02 69381351 | Fax 02 69381432 | Mob 0418 600606 | email:
> trevor.john...@det.nsw.edu.au
>
> **
> This message is intended for the addressee named and may contain
> privileged information or confidential information or both. If you
> are not the intended recipient please delete it and notify the sender.
> **
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Return 30% of results?
Is there a way to return a percentage of the rows found? I tried window functions but get an error... ERROR: syntax error at or near "OVER" SELECT id, cume_dist FROM ( SELECT id, cume_dist() OVER (ORDER BY id) FROM employees ) s WHERE cume_dist < 0.3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Return 30% of results?
Nick wrote on 18.09.2009 23:31: Is there a way to return a percentage of the rows found? I tried window functions but get an error... ERROR: syntax error at or near "OVER" SELECT id, cume_dist FROM ( SELECT id, cume_dist() OVER (ORDER BY id) FROM employees ) s WHERE cume_dist < 0.3 Works for me. Are you sure you are using Postgrs 8.4? (Windowing functions are not available in earlier versions) Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] MD5 sum mismatch in source rpm
I can't install the 8.2.14 or 8.4.1 source rpms: # rpm -Uhv postgresql-8.2.14-1PGDG.f11.src.rpm warning: postgresql-8.2.14-1PGDG.f11.src.rpm: Header V4 DSA signature: NOKEY, key ID 442df0f8 1:postgresql warning: user devrim does not exist - using root warning: group devrim does not exist - using root ### [100%] error: unpacking of archive failed on file /root/rpmbuild/SOURCES/Makefile.regress;4ab40ac6: cpio: MD5 sum mismatch Tried from three mirrors. I know from a previous email that I should build the source rpms myself but just in case it matters. Regards, Clodoaldo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MD5 sum mismatch in source rpm
Hi Clodoaldo, On Fri, 2009-09-18 at 19:42 -0300, Clodoaldo Neto wrote: > > # rpm -Uhv postgresql-8.2.14-1PGDG.f11.src.rpm > warning: postgresql-8.2.14-1PGDG.f11.src.rpm: Header V4 DSA signature: > NOKEY, key ID 442df0f8 >1:postgresql warning: user devrim does not exist - > using root > warning: group devrim does not exist - using root > ### [100%] > error: unpacking of archive failed on file > /root/rpmbuild/SOURCES/Makefile.regress;4ab40ac6: cpio: MD5 sum > mismatch > > Tried from three mirrors. I know from a previous email that I should > build the source rpms myself but just in case it matters. Could you please download this one and re-test? http://yum.pgsqlrpms.org/srpms/8.2/fedora/fedora-11-i386/postgresql-8.2.14-1PGDG.f11.src.rpm I tested that SRPM on my local server, and : $ rpm -Uvh postgresql-8.2.14-1PGDG.f11.src.rpm 1:postgresql ### [100%] If the SRPM above works for you, I will re-upload srpms to PostgreSQL FTP directory. Also, you can verify the package with the following sum: $ sha1sum postgresql-8.2.14-1PGDG.f11.src.rpm b5b48eca92da041abcbb879225badb8de1b85edf postgresql-8.2.14-1PGDG.f11.src.rpm Regards, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Return 30% of results?
//Display the top 40 % of rows in BB_DEPARTMENT SQL> select ROWNUM,ID FROM EMPLOYEES WHERE ROWNUM < .3*(SELECT COUNT(ID) FROM BB _DEPARTMENT) GROUP BY ROWNUM,ID; ROWNUM -- 1 HTH Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > From: nboutel...@gmail.com > Subject: [GENERAL] Return 30% of results? > Date: Fri, 18 Sep 2009 14:31:29 -0700 > To: pgsql-general@postgresql.org > > Is there a way to return a percentage of the rows found? I tried > window functions but get an error... > > ERROR: syntax error at or near "OVER" > > SELECT id, cume_dist FROM ( > SELECT id, cume_dist() OVER (ORDER BY id) FROM employees > ) s > WHERE cume_dist < 0.3 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Hotmail: Powerful Free email with security by Microsoft. http://clk.atdmt.com/GBL/go/171222986/direct/01/
Re: [GENERAL] Return 30% of results?
On Fri, Sep 18, 2009 at 07:54:30PM -0400, Martin Gainty wrote: > > //Display the top 40 % of rows in BB_DEPARTMENT > > SQL> select ROWNUM,ID FROM EMPLOYEES WHERE ROWNUM < .3*(SELECT COUNT(ID) FROM > BB > _DEPARTMENT) GROUP BY ROWNUM,ID; > > ROWNUM > -- > 1 > HTH > Martin Gainty That's Oracle, not PostgreSQL. Are you really trying to help here? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MD5 sum mismatch in source rpm
2009/9/18 Devrim GÜNDÜZ : > Hi Clodoaldo, > > On Fri, 2009-09-18 at 19:42 -0300, Clodoaldo Neto wrote: >> >> # rpm -Uhv postgresql-8.2.14-1PGDG.f11.src.rpm >> warning: postgresql-8.2.14-1PGDG.f11.src.rpm: Header V4 DSA signature: >> NOKEY, key ID 442df0f8 >>1:postgresql warning: user devrim does not exist - >> using root >> warning: group devrim does not exist - using root >> ### [100%] >> error: unpacking of archive failed on file >> /root/rpmbuild/SOURCES/Makefile.regress;4ab40ac6: cpio: MD5 sum >> mismatch >> >> Tried from three mirrors. I know from a previous email that I should >> build the source rpms myself but just in case it matters. > > Could you please download this one and re-test? > > http://yum.pgsqlrpms.org/srpms/8.2/fedora/fedora-11-i386/postgresql-8.2.14-1PGDG.f11.src.rpm > > I tested that SRPM on my local server, and : > > $ rpm -Uvh postgresql-8.2.14-1PGDG.f11.src.rpm > 1:postgresql ### [100%] > > If the SRPM above works for you, I will re-upload srpms to PostgreSQL > FTP directory. Also, you can verify the package with the following sum: > > $ sha1sum postgresql-8.2.14-1PGDG.f11.src.rpm > b5b48eca92da041abcbb879225badb8de1b85edf postgresql-8.2.14-1PGDG.f11.src.rpm > Although I have already built some srpms I was just following recipes and I never really tried to understand what I was doing so it is probably my mistake. This time I was trying to install the f11 srpm (the only one I found in the mirrors) in f8 and got the above error. Installing the f11 srpm in f10 works as also works installing the f8 srpm in f8. So the srpm in the mirrors is not corrupted. Sorry for the noise. Clodoaldo > Regards, > -- > Devrim GÜNDÜZ, RHCE > Command Prompt - http://www.CommandPrompt.com > devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr > http://www.gunduz.org > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
