[SQL] text+number, find largest entry
hi, i have a table, where there is a varchar(500) column, which contains data that is strangely formatted: it starts with letters, and ends with a number. for example: xyz001 xyz002 xyz044 xyz1243 abc01 abc993 abc2342 and so on. now, for a given text-prefix (for example "xyz"), i need to find the record with the largest "numeric component". so for example, for the text-prefix "xyz", the corresponding entry would be "xyz1243". this lookup does not have to be especially fast. i realize that i could add some additional columns to this table, and store the text-part and the numeric-part separately, but first i would prefer a non-alter-table solution :) currently my only idea is to find the longest entry, check how many of them are, and then find the ones whose numeric part starts with "9" etc... ugly, but should work. are there any better ways to do it? thanks, gabor ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] VIEW on lookup table
Hello all, I have a lookup table consisting of 100,000+ rows. 99% of the lookup values resolve to 'Unknown'. Building the lookup table takes a long time. I would like to remove the 'Unknown' entries from the table and provide a VIEW to emulate them. The VIEW would need to provide all 100,000+ rows by using the reduced lookup data and generating the remaining values on the fly. The lookup table structure: CREATE TABLE lookup_data ( id1 INTEGER, id2 INTEGER, name TEXT, PRIMARY KEY (id1, id2) ); id1 is an INTEGER; from 0 through to 50,000+ id2 is an INTEGER; either 9 or 16. Example data: INSERT INTO lookup_data (id1, id2, name) VALUES (1, 9, 'a'); INSERT INTO lookup_data (id1, id2, name) VALUES (1, 16, 'b'); INSERT INTO lookup_data (id1, id2, name) VALUES (2, 9, 'c'); INSERT INTO lookup_data (id1, id2, name) VALUES (2, 16, 'd'); INSERT INTO lookup_data (id1, id2, name) VALUES (3, 9, 'e'); INSERT INTO lookup_data (id1, id2, name) VALUES (3, 16, 'f'); INSERT INTO lookup_data (id1, id2, name) VALUES (4, 9, 'g'); INSERT INTO lookup_data (id1, id2, name) VALUES (4, 16, 'h'); INSERT INTO lookup_data (id1, id2, name) VALUES (8, 9, 'i'); INSERT INTO lookup_data (id1, id2, name) VALUES (8, 16, 'j'); INSERT INTO lookup_data (id1, id2, name) VALUES (10, 9, 'k'); INSERT INTO lookup_data (id1, id2, name) VALUES (10, 16, 'l'); .. In the example data, entries where id1 is 5,6,7,9 are 'Unknown'; The VIEW would return: id1, id2, name 1, 9, 'a' 1, 16, 'b' 2, 9, 'c' 2, 16, 'd' 3, 9, 'e' 3, 16, 'f' 4, 9, 'g' 4, 16, 'h' 5, 9, 'Unknown' 5, 16, 'Unknown' 6, 9, 'Unknown' 6, 16, 'Unknown' 7, 9, 'Unknown' 7, 16, 'Unknown' 8, 9, 'i' 8, 16, 'j' 9, 9, 'Unknown' 9, 16, 'Unknown' 10, 9, 'k' 10, 16, 'l' I am using Postgres 7.2.1, which prevents me using a function to return a result set. Can I achieve this in pure SQL? Many thanks, JJ Gabor. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] VIEW on lookup table
> Yes. If you create a table with all of the values, 1 to 100,000+, and then > join that with lookup_data, using a "left outer join", and then use a > case statement for the value -- when NULL, 'Unknown', then it should > work. This would still require constructing a large table, which is what I want to avoid. > I would look at bending the requirements a bit before I do this. Why do > you want the string "Unknown" and not NULL? What is this table going to > be used for? Also, just because you can't write a function in the > database to do this doesn't mean you can't write a function in perl or > python outside of the database to do it. The technology used to access the database does not cope very well with NULL/missing rows/colunns :/ As it turns out, the lookup table has been ditched. > Also, seriously consider upgrading to 7.4.1. 7.2 is ancient and really > shouldn't be used anymore. Mutch as I would like to, this is not an option. Thanks for your help, JJ On Fri, Mar 05, 2004 at 08:39:12AM -0800, Jonathan M. Gardner wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Friday 27 February 2004 2:18 pm, JJ Gabor wrote: > > Hello all, > > > > I have a lookup table consisting of 100,000+ rows. > > > > 99% of the lookup values resolve to 'Unknown'. > > > > Building the lookup table takes a long time. > > > > I would like to remove the 'Unknown' entries from the > > table and provide a VIEW to emulate them. > > > > The VIEW would need to provide all 100,000+ rows by > > using the reduced lookup data and generating the > > remaining values on the fly. > > > > The lookup table structure: > > > > CREATE TABLE lookup_data ( > > > > id1 INTEGER, > > id2 INTEGER, > > name TEXT, > > > > PRIMARY KEY (id1, id2) > > ); > > > > id1 is an INTEGER; from 0 through to 50,000+ > > id2 is an INTEGER; either 9 or 16. > > > > Example data: > > > > INSERT INTO lookup_data (id1, id2, name) VALUES (1, 9, 'a'); > > INSERT INTO lookup_data (id1, id2, name) VALUES (1, 16, 'b'); > > INSERT INTO lookup_data (id1, id2, name) VALUES (2, 9, 'c'); > > INSERT INTO lookup_data (id1, id2, name) VALUES (2, 16, 'd'); > > INSERT INTO lookup_data (id1, id2, name) VALUES (3, 9, 'e'); > > INSERT INTO lookup_data (id1, id2, name) VALUES (3, 16, 'f'); > > INSERT INTO lookup_data (id1, id2, name) VALUES (4, 9, 'g'); > > INSERT INTO lookup_data (id1, id2, name) VALUES (4, 16, 'h'); > > INSERT INTO lookup_data (id1, id2, name) VALUES (8, 9, 'i'); > > INSERT INTO lookup_data (id1, id2, name) VALUES (8, 16, 'j'); > > INSERT INTO lookup_data (id1, id2, name) VALUES (10, 9, 'k'); > > INSERT INTO lookup_data (id1, id2, name) VALUES (10, 16, 'l'); > > .. > > > > In the example data, entries where id1 is 5,6,7,9 are 'Unknown'; > > > > The VIEW would return: > > > > id1, id2, name > > 1, 9, 'a' > > 1, 16, 'b' > > 2, 9, 'c' > > 2, 16, 'd' > > 3, 9, 'e' > > 3, 16, 'f' > > 4, 9, 'g' > > 4, 16, 'h' > > 5, 9, 'Unknown' > > 5, 16, 'Unknown' > > 6, 9, 'Unknown' > > 6, 16, 'Unknown' > > 7, 9, 'Unknown' > > 7, 16, 'Unknown' > > 8, 9, 'i' > > 8, 16, 'j' > > 9, 9, 'Unknown' > > 9, 16, 'Unknown' > > 10, 9, 'k' > > 10, 16, 'l' > > > > I am using Postgres 7.2.1, which prevents me using a > > function to return a result set. > > > > Can I achieve this in pure SQL? > > Yes. If you create a table with all of the values, 1 to 100,000+, and then > join that with lookup_data, using a "left outer join", and then use a > case statement for the value -- when NULL, 'Unknown', then it should > work. > > I would look at bending the requirements a bit before I do this. Why do > you want the string "Unknown" and not NULL? What is this table going to > be used for? Also, just because you can't write a function in the > database to do this doesn't mean you can't write a function in perl or > python outside of the database to do it. > > Also, seriously consider upgrading to 7.4.1. 7.2 is ancient and really > shouldn't be used anymore. > > - -- > Jonathan Gardner > [EMAIL PROTECTED] > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.2.3 (GNU/Linux) > > iD8DBQFASK0wqp6r/MVGlwwRAub2AKCUcqvFvkD1KjXLEeg8osybgw5kqwCgiq8W > YiJY3ZYsAXNfjjBTCF0vGKE= > =5EIl > -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Constraint->function dependency and dump in 7.3
Hi! Why don't you use pg_restore. You can set the order of restoring with parameters. (I haven't tried) By, Gabor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Együd Csaba Sent: Friday, July 09, 2004 7:15 AM To: 'SZŰCS Gábor'; [EMAIL PROTECTED] Subject: Re: [SQL] Constraint->function dependency and dump in 7.3 Hi Gábor! I had the same problem and someone pointed me the right direction. I tried to define a table default clause refering a function. Reloading the dump file an error messaged raised up saying that the referred function doesn't exist. This is because dumping out the schema pg_dump pushes out the table definitions first and then the functions (I don't know why can not realize these issues.). You can keep the schema dump in a separete file and move the referred functions in front of the tble definitions. After that regulary dump out only the data. Restoing the db start with the schema file. I hope I was clear. Another advance of this method is that it is absolutely Y3K safe. :) Bye, -- Csaba Együd > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of SZŰCS Gábor > Sent: 2004. július 8. 13:10 > To: [EMAIL PROTECTED] > Subject: [SQL] Constraint->function dependency and dump in 7.3 > > > Dear Gurus, > > Just recently realized that our daily dump from a 7.3 > (production) db to a > 7.4 (development) server has problems. I have no idea where > to search for an > answer so please feel free to point me to the appropriate > thread, doc or TFM > :) > > Below is two portions of the dump, which seems to be in the > wrong order (at > least for me). I'm not sure, and not in a position to easily > test it, that > it's wrong order in 7.3; but 7.4 has problems creating the > table without the > function (which is logical): > > %--- cut here ---% > CREATE TABLE cim ( > -- etc etc ... > orszag_kod integer, > CONSTRAINT cim_orszag_kod CHECK ((hely_fajta(orszag_kod) = 7)) > ); > > -- ... several lines later: > > CREATE FUNCTION hely_fajta (integer) RETURNS integer > AS ' ... ' > LANGUAGE sql; > %--- cut here ---% > > Checked pg_depend, and constraint cim_orszag_kod refers to function > hely_fajta, but noone (not even the table) refers to the > constraint. I'm > just wondering if it's ok... > > 1) is it normal that the table does not refer to its constraints? > 2) if not, do you have the idea of the possible cause? > 3) if so, is it normal for pg_dump to dump in this order? > 4) if so, how may I change it? > 5) may inserting into pg_depend solve the problem? > > TIA, > G. > %--- cut here ---% > \end > > > ---(end of > broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27. > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How do I convice postgres to use an index?
re-checked; it's WITHOUT in both version, but it's irrelevant if you give the full spec. Well, then maybe it was a difference between 7.2 and 7.3, but again, it's irrelevant in your case. Have you tried the typecast? G. %--- cut here ---% \end - Original Message - From: "Achilleus Mantzios" <[EMAIL PROTECTED]> Sent: Thursday, July 15, 2004 4:00 PM > > Also, I'd try to avoid naming attributes like (built-in) types. (iirc > > "timestamp" is a type without time zone in 7.3, and with time zone in 7.4 :) > ^^ > > Are you sure about it?? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
