[SQL] dynamic object creation
Hi, I'm not sure if the subject line has been proper. I have this following problem which I hope PostgreSQL can handle. I'm converting a complex flatfile where records are arranged serially. some fields are as 'n' times repeating blocks of multiple lines. Some subfields within these are also 'n' time repeating blocks of multiple lines. So in my main table I do not know (until at run time) how many fields to create (same for any sub tables). How can I do this dynamically? I tried using arrays, but retrieval from that is causing some problems. I have already checked the array utilities in the contrib section and have extended the operator list for other types (I'll send the file to it's original author so that he may include it if he wishes). I think there must be some object-oriented way of doing this without creating too many keys. or are keys the only and best method? Using this is causing a performance hit. If it's any help, what I'm trying to convert are biological databases distributed in 'SRS' flatfile format from ftp.ebi.ac.uk/pub/databases/ Thank you, Indraneel /. # Indraneel Majumdar ¡ E-mail: [EMAIL PROTECTED] # # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # # Centre for DNA Fingerprinting and Diagnostics, # # Hyderabad, India - 500076 # `/
[SQL] set digest
set digest
[SQL] if else query help
Hello all, I need to write a query that will pull information from table2 if information in table1 is older then xdate. My laymen example: SELECT table2.date, count(table2.name) as count WHERE table1.startdate > 2 weeks AND table2.submitdate > 2 weeks ; So i Guess my real questions is how do I determine the age of an entry to another table?
Re: [SQL] dynamic object creation
You may want to think about creating your table like this (for example): CREATE TABLE data ( key text, field_type char, value text ); CREATE UNIQUE INDEX data_key ON data(key, field_type, value); So this way each "record" takes up several rows in the table, and each "field" can take up as many rows as you need. A table like this, with two columns being arrays: key | field1 | field2 - a| [x,y,z] | [a,d,f] b| [m,n] | (NULL) Can be represented like this instead: key | field_type | value - a| 1 | x a| 1 | y a| 1 | z a| 2 | a a| 2 | d a| 2 | f b| 1 | m b| 1 | n I'm not sure what your data looks like, but I hope this helps. Mark Indraneel Majumdar wrote: > > Hi, > > I'm not sure if the subject line has been proper. I have this following > problem which I hope PostgreSQL can handle. > > I'm converting a complex flatfile where records are arranged serially. > some fields are as 'n' times repeating blocks of multiple lines. Some > subfields within these are also 'n' time repeating blocks of multiple > lines. So in my main table I do not know (until at run time) how many > fields to create (same for any sub tables). How can I do this dynamically? > > I tried using arrays, but retrieval from that is causing some problems. I > have already checked the array utilities in the contrib section and have > extended the operator list for other types (I'll send the file to it's > original author so that he may include it if he wishes). > > I think there must be some object-oriented way of doing this without > creating too many keys. or are keys the only and best method? Using this > is causing a performance hit. If it's any help, what I'm trying to convert > are biological databases distributed in 'SRS' flatfile format from > ftp.ebi.ac.uk/pub/databases/ > > Thank you, > Indraneel > > /. > # Indraneel Majumdar ¡ E-mail: [EMAIL PROTECTED] # > # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # > # Centre for DNA Fingerprinting and Diagnostics, # > # Hyderabad, India - 500076 # > `/
Re: [SQL] if else query help
hi Brian, BCD> I need to write a query that will pull information from table2 if BCD> information in table1 is older then xdate. BCD> My laymen example: BCD> SELECT table2.date, count(table2.name) as count BCD> WHERE table1.startdate > 2 weeks BCD> AND table2.submitdate > 2 weeks BCD> ; BCD> So i Guess my real questions is how do I determine the age of an entry to BCD> another table? Try this : Select table2.date,count(table2.name) as count from table1 as t1, table2 as t2 where t1.itemid=t2.itemid -- to link the tables and t1.startdate>now()-14 and t2.submitdate>now()-14; Someone corrects me if I'm wrong, I come from the Oracle world... Dates (or I should say TimeStamps) are stored as floating point values : the integer part is the number of days since a certain date (epoch or 01/01/1970 on unix-based databases) and the fractionnal part is the portion of the day (although I don't know --yet-- how to convert date2-date1 to an integer, trunc does not work). HTH -- Jean-Christophe Boggio [EMAIL PROTECTED] Independant Consultant and Developer Delphi, Linux, Oracle, Perl
Re: [SQL] if else query help
Brian, Jean-Christophe, >Someone corrects me if I'm wrong, I come from the Oracle world... > > Dates (or I should say TimeStamps) are stored as floating point values > : the integer part is the number of days since a certain date > (epoch or 01/01/1970 on unix-based databases) and the fractionnal part is the > portion of the day (although I don't know --yet-- how to convert > date2-date1 to an integer, trunc does not work). You're doing this the hard way. One of Postgres' best features is its rich collection of date-manipulation functions. Please see: ... H. The online docs appear to be down. When they're back up, please check the sections on: Date/Time data types, and Date/Time manipulation functions. -Josh Berkus P.S. Brian, a general tutorial on writing SQL, such as O'Reilly's soon-to-be released SQL book, might help you a great deal. -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] if else query help
Hey, there, This is very interesting. I have similar problem: I want drop some junky table in my database, how can I detect a table when last time it is used. I try to say that I want to know how long this table has NOT been used at all. I don't which system table holds this statistics. Josh Berkus wrote: > Brian, Jean-Christophe, > > >Someone corrects me if I'm wrong, I come from the Oracle world... > > > > Dates (or I should say TimeStamps) are stored as floating point values > > : the integer part is the number of days since a certain date > > (epoch or 01/01/1970 on unix-based databases) and the fractionnal part is the > > portion of the day (although I don't know --yet-- how to convert > > date2-date1 to an integer, trunc does not work). > > You're doing this the hard way. One of Postgres' best features is its > rich collection of date-manipulation functions. Please see: > > ... H. The online docs appear to be down. When they're back up, > please check the sections on: Date/Time data types, and Date/Time > manipulation functions. > > -Josh Berkus > > P.S. Brian, a general tutorial on writing SQL, such as O'Reilly's > soon-to-be released SQL book, might help you a great deal. > > -- > __AGLIO DATABASE SOLUTIONS___ > Josh Berkus >Complete information technology [EMAIL PROTECTED] > and data management solutions (415) 436-9166 >for law firms, small businesses fax 436-0137 > and non-profit organizations. pager 338-4078 > San Francisco -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
[SQL] Standard syntax?
I have been using (with success) this SQL statement in PostgreSQL: select col1, case when col2 = true then col3 else col4 end as colw, colM where etc. The above syntax, however, does not work for Interbase (6.01). For those who have had experience in other SQL servers, is this a "standard" or ANSI 9X compatible syntax or should I refrain from such syntax if I want my statements to be transportable from SQL Server to SQL Server? Best regards, Franz Fortuny
Re: [SQL] Standard syntax?
Franz, You'd better stay away from that syntax if you want to make your applications portable. I can tell you that it won't work on MS SQL Server or MySQL. I can't speak for Oracle. -Josh Berkus P.S. Thanks for the nifty construction ... I wouldn't have thought of it! -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Standard syntax?
"Franz J Fortuny" <[EMAIL PROTECTED]> writes: > I have been using (with success) this SQL statement in PostgreSQL: > > select col1, > case when col2 = true then > col3 > else > col4 > end as colw, > colM > where etc. > > The above syntax, however, does not work for Interbase (6.01). For those who > have had experience in other SQL servers, is this a "standard" or ANSI 9X > compatible syntax or should I refrain from such syntax if I want my > statements to be transportable from SQL Server to SQL Server? It is not part of SQL92, and I don't think it's part of SQL-3 either. Many databases (PostgreSQL, MSSQL, Oracle, ...) have such things though, but the syntax is different between them. If you can do without them, that's what you should try to do. Tomas
Re: [SQL] Standard syntax?
Hi, This is not SQL92, I believe it's an extention of Pg , Oracle uses 'decode' to implement if-then elsif then-else structure. Franz J Fortuny wrote: > I have been using (with success) this SQL statement in PostgreSQL: > > select col1, > case when col2 = true then > col3 > else > col4 > end as colw, > colM > where etc. > > The above syntax, however, does not work for Interbase (6.01). For those who > have had experience in other SQL servers, is this a "standard" or ANSI 9X > compatible syntax or should I refrain from such syntax if I want my > statements to be transportable from SQL Server to SQL Server? > > Best regards, > > Franz Fortuny -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] if else query help
On Thu, 12 Oct 2000, Jean-Christophe Boggio wrote: > portion of the day (although I don't know --yet-- how to convert > date2-date1 to an integer, trunc does not work). reltime(date2-date1)::int Will subtract date1 from date2, then cast it to an integer. John
Re: [SQL] Standard syntax?
Franz J Fortuny writes: > select col1, > case when col2 = true then > col3 > else > col4 > end as colw, > colM > where etc. > is this a "standard" or ANSI 9X compatible syntax Yes. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Standard syntax?
"Franz J Fortuny" <[EMAIL PROTECTED]> writes: > I have been using (with success) this SQL statement in PostgreSQL: > select col1, > case when col2 = true then > col3 > else > col4 > end as colw, > colM > where etc. > The above syntax, however, does not work for Interbase (6.01). CASE expressions are specified in SQL92, but they're labeled as an "intermediate SQL" feature rather than an "entry SQL" feature. So I'm not surprised that some other DBMSes don't have them. Entry SQL is a pretty impoverished subset (no VARCHAR type, to take a random example), so nearly everyone implements at least some intermediate- and full-SQL features. But exactly which ones is highly variable. regards, tom lane
[SQL] xml perl & pgsql
Dear Postgresql Users Can any one tell me where I can find some XML to Postgresql routines written in Perl. Thanks in advance for your help and assistance. Regards Philip
