[SQL] How do I run a search on array
Hi,
I've been trying to do:
select col1 from table while array_col[1][1:4]='2';
how do I do this sort of thing? There seems to be no docs ;-(
my array is {{"1","2","4","2"},{"3","2","5"},{"6","3","7","9"}}
I would also like to know that if I have an array as a large object, is it
possible to do a search on it using rows and columns (or by any other
way)?
thanks,
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] select
Hi, AFAIK, you can't enter a null value in a bool field, it has to be 1 or 0. \Indraneel On Sat, 23 Sep 2000, Jeff MacDonald wrote: > how would i select all rows where a boolean value is neither > t nor f.. ? > > ie if someone inserted without setting the boolean tag. > > Jeff MacDonald, > /****. # 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] Select between two databases
Hi, Does any one know how I may select between two databases running on same machine or on different machines eg. select colA1 from tableA1 where colA2 in (select colB1 from tableB1); here tableA1 and tableB1 are in different databases. The two databases might be under the same postmaster or on different machines under different postmasters. How do I query both simultaneously? Is it possible? Thanks, 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] Select between two databases
Not working, but thanks anyway. Can anyone point out the relevant area of the source where queries are handled? Thanks, Indraneel On Sun, 24 Sep 2000, Fredrick Bartlett wrote: > Not sure if same can be accomplished with different servers. > Try the following for database's on one server, use alias & dot notation... > > select a1.colA1 from > myDatabase1.tableA1 a1 > where a1.A2 in (select b1.colB1 from myDatabase2.tableB1 b1) > /****. # 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] 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 # `/
Re: [SQL] dynamic object creation
Thanks, this is what I'm currently using and want to change from. This table is taking a long time to insert data into and to extract. My smallest table of this sort has 68000 rows. In comparison arrays are 10 times faster but lack the required tools to work with (especially for multidimensional arrays, even after I have extended the operators in the contrib section). Also I cannot put field names for individual columns of an array. \Indraneel On Thu, 12 Oct 2000, Mark Volpe wrote: > 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 ¡ E-mail: [EMAIL PROTECTED] # # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # # Centre for DNA Fingerprinting and Diagnostics, # # Hyderabad, India - 500076 # `/
Re: [SQL] dynamic object creation
The example is below: On Thu, 12 Oct 2000, Jean-Christophe Boggio wrote: > Hi Indraneel, > > IM> I'm converting a complex flatfile where records are arranged serially. > IM> some fields are as 'n' times repeating blocks of multiple lines. Some > IM> subfields within these are also 'n' time repeating blocks of multiple > IM> lines. So in my main table I do not know (until at run time) how many > IM> fields to create (same for any sub tables). How can I do this dynamically? > > IM> I tried using arrays, but retrieval from that is causing some problems. I > IM> have already checked the array utilities in the contrib section and have > IM> extended the operator list for other types (I'll send the file to it's > IM> original author so that he may include it if he wishes). > > IM> I think there must be some object-oriented way of doing this without > IM> creating too many keys. or are keys the only and best method? Using this > IM> is causing a performance hit. If it's any help, what I'm trying to convert > IM> are biological databases distributed in 'SRS' flatfile format from > IM> ftp.ebi.ac.uk/pub/databases/ > > Could you be more precise, give an example of 2 "variable-length" > records ? -example--- ID ACP_DOMAIN; MATRIX. AC PS50075; DT NOV-1997 (CREATED); NOV-1997 (DATA UPDATE); JUL-1998 (INFO UPDATE). DE Acyl carrier protein phosphopantetheine domain profile. MA /GENERAL_SPEC: ALPHABET='ABCDEFGHIKLMNPQRSTVWYZ'; LENGTH=71; MA /DISJOINT: DEFINITION=PROTECT; N1=6; N2=66; MA /NORMALIZATION: MODE=1; FUNCTION=LINEAR; R1=2.3; R2=.02281121; TEXT='NScore'; MA /CUT_OFF: LEVEL=0; SCORE=271; N_SCORE=8.5; MODE=1; MA /CUT_OFF: LEVEL=-1; SCORE=184; N_SCORE=6.5; MODE=1; MA /DEFAULT: D=-20; I=-20; B1=-80; E1=-80; MI=-105; MD=-105; IM=-105; DM=-105; MM=1; M0=-1; NR /RELEASE=38,8; NR /TOTAL=173(116); /POSITIVE=173(116); /UNKNOWN=0(0); /FALSE_POS=0(0); NR /FALSE_NEG=0; /PARTIAL=3; CC /TAXO-RANGE=??EP?; /MAX-REPEAT=5; end--- here the row starting with /CUT_OFF may be present 'ni' times and the values for N_SCORE may be present 'nj' times (both 'ni' and 'nj' are unknown till at run time). Similiarly for /NORMALIZATION (which may be present multiple times) and Rx (R1, R2, etc). Also for /DEFAULT line (which may occur more than 1 time) and the values it contains. I can use an array, but retrieving the data will be a problem. One method might be to define my own types but then in the end how do I face the problem that number of entries are variable? Again another array? Or do I have to use a lot of tables with indexes (which will also be bulky and slow)? thanks, Indraneel > -- > Jean-Christophe Boggio > [EMAIL PROTECTED] > Independant Consultant and Developer > Delphi, Linux, Oracle, Perl > > /. # 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] many-many mapping between unique tables
Hi, I am facing a problem in mapping between two tables containing unique entries T1 T2 _ | x1 | | y1 | | x2 | | y2 | | x3 | | y3 | - - x(i) points to 1 or more entries in T2. y(i) points to one or more entries in T1. How do I store this mapping? I'd cannot use an array due to size restrictions and inability to extract data easily for furthur processing. I don't want to put everything into a single table and repeat values in the 2nd column since these are really huge tables and I cannot increase overhead by increasing data redundancy. Is there some way to do something about this? \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: Antw: [SQL] many-many mapping between unique tables
Yeah you understand the problem perfectly. The third table will definitely contain unique entries if you take both x and y simultaneously. I wanted some way of mapping where I would not need to repeat individual values of x and y. I am not from a maths background and have absolutely no idea of database design. Is it possible to map the columns without repeating values? \Indraneel On Mon, 16 Oct 2000, Gerhard Dieringer wrote: > I'm not sure if I understand your question. Is it right, that the x(i) are different >values > of an column x in table T1 and the y(i) are different values of an column y in table >T2? > And you want to have a n:m relation between the two tables? > Then you need a third table T12 with columns x and y, that contains one row for each > mapping of a x(i) with a y(j). > > I hope this helps. > > > Gerhard /********. # 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: Antw: [SQL] many-many mapping between unique tables
Thanks, I too came to the same conclusion (due to lack of persistence/patience). Thanks, Indraneel On Mon, 16 Oct 2000, Gerhard Dieringer wrote: > I don't know what type of data your columns x and y contain. If they are, > for example, of type text and contain long strings, then it would be better > to add an integer id column in each tables T1 and T2: x_id and y_id and > use these ids in the table T12. > > Then you only have to repeat the (short) numeric vales in T12. But I think there > is no way to totaly avoid any repetition. > > -- > Gerhard > /****. # 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: Antw: [SQL] many-many mapping between unique tables
Sorry for splitting mails. Is there some way of using the oid of each row to do the mapping instead of creating two more columns of integers? The third table can then be like this: create table T3( T1 oid, T2 oid ); also how can I find out the oid of a particular row? and how can I use this oid in a select statement? Thanks, Indraneel On Mon, 16 Oct 2000, Gerhard Dieringer wrote: > I don't know what type of data your columns x and y contain. If they are, > for example, of type text and contain long strings, then it would be better > to add an integer id column in each tables T1 and T2: x_id and y_id and > use these ids in the table T12. > > Then you only have to repeat the (short) numeric vales in T12. But I think there > is no way to totaly avoid any repetition. > > -- > Gerhard > /****. # 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: Antw: [SQL] many-many mapping between unique tables
Hi, Thank you very much. Now I know to use serial and not oid. I hope I got the correct message. Thanks, Indraneel On Mon, 16 Oct 2000, Josh Berkus wrote: > Please reference two things: > > 1. Look in the list archives for the last 4 weeks and you will read a > long discussion on the risks/limitations of using an OID as the primary > key on very large tables. (Subject: Object-Relational Database Design). > > 2. For how to use OIDs, please look in Bruce Momjian's excellent PGSQL > book-in-progress, available on the Postgresql.org website. (Chapter 7, I > believe). > /****. # 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] Connecting to Postgres from Windows
check your pg_hba.conf file for correct permissions. you need to have host based authenticationenabled for the network/hosts from where you are trying to connect. HTH \Indraneel On Fri, 3 Nov 2000, Umashankar Kotturu wrote: > Hi > > I am connecting to postgres in a java program running on windows 98. > > a) I am running the postgres on linux with -i option > b) I am using the url as "jdbc:postgresql://:5432/testdb" > > The program is unable to resolve the url? The same thing happens when > executing the java code on the linux machine (same as that running > postgres). However if I put 127.0.0.1 (or localhost) instead of > , it resolves and works fine. > > Any suggestions on what could be the problem ? > > Regards > Uma. > /********. # 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] Conditional SQL query
Hi, how can I insert a record after checking whether one of the fields has a certain value or not? eg: if table(col1)='value' insert into table values (x,x,x) I am using perl interface. Is there any way to define a function to take table name as an argument? (eg: select * from $table) I tried but this gave an error (so I'm using perl to pass the real tablename for each query) Any help will be great, 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] Conditional SQL query
On Sun, 5 Nov 2000, Marten Feldtmann wrote: > Indraneel Majumdar schrieb: > > > > Hi, > > > > how can I insert a record after checking whether one of the fields has a > > certain value or not? eg: > > > > if table(col1)='value' insert into table values (x,x,x) > > > > I am using perl interface. Is there any way to define a function to take > > table name as an argument? (eg: select * from $table) I tried but this > > gave an error (so I'm using perl to pass the real tablename for each > > query) > > > > insert into table select ... where col1 = 'value' > this is working but I am inserting values directly and not by select. eg: insert into table values(x,x,x) and not insert into table select * from table2 the former is not working. \Indraneel > > MF > /. # 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] Conditional SQL query
I finally got it to work. you can't use the parentheses. eg: insert into table select x,x,x where col1 = 'value'; and not insert into table select (x,x,x) where col1 = 'value'; the latter gives a ERROR: parser: parse error at or near "where" I am using PostgreSQL-7.0.2 Now I have one more problem. How do I insert only once if value does not exist? eg: insert into table once select x,x,x where not col1='value' Thanks, Indraneel On Sun, 5 Nov 2000, Cristóvão B. B. Dalla Costa wrote: > > > insert into table select ... where col1 = 'value' > > > > > > > this is working but I am inserting values directly and not by select. eg: > > > > insert into table values(x,x,x) > > and not > > insert into table select * from table2 > > > > insert into table select (x, x, x) where col1 = 'value' > /. # 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] Conditional SQL query
OK, I solved my own problems (Congrats ;-) Sorry for bothering the list. This is what I'm doing: INSERT INTO table SELECT x,x,x where (select count(*) from table where col1='value')=0; any suggestions? thanks, Indraneel On Mon, 6 Nov 2000, Indraneel Majumdar wrote: > Now I have one more problem. How do I insert only once if value does not > exist? eg: > > insert into table once select x,x,x where not col1='value' > /****. # 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] reinitialise serial counter
Hi, I have a table create table test (id serial, name text); after inserting values and doing delete from test if I try to insert values again, the id starts from where it left off previously. How can I get the serial counter to restart from 1 ? is it anything to do with setval(). How to use the setval? thanks, 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] newbie: execute an sql script
at the psql prompt do this: \i On Sun, 19 Nov 2000 [EMAIL PROTECTED] wrote: > hi everyone, > just wanna ask how I could execute an sql script to feed into the > database? > > Jess Emerson Uy > [EMAIL PROTECTED] > /********. # Indraneel Majumdar ¡ E-mail: [EMAIL PROTECTED] # # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # # Centre for DNA Fingerprinting and Diagnostics, # # Hyderabad, India - 500076 # `/
