Re: [GENERAL] DISTINCT ON without ORDER BY
Martijn van Oosterhout klep...@svana.org writes: SELECT * FROM foo WHERE id in (SELECT max(id) FROM foo GROUP BY bar); Is there a way to acheive the above result without a sort and without a self-join? Something like SELECT bar, (magic_agg_func(foo)).* FROM foo GROUP BY bar where you define an aggregate function magic_agg_func to remember the whole record for the largest value of id. Something like: postgres=# create function magic_transition(a,a) returns a as 'select case when $1.aid $2.aid then $1 else $2 end' language sql; postgres=# create aggregate magic (a) (sfunc = magic_transition, stype = a); Not sure it'll be faster though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- 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] Nooby Q: Should this take five hours? And counting?
Kenneth Tilton kentil...@gmail.com writes: A bit. I killed the indexing and jacked work_mem up to 500mb, indexing then finished in 7 min 25s. Yer a genius! FWIW creating indexes using maintenance_work_mem. I would not expect changing work_mem to really help much. That's, uh, curious. 2: You've got a slow disk subsystem, if you're already seeing 25% IOWait with only ~2 to 3 megs a second being written. This has been passed along to management for consideration. Depends. If it's all random i/o then 8-12MB/s is about right for 4-6 drives. If there's any sequential i/o mixed in then yeah, it's pretty poor. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- 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] Frequently unable connecting to db server doesn't listen
On Apr 20, 2009, at 4:10 AM, Net Tree Inc. wrote: The problem I am having has happened a few times within in a week. I am repeatly not able to connect to the db and having server doesn't listen message without touch anything, it just happen. First time it happen after I restarting my computer that has not been restart for almost a month. After restarting and trying to connect to DB and it failed, ... also I notice my computer can not be restart nor shutdown under the normal way by click on start menu and shutdown or restart. I can only shut it down use the hard way by pressing the power button, I don't know why, but its another story. That's probably a (maybe _the_) cause of your troubles. After the DB was shut down the hard way it will probably be in recovery mode the next time you start it. You can't connect to the DB while it's in recovery mode. I think the first thing you need to focus on is to find out why you can't shut down your system normally. If you can't find a solution for that, at least stop the PostgreSQL service (I'm guessing the name, I don't have PG on my game box) before you press the power button. Therefore for DB, I only can fix it by uninstall and reinstall PostgreSQL. But it happening repeatly. Reinstalling shouldn't be necessary, it's probably enough to wait until recovery is complete. The logs can tell you what's going on. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,49ec3820129741572128411! -- 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] One command to rule them all?
On Sunday 19 April 2009 19:57:40 Raymond O'Donnell wrote: I remember some time back there was a discussion about implementing a single catch-all command for PostgreSQL, to replace (or perhaps rather encompass) the various other utilities we currently use (psql, pg_dump, createdb, etc etc). Did that idea die a death, or is it still on the radar somewhere? I think it was never really alive. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] converting from bytea to integers
I'd like to convert some bytea data to an array of four byte integers (and vice versa). I'm probably missing something obvious, but I don't see an efficient way to generate a 4 byte integer from a bytea string (could be big endian or little endian). Converting back to bytea seems easy enough using to_hex. Thanks for any suggestions, John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [DOCS] [GENERAL] Postgresql 8.3X supports Arrays of Composite Types?
On Mon, Apr 20, 2009 at 8:13 AM, Robson Fidalgo r...@cin.ufpe.br wrote: Hi David, Thanks for your help, but I want a relational-object solution. The solution presented by Tom Lane (Thanks Tom) runs very well and it is a relational-object implementation (I suggest put a similar example in postgresql 8.3X documentation). Cheers, Robson. On Sun, Apr 19, 2009 at 8:56 PM, David Fetter da...@fetter.org wrote: On Sun, Apr 19, 2009 at 06:03:26PM -0300, Robson Fidalgo wrote: Hello, I am using postgresql 8.3X and I created a table (see example below) that has an attribute that is an Array of a Composite Type (ROW). However, I do not know how can I insert a record in this table. You want a normalized table anyhow. If you want something denormalized, use a view. Example: CREATE table phone ( cod varchar, num varchar); CREATE TABLE person ( name varchar, telephone phone[]); This is better as: CREATE TABLE phone ( cod VARCHAR, num VARCHAR, PRIMARY KEY(cod, num) ); CREATE TABLE person ( name varchar, PRIMARY KEY(name) ) CREATE TABLE person_phone ( name VARCHAR NOT NULL REFERENCES person(name), cod VARCHAR, num VARCHAR, FOREIGN KEY(cod, num) REFERENCES phone(cod, num), PRIMARY KEY(name, cod, num) ); Cheers, David. -- David Fetter da...@fetter.org 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-docs mailing list (pgsql-d...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
[GENERAL] Does Postgresql 8.3X support Object/Class Methods and association between objects with REF?
Hello, I am study the relational-object paradigm with postgresql 8.3X and I know that it supports composite and/or array attributes. However, I didn't find documentation/examples about the usage of Object/Class Methods and association between objects with REF (like oracle does). Then, I'd like to know if theses concepts (Method and REF) are supported by postgresql 8.3X? I've searched the mailing lists and have found little about the OR features. There is some book or URL with a good material about postgres 8.3 OR features? Below follows an example (Son has a REF for his Father and a get_name method) in oracle. Then, how can I do a similar example in Postgresql 8.3X? (Just a code example help me! You don't need explain the example.) Thanks in advances, Robson. CREATE TYPE tp_father AS OBJECT ( name varchar2(60)); CREATE OR REPLACE TYPE BODY tp_father AS MEMBER FUNCTION get_name RETURN varchar2 IS BEGIN RETURN name; END; END; CREATE TYPE tp_son AS OBJECT ( name varchar2(60), father REF tp_father); CREATE TABLE father OF tp_father; CREATE TABLE son OF tp_son;
Re: [GENERAL] postgreSQL amazon ec2 cloud
Do you think that it could useful mounting two different EBS to handle data and pg_xlog ? cheers, ste -- 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 Postgresql 8.3X support Object/Class Methods and association between objects with REF?
2009/4/20 Robson Fidalgo robson.fida...@gmail.com: Hello, I am study the relational-object paradigm with postgresql 8.3X and I know that it supports composite and/or array attributes. However, I didn't find documentation/examples about the usage of Object/Class Methods and association between objects with REF (like oracle does). Then, I'd like to know if theses concepts (Method and REF) are supported by postgresql 8.3X? I've searched the mailing lists and have found little about the OR features. There is some book or URL with a good material about postgres 8.3 OR features? Below follows an example (Son has a REF for his Father and a get_name method) in oracle. Then, how can I do a similar example in Postgresql 8.3X? (Just a code example help me! You don't need explain the example.) Thanks in advances, Hello this feature isn't supported regards Pavel Stehule Robson. CREATE TYPE tp_father AS OBJECT ( name varchar2(60)); CREATE OR REPLACE TYPE BODY tp_father AS MEMBER FUNCTION get_name RETURN varchar2 IS BEGIN RETURN name; END; END; CREATE TYPE tp_son AS OBJECT ( name varchar2(60), father REF tp_father); CREATE TABLE father OF tp_father; CREATE TABLE son OF tp_son; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] xlog vs. shmfs
I know that it's guaranteed to cause problems when putting xlog in a disk mounted from shared memory, but in what scale? Am I looking at data loss or full database cluster corruption? I'm running postgresql 8.2.4. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error stranger
I get this error when make a select below SELECT * FROM batch.funcionalidade_iniciada where proi_id = x ERROR: missing chunk number 0 for toast value 458755 SQL state: XX000 Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com
Re: [GENERAL] xlog vs. shmfs
On Mon, Apr 20, 2009 at 09:28:20PM +0800, Botao Pan wrote: I know that it's guaranteed to cause problems when putting xlog in a disk mounted from shared memory, but in what scale? Am I looking at data loss or full database cluster corruption? I'm running postgresql 8.2.4. There is no essential difference between the data in your user tables and the data in the catalogs that let you read your own data. So corruption could be anything between losing some data in your tables to complete wiping out of columns or tables or even the entire DB (if you're unlucky). Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [DOCS] [GENERAL] Postgresql 8.3X supports Arrays of Composite Types?
Hi David, Thanks for your help, but I want a relational-object solution. The solution presented by Tom Lane (Thanks Tom) runs very well and it is a relational-object implementation (I suggest put a similar example in postgresql 8.3X documentation). Cheers, Robson. On Sun, Apr 19, 2009 at 8:56 PM, David Fetter da...@fetter.org wrote: On Sun, Apr 19, 2009 at 06:03:26PM -0300, Robson Fidalgo wrote: Hello, I am using postgresql 8.3X and I created a table (see example below) that has an attribute that is an Array of a Composite Type (ROW). However, I do not know how can I insert a record in this table. You want a normalized table anyhow. If you want something denormalized, use a view. Example: CREATE table phone ( cod varchar, num varchar); CREATE TABLE person ( name varchar, telephone phone[]); This is better as: CREATE TABLE phone ( cod VARCHAR, num VARCHAR, PRIMARY KEY(cod, num) ); CREATE TABLE person ( name varchar, PRIMARY KEY(name) ) CREATE TABLE person_phone ( name VARCHAR NOT NULL REFERENCES person(name), cod VARCHAR, num VARCHAR, FOREIGN KEY(cod, num) REFERENCES phone(cod, num), PRIMARY KEY(name, cod, num) ); Cheers, David. -- David Fetter da...@fetter.org 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-docs mailing list (pgsql-d...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] [GENERAL] Postgresql 8.3X supports Arrays of Composite Types?
On Mon, Apr 20, 2009 at 08:13:15AM -0300, Robson Fidalgo wrote: Hi David, Thanks for your help, but I want a relational-object solution. You can have one without denormalizing. Just use VIEWs and rewrite RULEs for INSERTs, UPDATEs and DELETEs on them. The solution presented by Tom Lane (Thanks Tom) runs very well and it is a relational-object implementation (I suggest put a similar example in postgresql 8.3X documentation). The docs already contain an example: http://www.postgresql.org/docs/current/static/rules-update.html There are excellent reasons not to encourage people to do only half the job. One part, the smaller part, is presenting an interface which one part of your OO code can talk to. The other part, and the much larger one, is having a well-indexed, normalized data store underneath. Example: Under the store-the-compound system you're proposing, how do you find all the people who have a common prefix? Answer: Normalize. If you need that answer quickly, you're looking at down time and DDL changes. The questions you ask about the data are impossible to know in advance, so normalized data helps you deal with that. Cheers, David. -- David Fetter da...@fetter.org 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
[GENERAL] New 8.4 features
Hello, Some of the new language features in 8.4 seem like pretty major additions. I know that the window functions will be very useful. There have been many times in the past when I've wanted to aggregate in this way: http://elegantcode.com/2009/01/04/sql-window-clause/ If this is possible now it'll help massively in rapid prototyping - in the past, reports involving aggregates have needed the most time to develop. Another interesting feature is recursive SQL. I know 8.4 is only just in beta but it would be good to learn what is (and isn't) possible. These seem like the sorts of things that would get good writeups at varlena.com but I see there haven't been any new posts there in a couple of years. My question is, is anyone planning to blog / write focussing on these features? Oliver Kohll www.gtwm.co.uk - company www.gtportalbase.com - product -- 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] New 8.4 features
There's quite few articles about it on depesz.com. And since I started to blog recently about my own skirmishes/encounters with postgresql too - I'll probably write something about it. So far, personally - I used WITH() for PI calculations, as an example. -- 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] Set search_path for session
Thanks everyone! A follow up question: Is there any way to set the search_path to all existing schemas? I'm looking for some kind of wild card method that will automatically pick up every schema. Thanks, Mary -- 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] Problem with pgpool-II tool
Quoting aravind chandu avin_frie...@yahoo.com: I have encountered a problem while configuring pgpool-II,I encountered a problem while executing the following command. You really need to post this on the pgpool list, not here. Subscribe here: http://pgfoundry.org/mailman/listinfo/pgpool-general Henry -- 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 amazon ec2 cloud
Hi, I wanted to reply to an existing thread but it seems a new one has been created, so I think more details are required... I want to run my postgres DB on Amazon EC2 using a EBS persistent disk for postgres installation. In this way data and pg_xlog will be on the same disk. I was just wondering if also on ec2 with EBS disks having pg_xlog on a different EBS disk could be useful. Thanks in advance ste Stefano Nichele wrote: Do you think that it could useful mounting two different EBS to handle data and pg_xlog ? cheers, ste -- 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 amazon ec2 cloud
From the (very little) work I've done with EC2, it seems that what you get logically doesn't have much relation to what you get physically. I don't recall any guarentee that a virtual disk is equivilent to a physical spindle in terms of determining performance, or even that your virtual disk will perform consistently over time. Of course, you should test a few setups and see how they perform. On Mon, 20 Apr 2009, Stefano Nichele wrote: Hi, I wanted to reply to an existing thread but it seems a new one has been created, so I think more details are required... I want to run my postgres DB on Amazon EC2 using a EBS persistent disk for postgres installation. In this way data and pg_xlog will be on the same disk. I was just wondering if also on ec2 with EBS disks having pg_xlog on a different EBS disk could be useful. Thanks in advance ste Stefano Nichele wrote: Do you think that it could useful mounting two different EBS to handle data and pg_xlog ? cheers, ste -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Space for pg_dump
Hi,
Rainer Bauer use...@munnin.com writes:
Greg Smith wrote:
Since running an entire pgdump can take forever on a big database,
what I
usually do here is start by running the disk usage query at
http://wiki.postgresql.org/wiki/Disk_Usage
Interesting. However, the query gives an error if the table name
contains
upper case characters, like in my case tblConnections:
ERROR: relation public.tblconnections does not exist.
Replacing all occurences of relname by '' || relname || ''
fixes the
error.
That still fails if the table name contains double quotes. A proper
solution is to use the table OID --- I've corrected the example.
If you have big toast tables you get wrong results with the query
suggested
at http://wiki.postgresql.org/wiki/Disk_Usage because it takes the
toasted
values not into account.
Simple example (take a look at the first row - public.media):
SELECT nspname || '.' || relname AS relation,
pg_size_pretty(pg_relation_size(C.oid)) AS size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname !~ '^pg_toast'
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
relation |size
---+
public.media | 727 MB
public.identifier_idx | 342 MB
public.media_pk | 190 MB
public.mediateypes_pk | 16 kB
public.mediaformats_uk| 16 kB
public.contentsizes_pk| 16 kB
public.contenttype_pk | 16 kB
public.mediaformats_pk| 16 kB
public.contenttypes | 8192 bytes
public.media_media_id_seq | 8192 bytes
public.contentsizes | 8192 bytes
public.mediaformats | 8192 bytes
public.mediatypes | 8192 bytes
public.vmedia2| 0 bytes
public.vmedia | 0 bytes
(15 rows)
Now a fixed query which gets the sizes of the related pg_toast_oid and
pg_toast_oid_index too:
SELECT nspname || '.' || relname AS relation,
pg_size_pretty(pg_relation_size(C.oid)
+ COALESCE((SELECT pg_relation_size(C2.oid) FROM
pg_class C2 WHERE C2.relname = 'pg_toast_' || C.oid ),0::bigint)
+ COALESCE((SELECT pg_relation_size(C3.oid) FROM
pg_class C3 WHERE C3.relname = 'pg_toast_' || C.oid || '_index'),
0::bigint)
) AS size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname !~ '^pg_toast'
ORDER BY pg_relation_size(C.oid)
+ COALESCE((SELECT pg_relation_size(C2.oid) FROM
pg_class C2 WHERE C2.relname = 'pg_toast_' || C.oid ),0::bigint)
+ COALESCE((SELECT pg_relation_size(C3.oid) FROM
pg_class C3 WHERE C3.relname = 'pg_toast_' || C.oid || '_index'),
0::bigint)
DESC
LIMIT 20;
relation |size
---+
public.media | 164 GB
public.identifier_idx | 342 MB
public.media_pk | 190 MB
public.contenttype_pk | 16 kB
public.contenttypes | 16 kB
public.contentsizes | 16 kB
public.contentsizes_pk| 16 kB
public.mediateypes_pk | 16 kB
public.mediaformats | 16 kB
public.mediatypes | 16 kB
public.mediaformats_pk| 16 kB
public.mediaformats_uk| 16 kB
public.media_media_id_seq | 8192 bytes
public.vmedia | 0 bytes
public.vmedia2| 0 bytes
(15 rows)
There is a difference of about 163 GB (which is from the toast of
public.media)
relation |size
-+
pg_toast.pg_toast_6366088 | 162 GB
pg_toast.pg_toast_6366088_index | 1832 MB
public.media| 727 MB
If you have only small or no toast tables the query from the wiki will
be working for you.
regards, jan
--
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] Space for pg_dump
Jan Otto wrote: If you have big toast tables you get wrong results with the query suggested at http://wiki.postgresql.org/wiki/Disk_Usage because it takes the toasted values not into account. Now a fixed query which gets the sizes of the related pg_toast_oid and pg_toast_oid_index too: Note that there is also the pg_total_relation_size function which will report the total table size, including toast table and indexes. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Space for pg_dump
On Apr 20, 2009, at 7:35 PM, Alvaro Herrera wrote: Jan Otto wrote: If you have big toast tables you get wrong results with the query suggested at http://wiki.postgresql.org/wiki/Disk_Usage because it takes the toasted values not into account. Now a fixed query which gets the sizes of the related pg_toast_oid and pg_toast_oid_index too: Note that there is also the pg_total_relation_size function which will report the total table size, including toast table and indexes. Ahhh, it was a long day... sometimes i think too complicated ;-) regards, jan -- 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 amazon ec2 cloud
Hi Stefano I'm intrigued with Amazon EC2 and did a little search on it. From your experience could it be used in similar fashion as a website - running a Postgresql database and having users access the database through an interface? If so, would the users need a Java environment installed on their machines or would the interface connect directley to Postgresql - like a website?? Bob - Original Message - From: Stefano Nichele stefano.nich...@gmail.com To: pgsql-general@postgresql.org Sent: Monday, April 20, 2009 9:26 AM Subject: Re: [GENERAL] postgreSQL amazon ec2 cloud Hi, I wanted to reply to an existing thread but it seems a new one has been created, so I think more details are required... I want to run my postgres DB on Amazon EC2 using a EBS persistent disk for postgres installation. In this way data and pg_xlog will be on the same disk. I was just wondering if also on ec2 with EBS disks having pg_xlog on a different EBS disk could be useful. Thanks in advance ste Stefano Nichele wrote: Do you think that it could useful mounting two different EBS to handle data and pg_xlog ? cheers, ste -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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 amazon ec2 cloud
Bob Pawley wrote: From your experience could it be used in similar fashion as a website - running a Postgresql database and having users access the database through an interface? If so, would the users need a Java environment installed on their machines ... um, the users would need a Java environment if they were running Java applications. or would the interface connect directley to Postgresql - like a website?? what 'interface' are you referring to ? a client application can connect to a postgres server with the postgres protocol, by default over port 5432/tcp... a client application would do this using libpq, or a native interface like jdbc (if the client is java) -- 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] New 8.4 features
On Mon, 20 Apr 2009, Oliver Kohll - Mailing Lists wrote: My question is, is anyone planning to blog / write focussing on these features? There's been regular blog posting from Hubert Lubaczewski in particular covering 8.4 features for over a year now. http://www.planetpostgresql.org/ is a good place to find PostgreSQL oriented blogs at. I've been collecting 8.4 related blog and talk presentations onto a list at http://wiki.postgresql.org/wiki/Waiting_for_8.4 and encourage others to expand on that with ones I've missed. -- * 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] New 8.4 features
Greg Smith wrote: I've been collecting 8.4 related blog and talk presentations onto a list at http://wiki.postgresql.org/wiki/Waiting_for_8.4 and encourage others to expand on that with ones I've missed. http://lwn.net/SubscriberLink/328591/3fdb051da4bfee26/ -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] New 8.4 features
On Mon, 2009-04-20 at 16:17 +0100, Grzegorz Jaśkiewicz wrote: There's quite few articles about it on depesz.com. And since I started to blog recently about my own skirmishes/encounters with postgresql too - I'll probably write something about it. So far, personally - I used WITH() for PI calculations, as an example. David Fetter also has a lot of material (mostly talk slides) at http://fetter.org/ Jeff Davis -- 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 amazon ec2 cloud
I'll run a java webapp running in tomcat connected to postgres via jdbc. BTW, why the access method should be important ? I mean, my main question is should pg_xlog be located on a different EBS than data ? My doubt is really about logical vs physical disk, since i think EBS is logical and two EBS disks could be run on the same physical disk.so why to separate data and pg_xlog ? Is it still useful ? It 's like to put data and pg_xlog on two partitions of the same disk. Cheers, ste John R Pierce wrote: Bob Pawley wrote: From your experience could it be used in similar fashion as a website - running a Postgresql database and having users access the database through an interface? If so, would the users need a Java environment installed on their machines ... um, the users would need a Java environment if they were running Java applications. or would the interface connect directley to Postgresql - like a website?? what 'interface' are you referring to ? a client application can connect to a postgres server with the postgres protocol, by default over port 5432/tcp... a client application would do this using libpq, or a native interface like jdbc (if the client is java) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Doubt about join queries
Hello I have a table with clients and other with stores, I want to calculate minimum distances between stores and clients, the client name and its closer store. At this moment I can only get clients ids and minimum distances grouping by client id, but when I try to join their respective store id, postgres requires me to add store id in group clause and it throws as many rows as the product of number clients and stores. This result is wrong, I only expect the minimum distance for every client. My code looks like this: SELECT distances.client_id, min(distances.distance) FROM( SELECT stores.id AS store_id, clients.id AS client_id, sqrt(power(store.x)+power(store.y)) AS distance FROM stores, clients WHERE 1=1 ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id; Also I've tried this: SELECT clients.id, MIN(distances.distance) FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id, sqrt(power(stores.x)+power(stores.y)) AS distance FROM stores, clients WHERE 1=1) distances ON distances.client_id = clients.id GROUP BY clients.id Thanks in advance! -- View this message in context: http://www.nabble.com/Doubt-about-join-queries-tp23142980p23142980.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
[GENERAL] Re-Install data folder failure
I have been having troubles getting 8.3 running on Windows XP. My last couple of attempts have been to install 8.3 in a folder off the root rather than off of 'Program Files'. Since I ‘uninstalled’ it, I have not been able to get a good installation. I have tried many times. I use Control Panels AddRemove to remove 8.3 and then I go through the registry and remove any remaining entries tjat address 'postgres'. When I start a new install, it always points to c: \program files\postg… which I change on the setup page to the new c: \pghome. I have a leftover environmental variable pgdata which points to c:\pghome\8.3\data. But this folder never gets built anymore. That is, there is not data folder. Any ideas on how I can get this built? I am installing this from an administrator account and I have a postgres account that is a power user. I have tried but can not install from that account. The PostgreSQL notes say that it is no longer necessary to operate from a non-administrator account so I tried to do things from the admin account and when it doesn’t work, I try it from the postgres power user account. So far, neither have worked. But I am guessing that is due to the no ‘data’ folder. Ray -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] update one table with another
Hello, I am looking for a way to update one table with another. I tried the following schema to update table2 based on data in table1. The idea is that I have a slowly changing dimension and I need to update data in the dimension based on an updated version of the table. I don't want to have to drop my foreign key constraints, delete all the data, copy new data, and then re-establish the constraints. The schema (taken from forums.devshed.com): update table2 set link = t1.link from table2 t2 inner join table1 t1 on t2.name = t1.name Applying to my own situation: update entities set customer_status = t1.customer_status from entities t2 inner join entity_dimension_update t1 on t2.entity_id = t1.entity_id (where entitiy_id is my primary key and customer_status is a varchar(11) and entity_dimension_update is a structural copy of entities) I'm not sure I entirely follow the logic here. I understand that the table being updated is somehow joined with the table in the from clause. I suppose that by including table2 itself as part of a joined table in the from clause, something like a join between the updated table and the updating table is accomplished (joined on a key to ensure a unique update value). The query ran for half an hour before I cancelled it. I am using Postgres 8.3. The only constraint on either table is the primary key constraint on entity_id. Each table has 62960 rows. Is there something I can do to speed this process up? Thank you, Matt -- matthew.pugs...@gmail.com
Re: [GENERAL] Doubt about join clause
In response to jc_mich juan.mich...@paasel.com: Hello I have a table with clients and other with stores, I want to calculate minimum distances between stores and clients, the client name and its closer store. At this moment I can only get clients ids and minimum distances grouping by client id, but when I try to join their respective store id, postgres requires me to add store id in group clause and it throws as many rows as the product of number clients and stores. This result is wrong, I only expect the minimum distance for every client. My code looks like this: SELECT distances.client_id, min(distances.distance) FROM( SELECT stores.id AS store_id, clients.id AS client_id, sqrt(power(store.x)+power(store.y)) AS distance FROM stores, clients WHERE 1=1 ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id; Also I've tried this: SELECT clients.id, MIN(distances.distance) FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id, sqrt(power(stores.x)+power(stores.y)) AS distance FROM stores, clients WHERE 1=1) distances ON distances.client_id = clients.id GROUP BY clients.id Thanks in advance! Something like this should work, (assuming I understand your tables): SELECT clients.id, stores.id, min(sqrt(power(clients.x-stores.x)+power(clients.y-stores.y)) FROM clients, stores GROUP BY clients.id, stores.id; -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] update one table with another
I've solved it. I just used a subselect. Worked very quickly. I had a lot of trouble with subqueries when I first started databases with MySQL. So I have been afraid of them. update entities set customer_status = select(customer_status from entity_dimension_update where entities.entity_id = entity_dimension_update.entity_id); Worked almost instantly. My apologies for the spam. -- matthew.pugs...@gmail.com
[GENERAL] trouble with to_char('L')
Hi, my database has UTF8 encoding and Finnish locale, the client_encoding and the console is set to WIN1252. I created a table with a single NUMERIC(5,2) column and inserted a few values. Running a query 'SELECT to_char(money, '999D99L') FROM table' through psql gives the following error message: ERROR: invalid byte sequence for encoding UTF8: 0x80 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. The graphical Query tool returns a set of empty rows. The query works ok without the 'L'. Thanks in advance, Mikko -- 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] converting from bytea to integers
John DeSoi wrote: I'd like to convert some bytea data to an array of four byte integers (and vice versa). I'm probably missing something obvious, but I don't see an efficient way to generate a 4 byte integer from a bytea string (could be big endian or little endian). get_byte()? mailtest= \set e '\'\12\15\107\20\'::bytea' mailtest= select get_byte(:e,0),get_byte(:e,1),get_byte(:e,2),get_byte(:e,3); get_byte | get_byte | get_byte | get_byte --+--+--+-- 10 |13 | 71 | 16 (1 row) Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] round behavior differs between 8.1.5 and 8.3.7
Howdy, None of the discussions about rounding so far have addressed what appears to be a significant change that occurred at some point between PostgreSQL v8.1.5 and v8.3.7. Can someone explain to me the difference between the two resultsets below? Additionally I would like to understand what option will consistently provide a banker's-round in v8.3.7, if possible. Here is the query, followed by the resultset for each version: SELECT round(3.5::numeric) as rn3.5, round(3.5::float8) as rf3.5, dround(3.5::numeric) as dn3.5, dround(3.5::float8) as df3.5, CAST(3.5::numeric as INTEGER) as cn3.5, CAST(3.5::float8 as INTEGER) as cf3.5, round(4.5::numeric) as rn4.5, round(4.5::float8) as rf4.5, dround(4.5::numeric) as dn4.5, dround(4.5::float8) as df4.5, CAST(4.5::numeric as INTEGER) as cn4.5, CAST(4.5::float8 as INTEGER) as cf4.5 v8.1.5: rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5 4,4,4,4,4,4,5,4,4,4,5,4 v8.3.7: rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5 4,3,3,3,4,3,5,4,4,4,5,4 Thanks, Robert
Re: [GENERAL] postgreSQL amazon ec2 cloud
On Mon, Apr 20, 2009 at 02:14:00PM +0200, Stefano Nichele wrote: Do you think that it could useful mounting two different EBS to handle data and pg_xlog ? Testing I've participated in suggests that it helps to split pg_xlog elsewhere. Your mileage may vary. - Josh / eggyknap signature.asc Description: Digital signature
Re: [GENERAL] Postgresql 8.3X supports Arrays of Composite Types?
On Sun, Apr 19, 2009 at 5:03 PM, Robson Fidalgo r...@cin.ufpe.br wrote:
Until here is everything ok, but I have not success with insert values, then
I tried:
1)insert into person values ('Joe', '{(1,),(2,) }');
2)insert into person values ('Joe', array[('1',''),('2','')]);
3)insert into person values ('Joe', array[row('1',''),row('2','')]);
4)insert into person values ('Joe',
_phone[phone('1',''),phone('2','')]);**
** considering _phone = name of array type (automatically created by
postgres) and phone = name of composite type (also automatically created by
postgres)
I agree with David -- arrays of composites should not be used in table
definitions. There are exceptions, but you have to be very cautious.
The phone number composite is basically ok, but I'd advise dropping
the array minimum. Here are the basic problems:
*) constraint checking vs. array of composites is problematic
*) updating a specific field of a specific composite is not really
possible...you have to build a complete new composite array and update
the table with it.
*) searching (who has a phone number x?) is a problem
Imagine a client changes one of his/her phone numbers and compare the
sql you would have to write doing it the classic way vs. your way.
As David noted, if you like the composite format in the presentation
of data, you can trivially do this in view. There may be reasons to
do this -- the advantages of composite are convenience in passing data
to/from functions and nesting data returned to the client. There is
no disadvantage of nesting data 'in query' -- that's how I do it and
it works very well.
There are a couple of exceptions to the 'no arrays in table'. You may
have a lot of static data (think float[]) that you are doing numerical
analysis on the client for example. It only ever gets
inserted/selected/deleted in bulk and never updated. There are other
exceptions, but they are rare. Usually it's better doing it the 'sql
way'
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] update one table with another
On Apr 20, 2009, at 10:34 PM, Matthew Pugsley wrote: I've solved it. I just used a subselect. Worked very quickly. I had a lot of trouble with subqueries when I first started databases with MySQL. So I have been afraid of them. update entities set customer_status = select(customer_status from entity_dimension_update where entities.entity_id = entity_dimension_update.entity_id); What a peculiar way to write a subquery, with the braces like that. Normally you'd put the opening brace before the select statement, not after it. Worked almost instantly. Alternatively you could use UPDATE...FROM: update entities set customer_status = t2.customer_status from entity_dimension_update as t2 where entity_id = t2.entity_id Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,49ed0151129747011493647! -- 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] Doubt about join clause
You've understood very well my problem, but also this query works as worse than everything I did before, it throws as many rows as rows are contained my tables clients and stores. I only want to find for every client what store is closer to him, I expect one client to one store and their distance Thanks a lot Bill Moran wrote: In response to jc_mich juan.mich...@paasel.com: Hello I have a table with clients and other with stores, I want to calculate minimum distances between stores and clients, the client name and its closer store. At this moment I can only get clients ids and minimum distances grouping by client id, but when I try to join their respective store id, postgres requires me to add store id in group clause and it throws as many rows as the product of number clients and stores. This result is wrong, I only expect the minimum distance for every client. My code looks like this: SELECT distances.client_id, min(distances.distance) FROM( SELECT stores.id AS store_id, clients.id AS client_id, sqrt(power(store.x)+power(store.y)) AS distance FROM stores, clients WHERE 1=1 ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id; Also I've tried this: SELECT clients.id, MIN(distances.distance) FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id, sqrt(power(stores.x)+power(stores.y)) AS distance FROM stores, clients WHERE 1=1) distances ON distances.client_id = clients.id GROUP BY clients.id Thanks in advance! Something like this should work, (assuming I understand your tables): SELECT clients.id, stores.id, min(sqrt(power(clients.x-stores.x)+power(clients.y-stores.y)) FROM clients, stores GROUP BY clients.id, stores.id; -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- View this message in context: http://www.nabble.com/Doubt-about-join-queries-tp23142980p23146909.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] Doubt about join clause
On Mon, Apr 20, 2009 at 7:39 PM, jc_mich juan.mich...@paasel.com wrote: You've understood very well my problem, but also this query works as worse than everything I did before, it throws as many rows as rows are contained my tables clients and stores. I only want to find for every client what store is closer to him, I expect one client to one store and their distance select clients.id as client_id, (select stores.id from stores order by (power(clients.x-stores.x)+power(clients.y-stores.y)) asc limit 1) as store_id from clients; Should do the trick, or at least something very similar. -- - David T. Wilson david.t.wil...@gmail.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] round behavior differs between 8.1.5 and 8.3.7
On Monday 20 April 2009 2:21:31 pm Robert Morton wrote: Howdy, None of the discussions about rounding so far have addressed what appears to be a significant change that occurred at some point between PostgreSQL v8.1.5 and v8.3.7. Can someone explain to me the difference between the two resultsets below? Additionally I would like to understand what option will consistently provide a banker's-round in v8.3.7, if possible. Here is the query, followed by the resultset for each version: SELECT round(3.5::numeric) as rn3.5, round(3.5::float8) as rf3.5, dround(3.5::numeric) as dn3.5, dround(3.5::float8) as df3.5, CAST(3.5::numeric as INTEGER) as cn3.5, CAST(3.5::float8 as INTEGER) as cf3.5, round(4.5::numeric) as rn4.5, round(4.5::float8) as rf4.5, dround(4.5::numeric) as dn4.5, dround(4.5::float8) as df4.5, CAST(4.5::numeric as INTEGER) as cn4.5, CAST(4.5::float8 as INTEGER) as cf4.5 v8.1.5: rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5 4,4,4,4,4,4,5,4,4,4,5,4 v8.3.7: rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5 4,3,3,3,4,3,5,4,4,4,5,4 Thanks, Robert Well it wasn't 8.3.5 :) because: postgres=# SELECT version(); version PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3) (1 row) postgres=# SELECT postgres-# round(3.5::numeric) as rn3.5, postgres-# round(3.5::float8) as rf3.5, postgres-# dround(3.5::numeric) as dn3.5, postgres-# dround(3.5::float8) as df3.5, postgres-# CAST(3.5::numeric as INTEGER) as cn3.5, postgres-# CAST(3.5::float8 as INTEGER) as cf3.5, postgres-# round(4.5::numeric) as rn4.5, postgres-# round(4.5::float8) as rf4.5, postgres-# dround(4.5::numeric) as dn4.5, postgres-# dround(4.5::float8) as df4.5, postgres-# CAST(4.5::numeric as INTEGER) as cn4.5, postgres-# CAST(4.5::float8 as INTEGER) as cf4.5 postgres-# ; rn3.5 | rf3.5 | dn3.5 | df3.5 | cn3.5 | cf3.5 | rn4.5 | rf4.5 | dn4.5 | df4.5 | cn4.5 | cf4.5 ---+---+---+---+---+---+---+---+---+---+---+--- 4 | 4 | 4 | 4 | 4 | 4 | 5 | 4 | 4 | 4 | 5 | 4 (1 row) -- Adrian Klaver akla...@comcast.net -- 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] round behavior differs between 8.1.5 and 8.3.7
On Monday 20 April 2009 5:20:47 pm Adrian Klaver wrote: On Monday 20 April 2009 2:21:31 pm Robert Morton wrote: Howdy, None of the discussions about rounding so far have addressed what appears to be a significant change that occurred at some point between PostgreSQL v8.1.5 and v8.3.7. Can someone explain to me the difference between the two resultsets below? Additionally I would like to understand what option will consistently provide a banker's-round in v8.3.7, if possible. Here is the query, followed by the resultset for each version: SELECT round(3.5::numeric) as rn3.5, round(3.5::float8) as rf3.5, dround(3.5::numeric) as dn3.5, dround(3.5::float8) as df3.5, CAST(3.5::numeric as INTEGER) as cn3.5, CAST(3.5::float8 as INTEGER) as cf3.5, round(4.5::numeric) as rn4.5, round(4.5::float8) as rf4.5, dround(4.5::numeric) as dn4.5, dround(4.5::float8) as df4.5, CAST(4.5::numeric as INTEGER) as cn4.5, CAST(4.5::float8 as INTEGER) as cf4.5 v8.1.5: rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5 4,4,4,4,4,4,5,4,4,4,5,4 v8.3.7: rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5 4,3,3,3,4,3,5,4,4,4,5,4 Thanks, Robert Well it wasn't 8.3.5 :) because: postgres=# SELECT version(); version --- - PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3) (1 row) postgres=# SELECT postgres-# round(3.5::numeric) as rn3.5, postgres-# round(3.5::float8) as rf3.5, postgres-# dround(3.5::numeric) as dn3.5, postgres-# dround(3.5::float8) as df3.5, postgres-# CAST(3.5::numeric as INTEGER) as cn3.5, postgres-# CAST(3.5::float8 as INTEGER) as cf3.5, postgres-# round(4.5::numeric) as rn4.5, postgres-# round(4.5::float8) as rf4.5, postgres-# dround(4.5::numeric) as dn4.5, postgres-# dround(4.5::float8) as df4.5, postgres-# CAST(4.5::numeric as INTEGER) as cn4.5, postgres-# CAST(4.5::float8 as INTEGER) as cf4.5 postgres-# ; rn3.5 | rf3.5 | dn3.5 | df3.5 | cn3.5 | cf3.5 | rn4.5 | rf4.5 | dn4.5 | df4.5 | cn4.5 | cf4.5 ---+---+---+---+---+---+---+---+---+--- +---+--- 4 | 4 | 4 | 4 | 4 | 4 | 5 | 4 | 4 | 4 | 5 | 4 (1 row) -- Adrian Klaver akla...@comcast.net I upgraded to 8.3.7 and I still don't see what you see. There must be something else going here. postgres=# SELECT version(); version PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3) (1 row) postgres=# SELECT round(3.5::numeric) as rn3.5, round(3.5::float8) as rf3.5, dround(3.5::numeric) as dn3.5, dround(3.5::float8) as df3.5, CAST(3.5::numeric as INTEGER) as cn3.5, CAST(3.5::float8 as INTEGER) as cf3.5, round(4.5::numeric) as rn4.5, round(4.5::float8) as rf4.5, dround(4.5::numeric) as dn4.5, dround(4.5::float8) as df4.5, CAST(4.5::numeric as INTEGER) as cn4.5, CAST(4.5::float8 as INTEGER) as cf4.5 ; rn3.5 | rf3.5 | dn3.5 | df3.5 | cn3.5 | cf3.5 | rn4.5 | rf4.5 | dn4.5 | df4.5 | cn4.5 | cf4.5 ---+---+---+---+---+---+---+---+---+---+---+--- 4 | 4 | 4 | 4 | 4 | 4 | 5 | 4 | 4 | 4 | 5 | 4 (1 row) -- Adrian Klaver akla...@comcast.net -- 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] Doubt about join clause
On Mon, Apr 20, 2009 at 08:02:49PM -0400, David Wilson wrote: On Mon, Apr 20, 2009 at 7:39 PM, jc_mich juan.mich...@paasel.com wrote: You've understood very well my problem, but also this query works as worse than everything I did before, it throws as many rows as rows are contained my tables clients and stores. I only want to find for every client what store is closer to him, I expect one client to one store and their distance select clients.id as client_id, (select stores.id from stores order by (power(clients.x-stores.x)+power(clients.y-stores.y)) asc limit 1) as store_id from clients; Should do the trick, or at least something very similar. Another option would be to use DISTINCT ON and the geometric bits in PG, something like: SELECT DISTINCT ON (client_id) client_id, store_id, distance FROM ( SELECT c.id AS client_id, s.id AS store_id, point(c.x,c.y) - point(s.x,s.y) AS distance FROM clients c, stores s) ORDER BY client_id, distance; I'd also expect there to be some GiST magic that can be weaved to get the above to work somewhat efficiently. -- Sam http://samason.me.uk/ -- 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] converting from bytea to integers
On Apr 20, 2009, at 5:23 PM, Daniel Verite wrote: get_byte()? mailtest= \set e '\'\12\15\107\20\'::bytea' mailtest= select get_byte(:e,0),get_byte(:e,1),get_byte(:e, 2),get_byte(:e,3); get_byte | get_byte | get_byte | get_byte --+-- +--+-- 10 | 13 | 71 | 16 That's what I ended up with. My first attempts at it were unsuccessful because I did not notice that get_byte uses zero indexing. Earlier in the routine I extracted bytes using substring and just assumed they used the same indexing. They don't. It might be worthy of a documentation note -- it seems easy to miss if you have not used the binary functions before. I generated the integer from the bytes using something like this: b1 = get_byte(p_array, i+3); b2 = get_byte(p_array, i+2); b3 = get_byte(p_array, i+1); b4 = get_byte(p_array, i); val = (b1 24) + (b2 16) + (b3 8) + b4; Thanks, John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
