Re: [GENERAL] Question on a select
Bruno Wolff III wrote: There should be parenthesis around the list to test. WHERE a_name, a_type, a_dir NOT IN ( should be WHERE (a_name, a_type, a_dir) NOT IN ( That did it (I think)! I believe that the NOT IN query should run comparably to the LEFT JOIN example supplied by the other person (at least in recent versions of Postgres). I would expect this to run faster than using NOT EXISTS. You probably want to try all 3. The semantics of the three ways of doing this are not all equivalent if there are NULLs in the data being used to eliminate rows. As you indicated you don't have NULLs this shouldn't be a problem. Another way to write this is using set different (EXCEPT or EXCEPT ALL) using the key fields and then joining back to table a to pick up the other fields. However this will almost certianly be slower than the other methods. Something odd, now that I have the other method working (I think)... tle-bu= SELECT a.file_name, a.file_parent_dir, a.file_type FROM file_info_1 a LEFT JOIN file_set_1 b ON a.file_name=b.fs_name AND a.file_parent_dir=b.fs_parent_dir AND a.file_type=b.fs_type WHERE b.fs_name IS NULL; returns the results in roughly 1 or 2 seconds on a test data set of 15,000 entries. I have an index on both 'file_info_1' covering 'fs_name', 'fs_parent_dir' and 'fs_type' and on 'file_set_1' covering 'file_name', 'file_parent_dir' and 'file_type'. When I try the seconds method though: tle-bu= SELECT file_name, file_parent_dir, file_type FROM file_info_1 WHERE (file_name, file_parent_dir, file_type) NOT IN (SELECT fs_name, fs_parent_dir, fs_type FROM file_set_1); It took so long to process that after roughly three minutes I stopped the query for fear of overheating my laptop (which happend a while back forcing a thermal shut down). The indexes are: CREATE INDEX file_info_#_display_idx ON file_info_# (file_type, file_parent_dir, file_name); CREATE INDEX file_set_#_sync_idx ON file_set_# (fs_name, fs_parent_dir, fs_type) Are these not effective for the second query? If not, what should I change or add? If so, would you have any insight into why there is such an incredible difference in performance? Thanks very much again!! Madison ---(end of broadcast)--- TIP 3: 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: [GENERAL] Question on a select
They are all 'not null' and I am trying to do exactly the kind of task you described. I tried the first example on my DB and got a syntax error: tle-bu= SELECT a.file_name, a.file_parent_dir, a.file_type FROM file_info_1 a WHERE NOT EXIST (SELECT NULL FROM file_set_1 b WHERE b.fs_name=a.file_name, b.fs_parent_dir=a.file_parent_dir, b.fs_type=a.file_type); ERROR: syntax error at or near SELECT at character 88 I've quickly read the thread and I don't think you got an answer as to why you are getting a syntax error here. Your query shows something line WHERE b.fs_name=a.file_name, b.fs_parent_dir=a.file_parent_dir you need to put an AND instead of a comma: WHERE b.fs_name=a.file_name AND b.fs_parent_dir=a.file_parent_dir As for which of the queries is best I don't know. My background is as an Oracle developer. I think that Bruno already suggested testing the three queries. There is a trace utility which shows some of what happens under the covers of a query. I've used it extensively in Oracle but have never used it in Postgresql. If I understand what you said, the NOT IN was significantly slower. That has been my experience in Oracle long time ago so I've tended to shy away from that syntax. I'm sure optimizers are much better now then when I experimented with NOT IN but my coworker who tried it in Oracle was getting a slower response than with a subselect about a year ago. Theoretically if 3 queries are logically equivalent as the three queries you've been given, an optimizer should find the same best query plan to execute it. I don't think that optimizers are that smart yet. The outer join is probably doing either a sort merge or a hash join. In your application this should be the best option. (A sort merge sorts both tables first or at least the key columns and then merges the tables together.) Bruno said that the subselect would be slower. It may be that he thinks it will do a nested loop. That is that it will read each row in table A and try to find that concatenated key in table B's index. I don't think that a nested loop would be very good in your particular application. As for the indexes you set up, I think they are correct indexes. Vincent ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Question on a select
The indexes are: CREATE INDEX file_info_#_display_idx ON file_info_# (file_type, file_parent_dir, file_name); CREATE INDEX file_set_#_sync_idx ON file_set_# (fs_name, fs_parent_dir, fs_type) Are these not effective for the second query? If not, what should I change or add? If so, would you have any insight into why there is such an incredible difference in performance? I didn't look at your indexes closely enough. When you have concatenated index, you want to have the most selective colum first. I guess that file_type is not very selective. file_name is probably the most selective. In the above, the index on file_set_# is optimal. The index on file_info_# is suboptimal. However, if the query is doing a hash join or sort merge, an index is not used so the index doesn't matter. However, you probably do other queries that do use the index so it should be fixed. Vincent ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL]
Hi all, Is it possible to count and display the number of children of a parent in a generic query? parent table: id child table: id, parent_id Example output of the query: parentidnumber_of_children parent1 2 parent2 6 parent3 0 Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 3: 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: [GENERAL] many similar indexbased selects are extremely slow
I use a bigger psql-table to store information and keep an id-value of how big ? each row in memory of my application for faster access. related to the previous question : are you sure there won't be a day where it won't fit ? My applications is able to calculate a list of needed id's in very short time and then wants to retrieve all rows corresponding to this id's. select field1,field2,field3 from mytable where id=XX; Good for one id, see below There is a index on the id-field and the id-field is of type OID, so everything should be quite fast. Unfortunately it is not. Why not use integer (serial) instead of oid ? On a 2GHz-machine with 1GB RAM it takes approx. 10seconds to fetch 1 rows. In this testscenario I only fetch the OID and no other col. I think this is pretty fast. The machine runs 1K queries/s, including generating the query, passing it to postgres via a socket, parsing it, executing it, returning one row via a socket, etc. If you want faster results you'll have to get all your results in one query, and only then can get it a lot faster (not mentioning reducing your server load by a lot !) I dont understand this. Am I expecting far to much? Is 10seconds for the retrieval of 1 OIDs a fine value? I want it to be less than one For 10K queries, it's fast ! I also tried to use the IN-operator, which is much more slower. Is there That's what I'd advise you to use. You should find why it's slow and make it fast. Why not post the EXPLAIN ANALYZE results for a SELECT * FROM thetable WHERE id_artikel IN (1000 values) for instance ? WHat plan does it choose ? If all else fails, you can create a set-returning function which will take an array of id's as its parameter, loop on it, do a SELECT for each oid, and RETURN NEXT for each result ; then you can process the whole result set in one query ; but it'll be slower than a propermy optimized IN query... ---(end of broadcast)--- TIP 3: 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: [GENERAL] Function Parameters
Maybe you could use arrays as some function parameters ? Can you explain why you need so many parameters ? On Sat, 1 Jan 2005 22:25:02 -0700, Michael Fuhr [EMAIL PROTECTED] wrote: On Sun, Jan 02, 2005 at 01:31:22AM +, Oluwatope Akinniyi wrote: I tried to create a function with about 60 input parameters and got an error message that a function cannot take more than 32 parameters. What's the function's purpose? Why does it need so many arguments? You might be able to get around the limitation with a composite type, but maybe there's a different way to do what you want. Another possibility would be to rebuild PostgreSQL and change the limit. I don't know what the implications are other than what the comment in the code says: There is no specific upper limit, although large values will waste system-table space and processing time and Changing these requires an initdb. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] many similar indexbased selects are extremely slow
select field1,field2,field3 from mytable where id=XX; For instance, on my machine : SELECT * FROM bigtable with 2M rows WHERE id IN (list of 500 values) takes 10 ms. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] disabling OIDs?
On Sat, Jan 01, 2005 at 06:35:30PM -0800, Jeff Davis wrote: On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote: OK, thanks. So is there any real benefit in doing this in a generic (non-dspam) sense, or is it just a hack that wouldn't be noticable? Any risks or potential problems down the line? I'd just like to add that some 3rd party applications/interfaces make use of OIDs, as a convenient id to use if there is no primary key (or if the 3rd party software doesn't take the time to find the primary key). One might argue that those 3rd party applications/interfaces are broken, but you still might want to keep OIDs around in case you have a use for one of those pieces of software. Yep, especially since an OID is not a unique value and so can't possibly be a primary key and generally isn't indexed either. Even Access asks you to identify the primary key... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpS9allrWi4v.pgp Description: PGP signature
Re: [GENERAL] Question on a select
Vincent Hikida wrote: The indexes are: CREATE INDEX file_info_#_display_idx ON file_info_# (file_type, file_parent_dir, file_name); CREATE INDEX file_set_#_sync_idx ON file_set_# (fs_name, fs_parent_dir, fs_type) Are these not effective for the second query? If not, what should I change or add? If so, would you have any insight into why there is such an incredible difference in performance? I didn't look at your indexes closely enough. When you have concatenated index, you want to have the most selective colum first. I guess that file_type is not very selective. file_name is probably the most selective. In the above, the index on file_set_# is optimal. The index on file_info_# is suboptimal. However, if the query is doing a hash join or sort merge, an index is not used so the index doesn't matter. However, you probably do other queries that do use the index so it should be fixed. Vincent Thank you, Vincent! I didn't realize that the order made a difference. A sign of how much learning I need to do. :p For reference, I think 'file_parent_dir' and 'fs_parent_dir' are the most important because I do an 'ORDER BY [fs|file]_parent_dir ASC' on most queries. I've made the changes, thank you again! Madison ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] PostgreSQL 8.0.0 Release Candidate 3
As was anticipated, time between Release Candidate 2 and 3 was nice and short, with more changes being made now to Documentation vs Code. A current list of *known* supported platforms can be found at: http://developer.postgresql.org/supported-platforms.html We're always looking to improve that list, so we encourage anyone that is running a platform not listed to please report on any success or failures with Release Candidate 3. Baring *any* coding changes (documentation != code) over the next week or so, we *hope* that this will the final Release Candidate before Full Release, with that being aimed for the 15th (or earlier). As always, this release is available on all mirrors, as listed at: http://www.postgresql.org/mirrors-ftp.html For those using Bittorrent, David Fetter has updated the .torrents, which can be downloaded from: http://bt.postgresql.org Please report any bug reports with this Release Candidate to: pgsql-bugs@postgresql.org Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Question on a select
I didn't realize that the order made a difference. A sign of how much learning I need to do. :p For reference, I think 'file_parent_dir' and 'fs_parent_dir' are the most important because I do an 'ORDER BY [fs|file]_parent_dir ASC' on most queries. I've made the changes, thank you again! If you SELECT ... WHERE condition on A order by B : an index on A will be used, but an index on B won't If you SELECT ... WHERE condition on A order by A, B : an index on A,B will be used because it will give the rows in already sorted order ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Shared Sequences?
Is there any way, with PG 8 rc 3, to share a sequence across databases - assuming all databases are on the same machine? Thanks, Dunc ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Shared Sequences?
begin C. Duncan Hudson [EMAIL PROTECTED] wrote: Is there any way, with PG 8 rc 3, to share a sequence across databases - assuming all databases are on the same machine? Thanks, Possibly via contrib/dblink, also on ealier versions. I'm not sure about sequences, but with tables this is possible. end Andreas -- Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau- fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-) ---(end of broadcast)--- TIP 3: 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: [GENERAL] Large Objects
BYTEA is not always pragmatic. What is the file is 100 megs? 256 megs? What is the size when bytea become inafective ? I don't think it's so much a matter of effectiveness, it makes no difference at all in storage space. Ah, thanks, good to know. Something new to learn every day... The issue is that if you store it in a field, accessing it becomes an all or nothing affair, which means if it's a 100Mb object, it's all going to be accessed whenever you ask for it. At least for reads you are wrong. You can use substring() on bytea quite nicely. Remember, however, that that operates on *bytes*, not characters. Also be careful about encodings being set for the connection. At least with PostgreSQL 7.4 we found we had to reset client_encoding despite the docs saying encodings won't affect bytea field reads. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL]
Useful to add a title to your messages before you post... How about: select parentid, count(*) as number_of_children from childtable group by parentid order by parentid; If there are parent records that have no children then these will be omitted. The query above totally ignores the parent table (which you may not want it to do). John Sidney-Woollett Joost Kraaijeveld wrote: Hi all, Is it possible to count and display the number of children of a parent in a generic query? parent table: id child table: id, parent_id Example output of the query: parentidnumber_of_children parent1 2 parent2 6 parent3 0 Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] OS X shared memory problems 8.0rc3
I just downloaded and installed RC3 on my OS X system (10.3.7), and I'm getting a shared memory error when trying to run initdb (error message listed below). I received a similar error a few weeks ago after upgrading my OS to 10.3.7, but I was able to get around that by reducing the shared_buffers setting in postgresql.conf. I'm not sure how to work around this one, though, since I'm starting from scratch and the error is coming from initdb. What is the best way to correct this situation? Any help would be appreciated! Thanks, Tim Error message from initdb: /usr/local/pgsql tperrigo$ sudo -u postgres initdb --encoding=UNICODE /usr/local/pgsql/data The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating directory /usr/local/pgsql/data/global ... ok creating directory /usr/local/pgsql/data/pg_xlog ... ok creating directory /usr/local/pgsql/data/pg_xlog/archive_status ... ok creating directory /usr/local/pgsql/data/pg_clog ... ok creating directory /usr/local/pgsql/data/pg_subtrans ... ok creating directory /usr/local/pgsql/data/base ... ok creating directory /usr/local/pgsql/data/base/1 ... ok creating directory /usr/local/pgsql/data/pg_tblspc ... ok selecting default max_connections ... 10 selecting default shared_buffers ... 50 creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=1, size=1155072, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1155072 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 10). The PostgreSQL documentation contains more information about shared memory configuration. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Shared Sequences?
On Sun, Jan 02, 2005 at 10:44:16AM -0500, C. Duncan Hudson wrote: Is there any way, with PG 8 rc 3, to share a sequence across databases - assuming all databases are on the same machine? As Andreas Kretschmer mentioned, you might be able to use dblink. Why do you want to share a sequence across databases? Could you use separate schemas in one database instead of separate databases? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: 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: [GENERAL] PostgreSQL 8.0.0 Release Candidate 3
Does this release incorporate a change to the bgwriter or was it determined to leave it as-is until 8.1? Regards, Jeff Davis On Sun, 2005-01-02 at 10:56 -0400, Marc G. Fournier wrote: As was anticipated, time between Release Candidate 2 and 3 was nice and short, with more changes being made now to Documentation vs Code. A current list of *known* supported platforms can be found at: http://developer.postgresql.org/supported-platforms.html We're always looking to improve that list, so we encourage anyone that is running a platform not listed to please report on any success or failures with Release Candidate 3. Baring *any* coding changes (documentation != code) over the next week or so, we *hope* that this will the final Release Candidate before Full Release, with that being aimed for the 15th (or earlier). As always, this release is available on all mirrors, as listed at: http://www.postgresql.org/mirrors-ftp.html For those using Bittorrent, David Fetter has updated the .torrents, which can be downloaded from: http://bt.postgresql.org Please report any bug reports with this Release Candidate to: pgsql-bugs@postgresql.org Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: 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: [GENERAL] OS X shared memory problems 8.0rc3
Timothy Perrigo [EMAIL PROTECTED] writes: I just downloaded and installed RC3 on my OS X system (10.3.7), and I'm getting a shared memory error when trying to run initdb (error message listed below). I received a similar error a few weeks ago after upgrading my OS to 10.3.7, but I was able to get around that by reducing the shared_buffers setting in postgresql.conf. I think you probably are trying to run two postmasters at once. You really need to increase the OS X memory limits, instead. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Select number of children of a parent query
Hi John, John Sidney-Woollett schreef: Useful to add a title to your messages before you post... It escaped before finishing. How about: select parentid, count(*) as number_of_children from childtable group by parentid order by parentid; It works but can you tell me why this works? Is the count(*) over the group by parentid? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Function Parameters
On Sun, Jan 02, 2005 at 01:31:22AM +, Oluwatope Akinniyi wrote: Hi, Compliments of the season. I tried to create a function with about 60 input parameters and got an error message that a function cannot take more than 32 parameters. Generally, a function with that many input parameters is a sign of a bad function design, rather than of an unreasonable limit in PostgreSQL. Is there a way around this? Or Am I in error? Um, how do I put this gently...a function with that many parameters means it's overwhelmingly likely that you are. Other people have made suggestions about recompiling PostgreSQL, hacking the source code, etc., etc. These are things you should only attempt when you are absolutely certain that there is no other way to do what you need to do than with a function of 60 parameters. Here, absolutely certain means having gone over the design of the entire application, re-doing all of it if needed, because if you go down the road of having a hand-hacked PostgreSQL, you severely limit the community's ability to help you when you encounter a problem. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Shared Sequences?
Michael Fuhr wrote: On Sun, Jan 02, 2005 at 10:44:16AM -0500, C. Duncan Hudson wrote: Is there any way, with PG 8 rc 3, to share a sequence across databases - assuming all databases are on the same machine? As Andreas Kretschmer mentioned, you might be able to use dblink. Why do you want to share a sequence across databases? Could you use separate schemas in one database instead of separate databases? Unfortunately, the app that I'm using doesn't yet support schemas - otherwise I'd be doing that. I have 3 instances of the application (each for a different business unit) and I don't want them generating the same numbers for different things. I want the numbers, across all business units, to be truly chronological - so I'd like them to share the same sequence. Dunc ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Function Parameters
On Sun, Jan 02, 2005 at 12:56:52PM -0800, David Fetter wrote: Other people have made suggestions about recompiling PostgreSQL, hacking the source code, etc., etc. These are things you should only attempt when you are absolutely certain that there is no other way to do what you need to do than with a function of 60 parameters. I mentioned changing the limit in the code and rebuilding but I hope that wasn't taken as a recommendation to do so. I'll echo what David says about rethinking what you're doing (hence my earlier question about why you need so many parameters). Hacking the code should only be a last resort. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Select number of children of a parent query
You might need to read a good SQL primer to get a full explanation of this feature. Reading from the docs, http://www.postgresql.org/docs/7.4/interactive/sql-select.html quote The optional GROUP BY clause has the general form GROUP BY expression [, ...] GROUP BY will condense into a single row all selected rows that share the same values for the grouped expressions. expression can be an input column name, or the name or ordinal number of an output column (SELECT list item), or an arbitrary expression formed from input-column values. In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name. Aggregate functions, if any are used, are computed across all rows making up each group, producing a separate value for each group (whereas without GROUP BY, an aggregate produces a single value computed across all the selected rows). When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column. /quote John Sidney-Woollett Joost Kraaijeveld wrote: Hi John, John Sidney-Woollett schreef: Useful to add a title to your messages before you post... It escaped before finishing. How about: select parentid, count(*) as number_of_children from childtable group by parentid order by parentid; It works but can you tell me why this works? Is the count(*) over the group by parentid? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Shared Sequences?
On Sun, 2005-01-02 at 16:19 -0500, C. Duncan Hudson wrote: [about databases sharing a sequence] I have 3 instances of the application (each for a different business unit) and I don't want them generating the same numbers for different things. I want the numbers, across all business units, to be truly chronological - so I'd like them to share the same sequence. do you realize that sequences are not garanteed to be chronological? gnari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Index, Tablespace and performance
Hi, I have a 4 x SCSI in RAID 10 (PG 7.4.6) with a regular performance. My database have 55 Gb of data. In PG 8, moving the indexes to a separeted disk (or array) can (generally) improve the performance if the system make a heavy use of indexes ? Thanks Alexandre ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OS X shared memory problems 8.0rc3
On Jan 2, 2005, at 12:58 PM, Tom Lane wrote: Timothy Perrigo [EMAIL PROTECTED] writes: I just downloaded and installed RC3 on my OS X system (10.3.7), and I'm getting a shared memory error when trying to run initdb (error message listed below). I received a similar error a few weeks ago after upgrading my OS to 10.3.7, but I was able to get around that by reducing the shared_buffers setting in postgresql.conf. I think you probably are trying to run two postmasters at once. You really need to increase the OS X memory limits, instead. regards, tom lane No, I just ran pg_ctl status to check, and here was the output: pg_ctl: neither postmaster nor postgres running I then ran initdb, and got the error message I posted before. I only want to run 1 postmaster at a time...any ideas? Thanks, Tim ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OS X shared memory problems 8.0rc3
Timothy Perrigo [EMAIL PROTECTED] writes: On Jan 2, 2005, at 12:58 PM, Tom Lane wrote: I think you probably are trying to run two postmasters at once. You really need to increase the OS X memory limits, instead. No, I just ran pg_ctl status to check, and here was the output: pg_ctl: neither postmaster nor postgres running Proves only that you weren't running another postmaster in the same data directory. Try ps auxww | grep post regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Joined delete
Hi all, After all the great help I got from you all I managed to finish what I was struggling with. The last thing I need to do should be pretty simple. :) I use this query to return values from one table that don't exits in another table: SELECT a.fs_name, a.fs_parent_dir, a.fs_type FROM file_set_1 a LEFT JOIN file_info_1 b ON a.fs_name=b.file_name AND a.fs_parent_dir=b.file_parent_dir AND a.fs_type=b.file_type WHERE b.file_name IS NULL; Which works perfectly. What I need to do though is instead of returning those value, I need to instead delete the matches. I tried: DELETE FROM file_set_1 a LEFT JOIN file_info_1 b ON a.fs_name=b.file_name AND a.fs_parent_dir=b.file_parent_dir AND a.fs_type=b.file_type WHERE b.file_name IS NULL; But I am getting the syntax error: ERROR: syntax error at or near a at character 24 This is probably obious... ^.^; Thank you again! Madison ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] function in postgres
Hi, The function CHAR in db2 returns a fixed length character string representation of an integer number.What is the corresponding function in Postgres? Regards, Vinita Bansal _ NRIs send 10 photos FREE to India. http://creative.mediaturf.net/creatives/icicibank/june/kodak/OTP.htm And win a FREE ticket to India. ---(end of broadcast)--- TIP 3: 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
