Re: [SQL] Isnumeric function?
Oliver, Theo: > ~ '^[0-9]+$' Actually, I usually do: ~ '^[0-9]+\.?[0-9]*$' ... to include decimals. However, the above assumes that there is at least a "0" before the decimal; it would be nice to adapt it to matching a leading decimal (i.e. .057 ) as well. Can't see any easy way, though ... -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to rename a constraint/trigger??
Andrei, > I have a database, where in the tables I have around 100 constrains (link > to other tables) that don't have a name "" or they have a name > like "$1" "$2". Now, I have a module which bases on the same structure, but > I get some query errors from a "" constraint. I really don't know > exaclty which the constrain generates the error, in that table I have 12 > such constrains. You'll be happy to know that in 7.4 we stopped using "". > Does anyone have a script or it exist a command so that I can rename all > the ""constrains in this database? You can't rename constraints, you have to drop and re-create them under a new name (do this in a transaction for data safety). However, this requires knowing what all those constraints do. I'd suggest doing a text pg_dump file of your schema only (no data) and looking in the constraints section at the end. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Isnumeric function?
On Wed, 2004-09-08 at 17:47, Josh Berkus wrote: > Oliver, Theo: > > > ~ '^[0-9]+$' > > Actually, I usually do: > > ~ '^[0-9]+\.?[0-9]*$' > > ... to include decimals. However, the above assumes that there is at least a > "0" before the decimal; it would be nice to adapt it to matching a leading > decimal (i.e. .057 ) as well. Can't see any easy way, though ... ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$' -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Put on the whole armor of God, that ye may be able to stand against the wiles of the devil." Ephesians 6:11 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Isnumeric function?
Theo, Oliver, > Any reason why you don't like ~ '^([0-9]?)+\.?[0-9]*$' ? Yes, because it also matches "." , which is not a valid numeric value. > ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$' Ah, the brute force approach ;-) Actually, the above could be written: ~ '^([0-9]+)|([0-9]*\\.[0-9]+)$' ... though that still seems inelegant to me. Is there a regex expert in the house? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Isnumeric function?
On Wed, 2004-09-08 at 18:48, Josh Berkus wrote: > Theo, Oliver, > > > Any reason why you don't like ~ '^([0-9]?)+\.?[0-9]*$' ? > > Yes, because it also matches "." , which is not a valid numeric value. > > > ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$' > > Ah, the brute force approach ;-) Nothing like using a nice big hammer! > Actually, the above could be written: > > ~ '^([0-9]+)|([0-9]*\\.[0-9]+)$' But that doesn't allow a trailing decimal point. > > ... though that still seems inelegant to me. Is there a regex expert in the > house? All the elegant approaches I can think of match the empty string. There must be at least one digit and 0 or 1 decimal point with no other characters permitted. If you use this as a constraint, you could make it elegant and combine it with another constraint to exclude '' and '.'. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Put on the whole armor of God, that ye may be able to stand against the wiles of the devil." Ephesians 6:11 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] aggregate function stddev
Implementers: Just noticed that the postgres stddev is the stddev_sample formula. There are two different ways to calculate this value. Their difference is very small with large samle size. It would be nice to distinguish the two different versions. I also noticed that oracle has stddev_sample and stddev_population. This is just a wish list. Kemin ** Proprietary or confidential information belonging to Ferring Holding SA or to one of its affiliated companies may be contained in the message. If you are not the addressee indicated in this message (or responsible for the delivery of the message to such person), please do not copy or deliver this message to anyone. In such case, please destroy this message and notify the sender by reply e-mail. Please advise the sender immediately if you or your employer do not consent to e-mail for messages of this kind. Opinions, conclusions and other information in this message represent the opinion of the sender and do not necessarily represent or reflect the views and opinions of Ferring. ** ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Isnumeric function?
Title: RE: [SQL] Isnumeric function? Thankyou all for your feedback. I actually only want to check for whole numbers, so the ~ '^[0-9]+$' _expression_ is good. The issue really is that our CMS system sometimes holds the value of primary keys within a "content" varchar column(don't ask!), which is a nightmare to search across. I tried applying an index across the "content" varchar column and it failed. error: btree item size 2744 exceeds maximum 2713. I assume I had to change some server settings to extend the maximum, however in the end this column holds content, and even applying an index would be incredible slow to search across hundred of thousands of "content" records looking for a primary key. So I came up with the following. A Insert/update trigger would call a procedure to check to see if the content is numeric(a whole number), if so would update an indexed integer column called (content_numeric). Which would be the base column to search appon. Here is the function anyway: CREATE OR REPLACE FUNCTION update_content_node() RETURNS trigger AS ' begin /* New function body */ IF NEW.content ~ \'^[0-9]+$\' THEN NEW.content_numeric := NEW.content; ELSE NEW.content_numeric := null; END IF; RETURN NEW; end; ' LANGUAGE 'plpgsql' IMMUTABLE; Does anyone have any better suggestions??? Theo __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
Re: [SQL] Isnumeric function?
Theo Galanakis <[EMAIL PROTECTED]> writes:
> So I came up with the following. A Insert/update trigger would call a
> procedure to check to see if the content is numeric(a whole number), if so
> would update an indexed integer column called (content_numeric). Which would
> be the base column to search appon.
> CREATE OR REPLACE FUNCTION update_content_node()
> RETURNS trigger AS
> '
> begin
> /* New function body */
> IF NEW.content ~ \'^[0-9]+$\' THEN
> NEW.content_numeric := NEW.content;
> ELSE
> NEW.content_numeric := null;
Hmm. Seems like you could get burnt by "content" that is by chance a
long string of digits --- you'd get an integer overflow error at the
attempt to assign to content_numeric. Can you make an assumption that
indexable keys are at most 9 digits? If so then
IF NEW.content ~ \'^[0-9]{1,9}$\' THEN
Or use a bigint column and crank up the number of digits appropriately.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Isnumeric function?
Title: RE: [SQL] Isnumeric function?
Thanks Tom,
Actually I did not attach the latest function, I did have a limit of 9 numerical characters, found that out when I applied the update to move all current numerical values to that column.
Theo
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 9 September 2004 9:57 AM
To: Theo Galanakis
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Isnumeric function?
Theo Galanakis <[EMAIL PROTECTED]> writes:
> So I came up with the following. A Insert/update trigger would call a
> procedure to check to see if the content is numeric(a whole number),
> if so would update an indexed integer column called (content_numeric).
> Which would be the base column to search appon.
> CREATE OR REPLACE FUNCTION update_content_node()
> RETURNS trigger AS
> '
> begin
> /* New function body */
> IF NEW.content ~ \'^[0-9]+$\' THEN
> NEW.content_numeric := NEW.content;
> ELSE
> NEW.content_numeric := null;
Hmm. Seems like you could get burnt by "content" that is by chance a long string of digits --- you'd get an integer overflow error at the attempt to assign to content_numeric. Can you make an assumption that indexable keys are at most 9 digits? If so then
IF NEW.content ~ \'^[0-9]{1,9}$\' THEN
Or use a bigint column and crank up the number of digits appropriately.
regards, tom lane
__This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
[SQL] check for circular references in a 2-table heirachy
Does anyone know how I could check to ensure circular references are no created in my 2 table heirachy structure? Here are the tables albums album_relations ++ +-+ | album_id | | parent_album_id | | title | | child_album_id | ++ +-+ This structure allows an album to have multiple parents. I have written a recursive function (on a PHP class) which creates an XML tree structure based on a 2D array result set**. It has a depth checker so you can set the limit of recursion. This is all well and good, but I want to create a PLPG/SQL trigger on the album_relations table to ensure that I won't have to throw the "depth limit exceeded" exception due to circular references. Any ideas would be appreciated. ** 2D array result set is built from SELECT a.*,r.parent_album_id FROM album_relations r RIGHT JOIN albums a ON a.album_id = r.child_album_id ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Isnumeric function?
Oliver Elphick wrote:
On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
Theo, Oliver,
Any reason why you don't like ~ '^([0-9]?)+\.?[0-9]*$' ?
Yes, because it also matches "." , which is not a valid numeric value.
~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
Ah, the brute force approach ;-)
Nothing like using a nice big hammer!
Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$" be a little cleaner?
Actually, the above could be written:
~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
But that doesn't allow a trailing decimal point.
... though that still seems inelegant to me. Is there a regex expert in the
house?
All the elegant approaches I can think of match the empty string. There
must be at least one digit and 0 or 1 decimal point with no other
characters permitted. If you use this as a constraint, you could make
it elegant and combine it with another constraint to exclude '' and '.'.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Isnumeric function?
Theo Galanakis <[EMAIL PROTECTED]> writes: > error: btree item size 2744 exceeds maximum 2713. > > I assume I had to change some server settings to extend the maximum, however I would guess the block size. But I'm just guessing. > in the end this column holds content, and even applying an index would be > incredible slow to search across hundred of thousands of "content" records > looking for a primary key. Perhaps you could have an indexed column that contains a crc32 hash? Then you could do searches by comparing crc32 which make for fast efficient integer index lookups. You should still include a comparison against the original content column since it is possible for there to be a rare crc32 collision. This doesn't let you do range lookups efficiently. But it does let you look up specific values quickly even when they aren't numeric. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Query Plan
Title: Query Plan Have a question regarding when a Query Plan uses an Index. I have a basic statement This query uses the Index Scan: explain analyse select * from tablea where columna_id < 57 This query uses Seq Scan: explain analyse select * from tablea where columna_id < 58 There are about 5000 records in this table, and columna_id is the primary key, which is Indexed... And recently recreated. My question is why is the Plan not using the index on the second occasions? Theo __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
