[SQL] Re: Efficiently determining the number of bits set in the contents of, a VARBIT field

I use a c function, nbits_set that will do what you need. I've posted the code in this email. TJ O'Donnell http://www.gnova.com #include "postgres.h" #include "utils/varbit.h" Datum nbits_set(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(nbits_set); Datum nbits_set(P

[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 yo

Re: [SQL] Desc Commnad in pgsql?

commands in client programs? I am not concerned whether these are compatible with other RDBMS. TJ O'Donnell http://www.gnova.com If you're looking for ways to "describe" a table, there are two mechanisms: 1. SQL standard (probably SQL:1993) describes an "informati

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 in

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

[SQL] Create Table xtest (like xtype)

Integer, b Text); Create Table xtest (Like xtype); ERROR: "xtype" is a composite type Is that possible some other way? TJ O'Donnell http://www.gnova.com ---(end of broadcast)--- TIP 4: Have you searched our list archives?

Re: [SQL] polymorphic functions and domains

#x27;t 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, act

[SQL] polymorphic functions and domains

ne help me get qtest('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 F

[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&#x

Re: [SQL] unique rows

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 w

[SQL] unique rows

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

[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 ou

[SQL] sql error creating function

nsert into fragset Values ('COCNC'); Insert 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 o

[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 preloadi

[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 abo

[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

be the same according to spec, just 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)

[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 b>1 and b>2 and d>3 But I see in the manual at: http://www.postgresql.org/docs/7.4/interactive/functions-comparisons.html#AEN12735 that only = and <> operators are supp

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

te: On Sun, 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(nume

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

[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 psa,tpsa(smil

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 col

[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 op

[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) {