[SQL] cool code_swarm animation of PostgreSQL development since 1996

This is a very cool animation for your amusement, amazement and edification. http://www.vimeo.com/1081680 TJ O'Donnell http://www.gnova.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How I can check a substring is a valid number in postgresql ?

This regular expression works for numeric/float values as well as integers. It allows for exponents. tvalue ~ E'^[+-]?[0-9]+(.[0-9]*)?([Ee][+-]?[0-9]+)?\$' TJ O'Donnell http://www.gnova.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription

Re: [SQL] Deploying PostgreSQL on virtualized hardware

I've been running PostgreSQL on a virtual server for several years now. I'm using VMWare with a Windows host and Linux guest. I've configured it to let Linux use a raw partition as a disk. Before I used a separate partition, the virtual disk had been a Windows file. Using the disk partition

Re: [SQL] Create Table xtest (like xtype)

Considering how related Created Types and Tables are (at least conceptually) it seems like a good idea to allow Created Types to be used in the Like clause. At least it would help me and make maintaining my db easier and cleaner. TJ O'Donnell http://www.gnova.com/ TJ O'Donnell [EMAIL PROTECTED

Re: [SQL] polymorphic functions and domains

the scheme for casting literals already prone to surprises? TJ Tom Lane wrote: TJ O'Donnell [EMAIL PROTECTED] writes: I really want two polymorphic functions, one taking a domain data type using varchar and one bytea. These aren't polymorphic functions, actually; they're just overloaded

[SQL] polymorphic functions and domains

('abc') to use the qtest(astring) function without having to explicitly cast 'abc'::astring; I'm using 8.2.5 and Linux.k Thanks, TJ O'Donnell www.gnova.com -- Drop Function ztest(Character Varying); Drop Function ztest(bytea); Drop Function qtest(astring

[SQL] seg data type

I had some questions about the contributed seg data type. My email to [EMAIL PROTECTED], the author, keeps bouncing. I'm guessing he's not at Argonne anymore. Is there someone responsible for the seg data type code? Anyone use it much and care to discuss it with me? Thanks, TJ O'Donnell http

Re: [SQL] unique rows

a perl hash to ensure uniqueness. As a side benefit, I was able to count the frequency of each input string while I was filtering and include that in the final table. TJ O'Donnell Markus Schaber wrote: Hi, TJ, TJ O'Donnell wrote: So, is there a way (a constraint, a check?) that will simply

[SQL] ERROR: could not write block 196261 of temporary file: No space left

I got the following error during a psql session of a big transaction filling a temporary table, then selecting distinct values from it for insertion into a permanent table. CREATE TABLE CREATE TABLE ERROR: could not write block 196261 of temporary file: No space left on device HINT: Perhaps

[SQL] unique rows

constraint above? Thanks, TJ O'Donnell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[SQL] sql error creating function

into fragset Values ('COCNCc1c1'); Select smiles from fragset; it works fine. What is wrong in the function definition? I'm using 8.1.3 Thanks, TJ O'Donnell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Bitfields always atomic? Other way to store attributes?

If I have two threads modifying the same bit field: thread1= update table set bf=bf | '01000' thread2= update table set bf=bf | '1' Will this operation always be safe (e.g. result in bf='11000')? Or Won't this always result in bf='11xxx', depending on the original values of bf?

[SQL] dynamically loaded functions

I've written some c-functions which depend on my code (gnova.so) as well as a third-party library of functions (oe_chem.so). Up until now, I've been preloading (in postgresql.conf) both .so's and it all works fine. To make it easier for my users to install my stuff, I'd like to avoid the

[SQL] several questions about R-tree index

According to the manual at: http://www.postgresql.org/docs/7.4/static/functions-geometry.html The PostgreSQL query planner will consider using an R-tree index whenever an indexed column is involved in a comparison using one of these operators: , , , , @, ~=, (Refer to Section 9.9 about the

[SQL]

There some documentation about aggregate functions in the manual, for example: http://www.postgresql.org/docs/7.4/static/sql-createaggregate.html Here's a simple agg function that should work for you, assuming your col types are int4. CREATE AGGREGATE andsum ( sfunc = int4and, basetype = int4,

Re: [SQL] 9.17.5. Row-wise Comparison

not necessarily in the same order. Thanks for the info, and for getting my meaning in spite of the typos in my sql. TJ Tom Lane wrote: TJ O'Donnell [EMAIL PROTECTED] writes: I've been using syntax like select a from tbl where (b,c,d) (1,2,3) to mean select a from t where b1 and b2 and d3 But I see

[SQL] 9.17.5. Row-wise Comparison

In tbl with columns a,b,c,d. I've been using syntax like select a from tbl where (b,c,d) (1,2,3) to mean select a from t where b1 and b2 and d3 But I see in the manual at: http://www.postgresql.org/docs/7.4/interactive/functions-comparisons.html#AEN12735 that only = and operators are

[SQL] SQL error: function round(double precision, integer) does not exist

I received the following error when executing a SQL statement: SQL error: ERROR: function round(double precision, integer) does not exist In statement: select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count, round((parameter*oe_count_matches(smiles,smarts)),2) as

Re: [SQL] SQL error: function round(double precision, integer) does

Thanks everyone. Your tips about casting my arg to round() as ::numeric worked just fine. I guess I was surprised that plpgsql didn't that on it's own! I'm used to too many forgiving c compilers, and such. TJ Christoph Haller wrote: TJ O'Donnell wrote: I received the following error when

Re: [SQL] SQL error: function round(double precision, integer) does

, Feb 27, 2005 at 03:26:07PM -0800, TJ O'Donnell wrote: ERROR: function round(double precision, integer) does not exist [snip] The functions described at: http://www.postgresql.org/docs/7.4/static/functions-math.html show that round(numeric,int) should

Re: [SQL] testing/predicting optimization using indexes

I was puzzled as to why my search slowed down when I added columns. The VACUUM did not restore the former speed, which I had obtained before adding the columns. So, I rebuilt the table with only the smiles column and my original speed was again obtained (not surprising). After I added the extra

[SQL] testing/predicting optimization using indexes

I have several questions reagaring the kind of increase in speed I can expect when I use a multi-column index. Here's what I've done so far. I've written some search functions which operate on character varying data used to represent molecular structures. We call this a Smiles string. I want to

[SQL] undigested email

Hello, According to my profile, I am subscribed for daily ~digest~. Yet, I recieve multiple ~individual~ emails every day. Am I doing something wrong? TJ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Function in C++

All I do is write a c interface with postgresql, using the standard method described in the documentation. There are example in the src code tree, too. The only trick is to declare my c++ functions to be callable by c, like in this example: extern C int oe_smarts_match(char *smi, char *sma) {