Re: [HACKERS] [GENERAL] Floating point error
Daniel Farina wrote: > On Mon, Mar 4, 2013 at 2:27 PM, Maciek Sakrejda wrote: >> On Sun, Mar 3, 2013 at 9:14 PM, Tom Lane wrote: >>> The real difficulty is that there may be more than one storable value >>> that corresponds to "1.23456" to six decimal digits. To be certain that >>> we can reproduce the stored value uniquely, we have to err in the other >>> direction, and print *more* decimal digits than the underlying precision >>> justifies, rather than a bit less. Some of those digits are going to >>> look like garbage to the naked eye. >> >> I think part of the difficulty here is that psql (if I understand this >> correctly) conflates the wire-format text representations with what >> should be displayed to the user. E.g., a different driver might parse >> the wire representation into a native representation, and then format >> that native representation when it is to be displayed. That's what the >> JDBC driver does, so it doesn't care about how the wire format >> actually looks. >> >>> pg_dump cares about reproducing values exactly, and not about whether >>> things are nice-looking, so it cranks up extra_float_digits. The JDBC >>> driver might be justified in doing likewise, to ensure that the >>> identical binary float value is stored on both client and server --- >>> but that isn't even a valid goal unless you assume that the server's >>> float implementation is the same as Java's, which is a bit of a leap of >>> faith, even if IEEE 754 is nigh universal these days. >> >> I would hope that any driver cares about reproducing values exactly >> (or at least as exactly as the semantics of the client and server >> representations of the data type allow). Once you start talking >> operations, sure, things get a lot more complicated and you're better >> off not relying on any particular semantics. But IEEE 754 >> unambiguously defines certain bit patterns to correspond to certain >> values, no? If both client and server talk IEEE 754 floating point, it >> should be possible to round-trip values with no fuss and end up with >> the same bits you started with (and as far as I can tell, it is, as >> long as extra_float_digits is set to the max), even if the >> implementations of actual operations on these numbers behave very >> differently on client and server. I think given that many ORMs can >> cause UPDATEs on tuple fields that have not changed as part of saving >> an object, stable round trips seem like a desirable feature. But all these things are already available: Any driver that cares can set extra_float_digits=3, and if it prefers the binary format, the wire protocol supports sending floating point values as such. > I also find the rationale for extra_float digits quite mysterious for > the same reason: why would most programs care about precision less > than pg_dump does? > > If a client wants floating point numbers to look nice, I think the > rendering should be on them (e.g. psql and pgadmin), and the default > should be to expose whatever precision is available to clients that > want an accurate representation of what is in the database. > > This kind of change may have many practical problems that may make it > un-pragmatic to alter at this time (considering the workaround is to > set the extra float digits), but I can't quite grasp the rationale for > "well, the only program that cares about the most precision available > is pg_dump". It seems like most programs would care just as much. I don't think that it is about looking nice. C doesn't promise you more than FLT_DIG or DBL_DIG digits of precision, so PostgreSQL cannot either. If you allow more, that would mean that if you store the same number on different platforms and query it, it might come out differently. Among other things, that would be a problem for the regression tests. 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: [GENERAL] 9.2 timestamp function syntax error
Guy Rouillier wrote: > I don't understand the error resulting from the following progression on > 9.2 (specifically "EnterpriseDB 9.2.1.3 on x86_64-unknown-linux-gnu, > compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit"): > > select sysdate => timestamp without time zone > > select timestamptz(sysdate) => timestamp with time zone > > select timestamp(timestamptz(sysdate)) => ERROR: syntax error at or > near "timestamptz" > > OR > > select timestamp(sysdate::timestamptz)) => ERROR: syntax error at or > near "sysdate" > > I see a function in pg_catalog with signature timestamp(timestamp with > time zone). Why isn't it being applied? That must be EnterpriseDB's proprietary Postgres Plus, since regular PostgreSQL doesn't have "sysdate". Try to ask EnterpriseDB, they are more likely to be able to help. 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
[GENERAL] Finding matching words in a word game
Hello, is there maybe a clever way of finding all possible words from a given set of letters by means of PostgreSQL (i.e. inside the database vs. scanning all database rows by a PHP script, which would take too long) - if the dictionary is kept in a simple table like: create table good_words ( word varchar(16) primary key, stamp timestamp default current_timestamp ); I could add a column above, where same letters as in "word" would be sorted alphabetically... but then I don't know. I've described my question in more detail at http://stackoverflow.com/questions/15220072/postgresql-and-word-games Thank you for any suggestions Alex -- 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] Finding matching words in a word game
Or I could add integer columns 'a', 'b', ... ,'z' to the table On Tue, Mar 5, 2013 at 10:29 AM, Alexander Farber wrote: > > create table good_words ( > word varchar(16) primary key, > stamp timestamp default current_timestamp > ); > > http://stackoverflow.com/questions/15220072/postgresql-and-word-games -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Floating point error
Daniel Farina wrote: > This kind of change may have many practical problems that may > make it un-pragmatic to alter at this time (considering the > workaround is to set the extra float digits), but I can't quite > grasp the rationale for "well, the only program that cares about > the most precision available is pg_dump". It seems like most > programs would care just as much. Something to keep in mind is that when you store 0.01 into a double precision column, the precise value stored, when written in decimal, is: 0.0120816681711721685132943093776702880859375 Of course, some values can't be precisely written in decimal with so few digits. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] 9.2 timestamp function syntax error
On 03/05/2013 12:30 AM, Albe Laurenz wrote: Guy Rouillier wrote: I don't understand the error resulting from the following progression on 9.2 (specifically "EnterpriseDB 9.2.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit"): select sysdate => timestamp without time zone select timestamptz(sysdate) => timestamp with time zone select timestamp(timestamptz(sysdate)) => ERROR: syntax error at or near "timestamptz" OR select timestamp(sysdate::timestamptz)) => ERROR: syntax error at or near "sysdate" I see a function in pg_catalog with signature timestamp(timestamp with time zone). Why isn't it being applied? That must be EnterpriseDB's proprietary Postgres Plus, since regular PostgreSQL doesn't have "sysdate". Actually that is not the issue. test=> select version(); version -- PostgreSQL 9.2.3 This does not work: test=> select timestamp(now()::timestampz); ERROR: syntax error at or near "now" LINE 1: select timestamp(now()::timestampz); test=> select timestamp(now()); ERROR: syntax error at or near "now" LINE 1: select timestamp(now()); This does: test=> select (now()::timestamptz)::timestamp; now 2013-03-05 06:23:05.169524 (1 row) test=> select now()::timestamp; now --- 2013-03-05 06:24:43.50932 (1 row) Try to ask EnterpriseDB, they are more likely to be able to help. Yours, Laurenz Albe -- Adrian Klaver adrian.kla...@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: [HACKERS] [GENERAL] Floating point error
On 05.03.2013 15:59, Kevin Grittner wrote: Daniel Farina wrote: This kind of change may have many practical problems that may make it un-pragmatic to alter at this time (considering the workaround is to set the extra float digits), but I can't quite grasp the rationale for "well, the only program that cares about the most precision available is pg_dump". It seems like most programs would care just as much. Something to keep in mind is that when you store 0.01 into a double precision column, the precise value stored, when written in decimal, is: 0.0120816681711721685132943093776702880859375 Of course, some values can't be precisely written in decimal with so few digits. It would be nice to have a base-2 text format to represent floats. It wouldn't be as human-friendly as base-10, but it could be used when you don't want to lose precision. pg_dump in particular. - Heikki -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why does slony use a cursor? Anyone know?
Hey everyone, Frankly, I'm shocked at what I just found. We did a delete last night of a few million rows, and come back this morning to find that slony is 9-hours behind. After some investigation, it became apparent that slony opens up a cursor and orders it by the log_actionseq column. Then it fetches 500 rows, and closes the cursor. So it's fetching several million rows into a cursor, to fetch 500, and then throw the rest away. That is quite possibly the least efficient manner I could think of to build a sync system, so maybe someone knows why they did it that way? At least with a regular query, it could sort by the column it wanted, and put a nifty index on it for those 500-row chunks it's grabbing. I must be missing something... Yeah, I know... millions of rows + slony = bad. But it doesn't have to be *this* bad. Even a few hundred thousand rows would be terrible with this design. I plan on asking the slony guys too, but I figured someone on this list might happen to know. Looks like I might have to look into Bucardo or Londiste. Thanks! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] 9.2 timestamp function syntax error
Adrian Klaver writes:
> This does not work:
> test=> select timestamp(now()::timestampz);
> ERROR: syntax error at or near "now"
timestamp(something) is a type name. Per the comment in gram.y:
* The type names appearing here are not usable as function names
* because they can be followed by '(' in typename productions, which
* looks too much like a function call for an LR(1) parser.
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
Re: [HACKERS] [GENERAL] Floating point error
On Tue, Mar 5, 2013 at 12:03 AM, Albe Laurenz wrote: > I don't think that it is about looking nice. > C doesn't promise you more than FLT_DIG or DBL_DIG digits of > precision, so PostgreSQL cannot either. > > If you allow more, that would mean that if you store the same > number on different platforms and query it, it might come out > differently. Among other things, that would be a problem for > the regression tests. Thank you: I think this is what I was missing, and what wasn't clear from the proposed doc patch. But then how can pg_dump assume that it's always safe to set extra_float_digits = 3? Why the discrepancy between default behavior and what pg_dump gets? It can't know whether the dump is to be restored into the same system or a different one (and AFAICT, there's not even an option to tweak extra_float_digits there). -- 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] 9.2 timestamp function syntax error
On 3/5/2013 9:52 AM, Tom Lane wrote:
Adrian Klaver writes:
This does not work:
test=> select timestamp(now()::timestampz);
ERROR: syntax error at or near "now"
timestamp(something) is a type name. Per the comment in gram.y:
* The type names appearing here are not usable as function names
* because they can be followed by '(' in typename productions, which
* looks too much like a function call for an LR(1) parser.
In PgAdmin, here is how timestamp() is defined:
CREATE OR REPLACE FUNCTION "timestamp"(timestamp with time zone)
RETURNS timestamp without time zone AS
'timestamptz_timestamp'
LANGUAGE internal STABLE STRICT
COST 1;
ALTER FUNCTION "timestamp"(timestamp with time zone)
OWNER TO postgres;
COMMENT ON FUNCTION "timestamp"(timestamp with time zone) IS 'convert
timestamp with time zone to timestamp';
and here is now timestamptz() is defined:
CREATE OR REPLACE FUNCTION timestamptz(timestamp without time zone)
RETURNS timestamp with time zone AS
'timestamp_timestamptz'
LANGUAGE internal STABLE STRICT
COST 1;
ALTER FUNCTION timestamptz(timestamp without time zone)
OWNER TO postgres;
COMMENT ON FUNCTION timestamptz(timestamp without time zone) IS 'convert
timestamp to timestamp with time zone';
Ugh, I just noticed the quotation marks around the timestamp function.
This works:
select "timestamp"(now()::timestamptz); => timestamp without time zone
This is a subtlety bound to be lost on most. Why is there both a
function and a type name with the same name? I suppose I could define a
synonym to make the function name distinct, but this seems like
something that should be addressed.
Thanks.
--
Guy Rouillier
--
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] Finding matching words in a word game
On Tue, Mar 5, 2013 at 1:29 AM, Alexander Farber wrote: > Hello, > > is there maybe a clever way of finding all possible words > from a given set of letters by means of PostgreSQL > (i.e. inside the database vs. scanning all database > rows by a PHP script, which would take too long) - > if the dictionary is kept in a simple table like: > > create table good_words ( > word varchar(16) primary key, > stamp timestamp default current_timestamp > ); > > I could add a column above, where same letters as in "word" > would be sorted alphabetically... but then I don't know. > Yes, that is how I'd do it. Then you'd just build an index on the sorted column and select on the sorted letters. You could do the canonicalization (sorting of the letters) in PHP for every query (and insert) and pass in that value, and so have the database not know what the meaning of the new column is. select word from good_words were sorted_letters=? Or you could create a function in the database that does the sort, and then your PHP would not have to pass in already sorted letters. select word from good_words were sorted_letters=sort_function(?), and also have triggers automatically compute and store the "sorted_letters" from "word" upon insert or update. Cheers, Jeff
Re: [HACKERS] [GENERAL] Floating point error
> "HL" == Heikki Linnakangas writes: HL> It would be nice to have a base-2 text format to represent floats. HL> It wouldn't be as human-friendly as base-10, but it could be used HL> when you don't want to lose precision. pg_dump in particular. hexidecimal notation for floats exists. The printf format flag is %a for miniscule and %A for majuscule. The result of 1./3. is 0xa.aabp-5. This site has some info and a conversion demo: http://gregstoll.dyndns.org/~gregstoll/floattohex/ -JimC -- James Cloos OpenPGP: 1024D/ED7DAEA6 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Floating point error
Maciek Sakrejda writes: > Thank you: I think this is what I was missing, and what wasn't clear > from the proposed doc patch. But then how can pg_dump assume that it's > always safe to set extra_float_digits = 3? It's been proven (don't have a link handy, but the paper is at least a dozen years old) that 3 extra digits are sufficient to accurately reconstruct any IEEE single or double float value, given properly written conversion functions in libc. So that's where that number comes from. Now, if either end is not using IEEE floats, you may or may not get equivalent results --- but it's pretty hard to make any guarantees at all in such a case. > Why the discrepancy between > default behavior and what pg_dump gets? Basically, the default behavior is tuned to the expectations of people who think that what they put in is what they should get back, ie we don't want the system doing this by default: regression=# set extra_float_digits = 3; SET regression=# select 0.1::float4; float4 - 0.10001 (1 row) regression=# select 0.1::float8; float8 - 0.10001 (1 row) We would get a whole lot more bug reports, not fewer, if that were the default behavior. 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
Re: [GENERAL] 9.2 timestamp function syntax error
Guy Rouillier writes: > Ugh, I just noticed the quotation marks around the timestamp function. > This works: > select "timestamp"(now()::timestamptz); => timestamp without time zone > This is a subtlety bound to be lost on most. Why is there both a > function and a type name with the same name? All cast functions are named after the target type, by convention. > I suppose I could define a > synonym to make the function name distinct, but this seems like > something that should be addressed. Really the right way to address it is to use cast notation. The function notation is sometimes convenient, but you have to worry about gotchas like this one, not to mention that some built-in types have multiple aliases. 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
Re: [HACKERS] [GENERAL] Floating point error
On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane wrote: >> Why the discrepancy between >> default behavior and what pg_dump gets? > > Basically, the default behavior is tuned to the expectations of people > who think that what they put in is what they should get back, ie we > don't want the system doing this by default: > > regression=# set extra_float_digits = 3; > SET > regression=# select 0.1::float4; >float4 > - > 0.10001 > (1 row) > > regression=# select 0.1::float8; >float8 > - > 0.10001 > (1 row) > > We would get a whole lot more bug reports, not fewer, if that were > the default behavior. Isn't this a client rendering issue, rather than an on-the-wire encoding issue? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Floating point error
Maciek Sakrejda writes: > On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane wrote: >> Basically, the default behavior is tuned to the expectations of people >> who think that what they put in is what they should get back, ie we >> don't want the system doing this by default: >> >> regression=# set extra_float_digits = 3; >> SET >> regression=# select 0.1::float4; >> float4 >> - >> 0.10001 >> (1 row) >> >> regression=# select 0.1::float8; >> float8 >> - >> 0.10001 >> (1 row) >> >> We would get a whole lot more bug reports, not fewer, if that were >> the default behavior. > Isn't this a client rendering issue, rather than an on-the-wire encoding > issue? Nope, at least not unless you ask for binary output format (which introduces a whole different set of portability gotchas, so it's not the default either). 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
Re: [HACKERS] [GENERAL] Floating point error
This conversation has moved beyond my ability to be useful but I want to remind everyone of my original issues in case it helps you improve the docs: 1) Data shown in psql did not match data retrieved by JDBC. I had to debug pretty deep into the JDBC code to confirm that a value I was staring at in psql was different in JDBC. Pretty weird, but I figured it had something to do with floating point malarky. 2) The problem in #1 could not be reproduced when running on our test database. Again very weird, because as far as psql was showing me the values in the two databases were identical. I used COPY to transfer some data from the production database to the test database. I now know that what you see in psql is not necessarily what you see in JDBC. I also know that you need to set extra_float_digits = 3 before using COPY to transfer data from one database to another or risk differences in floating point values. Sounds like both pg_dump and the JDBC driver must be doing this or its equivalent on their own. If the numeric types page of the documentation had mentioned the extra_float_digits then I might have been able to solve my own problem. I'd like you to add some mention of it even if it is just handwaving but will let you guys hash it out from here. Either way, PostgreSQL rocks! Tom On Mar 5, 2013, at 12:38 PM, Tom Lane wrote: > Maciek Sakrejda writes: >> On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane wrote: >>> Basically, the default behavior is tuned to the expectations of people >>> who think that what they put in is what they should get back, ie we >>> don't want the system doing this by default: >>> >>> regression=# set extra_float_digits = 3; >>> SET >>> regression=# select 0.1::float4; >>> float4 >>> - >>> 0.10001 >>> (1 row) >>> >>> regression=# select 0.1::float8; >>> float8 >>> - >>> 0.10001 >>> (1 row) >>> >>> We would get a whole lot more bug reports, not fewer, if that were >>> the default behavior. > >> Isn't this a client rendering issue, rather than an on-the-wire encoding >> issue? > > Nope, at least not unless you ask for binary output format (which > introduces a whole different set of portability gotchas, so it's > not the default either). > > regards, tom lane -- Tom Duffey tduf...@trillitech.com 414-751-0600 x102 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Floating point error
Em 05/03/2013 16:01, Tom Duffey escreveu: This conversation has moved beyond my ability to be useful but I want to remind everyone of my original issues in case it helps you improve the docs: 1) Data shown in psql did not match data retrieved by JDBC. I had to debug pretty deep into the JDBC code to confirm that a value I was staring at in psql was different in JDBC. Pretty weird, but I figured it had something to do with floating point malarky. As stated before, Java tries to be consistent across platforms. This is different than a dozen of C and C++ implementations you will find (some implementations will not be consistent even using same library on different platforms). 2) The problem in #1 could not be reproduced when running on our test database. Again very weird, because as far as psql was showing me the values in the two databases were identical. I used COPY to transfer some data from the production database to the test database. Yes, this is really interesting. Is the production database running on same platform (OS, architecture, updates) as the test database? Regards, Edson I now know that what you see in psql is not necessarily what you see in JDBC. I also know that you need to set extra_float_digits = 3 before using COPY to transfer data from one database to another or risk differences in floating point values. Sounds like both pg_dump and the JDBC driver must be doing this or its equivalent on their own. If the numeric types page of the documentation had mentioned the extra_float_digits then I might have been able to solve my own problem. I'd like you to add some mention of it even if it is just handwaving but will let you guys hash it out from here. Either way, PostgreSQL rocks! Tom On Mar 5, 2013, at 12:38 PM, Tom Lane wrote: Maciek Sakrejda writes: On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane wrote: Basically, the default behavior is tuned to the expectations of people who think that what they put in is what they should get back, ie we don't want the system doing this by default: regression=# set extra_float_digits = 3; SET regression=# select 0.1::float4; float4 - 0.10001 (1 row) regression=# select 0.1::float8; float8 - 0.10001 (1 row) We would get a whole lot more bug reports, not fewer, if that were the default behavior. Isn't this a client rendering issue, rather than an on-the-wire encoding issue? Nope, at least not unless you ask for binary output format (which introduces a whole different set of portability gotchas, so it's not the default either). regards, tom lane -- Tom Duffey tduf...@trillitech.com 414-751-0600 x102 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to get array of unique array values across rows?
I have a field containing a set of codes in a varchar array, each tied to a person. client_id | integer | service_codes | character varying(10)[] | I'm trying to query this info so that I can get the list (presumably in an array) of all the values in this array, across all the records for a client. So that if a person has two records, one with ORANGE and BLUE, and one with BLUE and GREEN, I could end up with a list of ORANGE, BLUE and GREEN. I had hopes for: SELECT client_id,array_agg(service_codes) FROM foo GROUP BY client_id; But was rebuffed with "ERROR: could not find array type for data type character varying[]" There's probably an easy answer for this, but it's completely escaping me. Any help appreciated. Thanks. Ken -- AGENCY Software A data system that puts you in control 100% Free Software *http://agency-software.org/* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list to learn more about AGENCY or follow the discussion.
Re: [GENERAL] How to get array of unique array values across rows?
Try this one. select X.client_id, array_agg(X.color) from (select distinct client_id, unnest(service_codes) as color from foo) X group by X.client_id; On Tue, Mar 5, 2013 at 3:28 PM, Ken Tanzer wrote: > I have a field containing a set of codes in a varchar array, each tied to > a person. > > client_id | integer > | > service_codes | character varying(10)[] | > > I'm trying to query this info so that I can get the list (presumably in an > array) of all the values in this array, across all the records for a > client. So that if a person has two records, one with ORANGE and BLUE, and > one with BLUE and GREEN, I could end up with a list of ORANGE, BLUE and > GREEN. > > I had hopes for: > > SELECT client_id,array_agg(service_codes) FROM foo GROUP BY client_id; > > But was rebuffed with "ERROR: could not find array type for data type > character varying[]" > > There's probably an easy answer for this, but it's completely escaping me. > Any help appreciated. Thanks. > > Ken > > -- > AGENCY Software > A data system that puts you in control > 100% Free Software > *http://agency-software.org/* > ken.tan...@agency-software.org > (253) 245-3801 > > Subscribe to the mailing > list > to > learn more about AGENCY or > follow the discussion. >
Re: [GENERAL] How to get array of unique array values across rows?
That worked perfectly. Thanks a lot! On Tue, Mar 5, 2013 at 12:49 PM, ChoonSoo Park wrote: > Try this one. > > select X.client_id, array_agg(X.color) > from (select distinct client_id, unnest(service_codes) as color >from foo) X > group by X.client_id; > > > On Tue, Mar 5, 2013 at 3:28 PM, Ken Tanzer wrote: > >> I have a field containing a set of codes in a varchar array, each tied to >> a person. >> >> client_id | integer >> | >> service_codes | character varying(10)[] | >> >> I'm trying to query this info so that I can get the list (presumably in >> an array) of all the values in this array, across all the records for a >> client. So that if a person has two records, one with ORANGE and BLUE, and >> one with BLUE and GREEN, I could end up with a list of ORANGE, BLUE and >> GREEN. >> >> I had hopes for: >> >> SELECT client_id,array_agg(service_codes) FROM foo GROUP BY client_id; >> >> But was rebuffed with "ERROR: could not find array type for data type >> character varying[]" >> >> There's probably an easy answer for this, but it's completely escaping >> me. Any help appreciated. Thanks. >> >> Ken >> >> -- >> AGENCY Software >> A data system that puts you in control >> 100% Free Software >> *http://agency-software.org/* >> ken.tan...@agency-software.org >> (253) 245-3801 >> >> Subscribe to the mailing >> list >> to >> learn more about AGENCY or >> follow the discussion. >> > > -- AGENCY Software A data system that puts you in control 100% Free Software *http://agency-software.org/* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list to learn more about AGENCY or follow the discussion.
[GENERAL] Postresql database library for Dart programming language
Hi all, I've been working on a Dart library for querying postgresql. If you're curious, have a go and let me know how it goes. https://github.com/xxgreg/postgresql Support for Heroku is coming soon. Cheers, Greg.
[GENERAL] PostgreSQL connect with Visual C++
Hello friends, I am new to postgres. I am doing Project on Image processing in OpenCV(IDE i am using is Visual C++ 2010). I have downloaded PostgreSQL 8.4 and installed it successfully. I want to know how to connect postgres with visual C++. Please do reply if you know anything. Because I have tried so many sites and got nothing. Thanks in advance. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-connect-with-Visual-C-tp5747463.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]
Mon, 04 Mar 2013 19:15:32 -0800 (PST) 04 Mar 2013 19:15:32 -0800 (PST) X-Newsgroups: pgsql.general Date: Mon, 4 Mar 2013 19:15:31 -0800 (PST) Complaints-To: groups-ab...@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=76.87.68.198; posting-account=96NFGAoAAABqgpEyKCN3YH2nEalcbJuu User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <2bc49454-76cf-450f-8b61-e0a8e4e33...@googlegroups.com> Subject: What query could I use to draw a support line on a stock chart? From: Nick Injection-Date: Tue, 05 Mar 2013 03:15:32 + Content-Type: text/plain; charset=ISO-8859-1 To: pgsql-general@postgresql.org Using this basic chart as an example, how could I write a query that would give me the lowest points of a trendline? chart = http://nboutelier.s3.amazonaws.com/images/support_line.png Here's the data INSERT INTO data (date,price) VALUES (0,3); INSERT INTO data (date,price) VALUES (1,4); INSERT INTO data (date,price) VALUES (2,2); INSERT INTO data (date,price) VALUES (3,7); INSERT INTO data (date,price) VALUES (4,3); INSERT INTO data (date,price) VALUES (5,8); INSERT INTO data (date,price) VALUES (6,3); INSERT INTO data (date,price) VALUES (7,6); INSERT INTO data (date,price) VALUES (8,4); INSERT INTO data (date,price) VALUES (9,9); INSERT INTO data (date,price) VALUES (10,4); INSERT INTO data (date,price) VALUES (11,6); The query would return the results 10 | 4 6 | 3 2 | 2 -- 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] Why does slony use a cursor? Anyone know?
On Tue, Mar 05, 2013 at 08:51:11AM -0600, Shaun Thomas wrote: > Hey everyone, > > Frankly, I'm shocked at what I just found. > > We did a delete last night of a few million rows, and come back this > morning to find that slony is 9-hours behind. After some > investigation, it became apparent that slony opens up a cursor and > orders it by the log_actionseq column. Then it fetches 500 rows, and > closes the cursor. So it's fetching several million rows into a > cursor, to fetch 500, and then throw the rest away. I once had a similar problem and it looked like they were missing an index. I asked about it and it turns out that I was running a somewhat old version, and it was fixed in later versions. Check that first. But ask anyway, I've always found the Slony guys very helpful. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL connect with Visual C++
On 3/4/2013 11:17 PM, dhaval257 wrote: Hello friends, I am new to postgres. I am doing Project on Image processing in OpenCV(IDE i am using is Visual C++ 2010). I have downloaded PostgreSQL 8.4 and installed it successfully. I want to know how to connect postgres with visual C++. You don't mention if you are using ODBC or a direction connection to the pgsql library. I'll assume the latter. I did a Google search for "postgresql visual c" and found this example: http://www.askyb.com/cpp/c-postgresql-example/ If you are just starting with PostgreSQL, you might as well start with the latest version. You can get 9.2.3 from the web site (http://www.postgresql.org/). -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
