Re: [GENERAL] Drop/ Alter index if exist
thanks 2009/12/22 Filip Rembiałkowski plk.zu...@gmail.com 2009/12/22 Pau Marc Munoz Torres paum...@gmail.com Hi every body there is some way to delete or rename an index only if this index exists? something like alter index index rename to pepe if exists for drop - yes: DROP INDEX IF EXISTS name; for rename - there is no simple solution. well, actually there are solutions but they are more complicated than the problem. (think of user defined functions) -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : paumarc.mu...@bioinf.uab.cat
[GENERAL] Drop/ Alter index if exist
Hi every body there is some way to delete or rename an index only if this index exists? something like alter index index rename to pepe if exists thanks -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : paumarc.mu...@bioinf.uab.cat
Re: [GENERAL] drop index
psql 8.2.6 (server 8.2.5) Schema | Name | Type | Owner |Table ++---+---+-- public | h2dbp | index | pau | precalc public | icsp | index | pau | local public | icspn | index | pau | names public | idx| index | pau | local public | iid| index | pau | blancprecalc public | ipssms | index | pau | pssms public | merda1 | index | pau | public | merda2 | index | pau | public | merda3 | index | pau | (9 rows) i would like to drop index merda1 2 and 3, but i can't because i drop their table before drop the index what can I do? 2008/9/16 Richard Huxton [EMAIL PROTECTED] Pau Marc Munoz Torres wrote: Hi everybody I have a problem with index, i droped a table before drop their indexs, so, now i can't remove the index That shouldn't be possible. What version of PostgreSQL is this? -- Richard Huxton Archonet Ltd -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
[GENERAL] too many warnings
Hi everybody i get a lot of warnings when i try to execute a postgresql from a script the warrning is HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. WARNING: nonstandard use of escape in a string literal LINE 1: ...9.table.viewentry_general_old.txt' WITH DELIMITER '\ '; DROP... the problem is thet this script was done by anothey guy, so i cant change this. by the way it is possible to change somthing at postgresql.conf that allows use \ as scape? i found this variable standard_conforming_strings but i didn't find it at postgresql.conf pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] Problems with memory
Hi maybe i should give you some more explanations of my problem. The reason for which i think that postgresql run out of memory is that: I have a relation with 6 fields, 29 indexes and 32000 registers, the registers where made up using a pgsql language to save disk space, and they work (see the table schema under those lines) Column | Type | Modifiers +--+--- id | integer | p1 | character(1) | p4 | character(1) | p6 | character(1) | p7 | character(1) | p9 | character(1) | Indexes: h2iab btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)) h2iad btree (idr(p1, p4, p6, p7, p9, 'H-2*IAd'::character varying)) h2iak btree (idr(p1, p4, p6, p7, p9, 'H-2*IAk'::character varying)) h2ied btree (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character varying)) hladqa10501 btree (idr(p1, p4, p6, p7, p9, 'HLA-DQA1*0501'::character varying)) hladqb10201 btree (idr(p1, p4, p6, p7, p9, 'HLA-DQB1*0201'::character varying)) hladr btree (idr(p1, p4, p6, p7, p9, 'HLA-DR'::character varying)) hladr1 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR1'::character varying)) hladr13 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR13'::character varying)) hladr3 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR3'::character varying)) hladr7 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR7'::character varying)) hladrb10101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying)) hladrb10102 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0102'::character varying)) hladrb10301 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0301'::character varying)) hladrb10302 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0302'::character varying)) hladrb10401 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0401'::character varying)) hladrb10402 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0402'::character varying)) hladrb10701 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0701'::character varying)) hladrb10802 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0802'::character varying)) hladrb10901 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0901'::character varying)) hladrb11101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1101'::character varying)) hladrb11102 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1102'::character varying)) hladrb11103 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1103'::character varying)) hladrb11104 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1104'::character varying)) hladrb11301 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1301'::character varying)) hladrb11302 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1302'::character varying)) hladrb11501 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1501'::character varying)) hladrb40101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB4*0101'::character varying)) hladrb50101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101'::character varying)) when i do a query as: select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; it works and return 128030 registers if i do select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; 3071970 registers, it don't work ERROR: relation pssms does not exist CONTEXT: SQL statement select score from PSSMS where AA= $1 and POS=1 and MOLEC= $2 PL/pgSQL function idr line 11 at SQL statement if i ask for explanation for both queries works: mhc2db= explain select count(*) from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; QUERY PLAN Aggregate (cost=66188.88..66188.89 rows=1 width=0) - Bitmap Heap Scan on precalc (cost=17615.20..63522.21 rows=107 width=0) Recheck Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) -2::double precision) - Bitmap Index Scan on hladrb10101 (cost=0.00..17348.54 rows=107 width=0) Index Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) -2::double precision) (5 rows) mhc2db= explain select count(*) from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; QUERY PLAN Aggregate (cost=66188.88..66188.89 rows=1 width=0) - Bitmap Heap Scan on precalc (cost=17615.20..63522.21 rows=107 width=0) Recheck Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) -2::double precision) - Bitmap Index Scan on hladrb10101 (cost=0.00..17348.54 rows=107 width=0) Index Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) -2::double precision) (5 rows) and the index used are the correct ones If for that reason that i think that my machine runs out of memory, by the way, this is not the biggest table that i have others have more than
[GENERAL] Problems with memory
Hi I'm setting up a big database , and when i say big, i mean BIG, the problem with this is that some times, when a do a query the database run out of memory, so I really need to increase the amount of memory reserved to postgress almost 10x, could anyone tell me how can i do that? i mean, what configuration file and variables do i have to modify? Thanks pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
[GENERAL] Problems with memory
Hi I'm setting up a big database , and when i say big, i mean BIG, the problem with this is that some times, when a do a query the database run out of memory, so I really need to increase the amount of memory reserved to postgress almost 10x, could anyone tell me how can i do that? i mean, what configuration file and variables do i have to modify? Thanks pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] select from an index
Hi
Recently i created an index in a table using a function (not a column) as
following
create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb')); , where idr
is a function that returns a real number,as a result i got the following
table
mhc2db= \d precalc;
Table public.precalc
Column | Type| Modifiers
-+---+--
id | integer | not null default
nextval('precalc_id_seq'::regclass)
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
h2iab btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)),
now, i perform a query using this index
Select * from precalc where Idr(p1, p4, p6, p7, p9, 'H-2*IAb')2
and its works, the problems comes when you try to do selects below certain
number that it crash,
the funtion looks like
create function IDR(char,char,char,char,char,varchar(20)) returns real AS'
DECLARE
output real;
P1 real;
P4 real;
P6 real;
P7 real;
P9 real;
BEGIN
select into P1 score from PSSMS where AA=$1 and POS=1 and
MOLEC=$6; (*)
select into P4 score from PSSMS where AA=$2 and POS=4 and
MOLEC=$6;
select into P6 score from PSSMS where AA=$3 and POS=6 and
MOLEC=$6;
select into P7 score from PSSMS where AA=$4 and POS=7 and
MOLEC=$6;
select into P9 score from PSSMS where AA=$5 and POS=9 and
MOLEC=$6;
select into output P1+P4+P6+P7+P9;
return output;
END;
' LANGUAGE plpgsql IMMUTABLE;
and crash at (*), some of you know why?
thanks
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
[GENERAL] complex query using postgresql
Hi everybody
I have de following table where i can perform two different queries:
select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DR7')2; where idr
is a function used to create indicies
and
select * from precalc where p1='S';
Now i would like to perform a query as :
select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DR7')2 and
p1='S'...;
but i don't know how
any body can help me?
thanks
Column | Type | Modifiers
+--+--
id | integer | not null default
nextval('precalc_id_seq'::regclass)
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
h2iab btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying))
h2iad btree (idr(p1, p4, p6, p7, p9, 'H-2*IAd'::character varying))
h2iak btree (idr(p1, p4, p6, p7, p9, 'H-2*IAk'::character varying))
h2ied btree (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character varying))
hladqa10501 btree (idr(p1, p4, p6, p7, p9, 'HLA-DQA1*0501'::character
varying))
hladqb10201 btree (idr(p1, p4, p6, p7, p9, 'HLA-DQB1*0201'::character
varying))
hladr btree (idr(p1, p4, p6, p7, p9, 'HLA-DR'::character varying))
hladr1 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR1'::character varying))
hladr13 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR13'::character varying))
hladr3 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR3'::character varying))
hladr7 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR7'::character varying))
hladrb10101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character
varying))
hladrb10102 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0102'::character
varying))
hladrb10301 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0301'::character
varying))
hladrb10302 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0302'::character
varying))
hladrb10401 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0401'::character
varying))
hladrb10402 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0402'::character
varying))
hladrb10701 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0701'::character
varying))
hladrb10802 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0802'::character
varying))
hladrb10901 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0901'::character
varying))
hladrb11101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1101'::character
varying))
hladrb11102 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1102'::character
varying))
hladrb11103 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1103'::character
varying))
hladrb11104 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1104'::character
varying))
hladrb11301 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1301'::character
varying))
hladrb11302 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1302'::character
varying))
hladrb11501 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1501'::character
varying))
hladrb40101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB4*0101'::character
varying))
hladrb50101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101'::character
varying))
iid btree (id)
ip1 btree (p1)
ip4 btree (p4)
ip6 btree (p6)
ip7 btree (p7)
ip9 btree (p9)
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
[GENERAL] help with plpgsql
Hi everybody I trying to upload some plpsql functions to postgresql database using a perl script and i get the following error psql:/usr/local/Make2D-DB_II/pgsql/make2db_functions.pgsql:85: ERROR: language plpgsql does not exist HINT: Use CREATE LANGUAGE to load the language into the database. and then when I try to create the language, i get geldb=# CREATE LANGUAGE plpgsql; ERROR: language plpgsql already exists anybody knows what's wrong? Thanks -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] help with plpgsql
Hi everybody I trying to upload some plpsql functions to postgresql database using a perl script and i get the following error psql:/usr/local/Make2D-DB_II /pgsql/make2db_functions.pgsql:85: ERROR: language plpgsql does not exist HINT: Use CREATE LANGUAGE to load the language into the database. and then when I try to create the language, i get geldb=# CREATE LANGUAGE plpgsql; ERROR: language plpgsql already exists anybody knows what's wrong? Thanks -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
[GENERAL] Fwd: pgsql configuration
Hi today I had a problem with postgresql.conf file, i remove it from my local home, any of you know if there is some security copy of it somewhere or if there is some how to recover it? the guy who did the configuration is not working at my office any more and i don't know is he changed some directory form default configuration thanks pau -- Pau Marc Muñoz Torrespostgresql.conf Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona)postgresql.conf telèfon: 93 5812807 Email : [EMAIL PROTECTED]
[GENERAL] pgsql configuration
Hi today I had a problem with postgresql.conf file, i remove it from my local home, any of you know if there is some security copy of it somewhere or if there is some how to recover it? the guy who did the configuration is not working at my office any more and i don't know is he changed some directory form default configuration thanks pau -- Pau Marc Muñoz Torrespostgresql.conf Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona)postgresql.conf telèfon: 93 5812807 Email : [EMAIL PROTECTED]
[GENERAL] loading files into tables
Hi I'm an ex-mysql user, i know there was a sentence in mysql that was load data local infile somefile.txt into table sometable; I'm trying to do something similar in postgresql, can someone help me? Thanks Pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] loading files into tables
Ok I'm tryint to execute copy local from /path with delimiter \t; and I get the following errors ERROR: must be superuser to COPY to or from a file. Can I do something similar to it without being superuser? for a Tab delimiter, should i use \t character? Thanks Pau 2008/2/4, Dimitri Fontaine [EMAIL PROTECTED]: Hi, Le lundi 04 février 2008, Pau Marc Munoz Torres a écrit : I'm an ex-mysql user, i know there was a sentence in mysql that was load data local infile somefile.txt into table sometable; I'm trying to do something similar in postgresql, can someone help me? COPY manual entry as already been pointed to you, let me introduce pgloader project in case you would need to edit the data on-the-fly (ETL): http://pgloader.projects.postgresql.org/ http://pgfoundry.org/projects/pgloader/ http://pgsql.tapoueh.org/site/html/pgloader/index.html More to come on this last link :) -- dim -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
[GENERAL] if exists...does it exists for insert statments?
could i use a sentence similar to the mysql sentence
insert if not exist into SP values ('cesp','sp');
in postgresql?
pau
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
Re: [GENERAL] if exists...does it exists for insert statments?
Thanks, i'll test it tomorrow
pau
2007/12/11, Richard Broersma Jr [EMAIL PROTECTED]:
--- On Tue, 12/11/07, Pau Marc Munoz Torres [EMAIL PROTECTED] wrote:
could i use a sentence similar to the mysql sentence
insert if not exist into SP values
('cesp','sp');
in postgresql?
Using standard ANSI-SQL the statement could be re-written:
INSERT INTO SP
SELECT a, b
FROM VALUES ( 'cesp', 'sp' ) AS tmp( a, b )
LEFT JOIN Sp
ON (Sp.col1,Sp.col2)=(tmp.a,tmp.b)
WHERE (Sp.col1,Sp.col2) IS NULL;
Regards,
Richard Broersma Jr.
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
[GENERAL] Can i Force to postgrsql to use a certain index?
Hi every body it is possible to force to postgresql to use a certain index? I know that in mysql exits Force index (index_name) option for select queries SELECT * FROM precalc FORCE INDEX (hladrb50101) ; any of you have used this? thanks pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
[GENERAL] Can i Force to postgrsql to use a certain index?
Hi every body it is possible to force to postgresql to use a certain index? I know that in mysql exits Force index (index_name) option for select queries SELECT * FROM precalc FORCE INDEX (hladrb50101) ; any of you have used this? thanks pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
[GENERAL] Making a query from 2 tables at same time
Hi everybody
I'm doing a two table query as follow
mhc2db= select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local
as t1, precalc as t2 where t1.ce='ACIAD' and t2.idr(p1, p4, p6, p7, p9,
'HLA-DRB5*0101')2;
and i get the following error
ERROR: schema t2 does not exist
but those tables exists!! and are as follow!!
mhc2db= \d precalc (t2)
Table public.precalc
Column | Type | Modifiers
+--+--
id | integer | not null default
nextval('precalc_id_seq'::regclass)
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
h2iab btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying))
h2iad btree (idr(p1, p4, p6, p7, p9, 'H-2*IAd'::character varying))
h2iak btree (idr(p1, p4, p6, p7, p9, 'H-2*IAk'::character varying))
h2ied btree (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character varying))
hladqa10501 btree (idr(p1, p4, p6, p7, p9, 'HLA-DQA1*0501'::character
varying))
hladr btree (idr(p1, p4, p6, p7, p9, 'HLA-DR'::character varying))
hladr1 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR1'::character varying))
hladr13 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR13'::character varying))
hladr3 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR3'::character varying))
hladr4 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR4'::character varying))
hladr7 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR7'::character varying))
hladrb10101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character
varying))
hladrb10102 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0102'::character
varying))
hladrb10301 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0301'::character
varying))
hladrb10401 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0401'::character
varying))
hladrb10402 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0402'::character
varying))
hladrb10404 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0404'::character
varying))
hladrb10701 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0701'::character
varying))
hladrb10802 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0802'::character
varying))
hladrb10901 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0901'::character
varying))
hladrb11101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1101'::character
varying))
hladrb11102 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1102'::character
varying))
hladrb11104 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1104'::character
varying))
hladrb11301 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1301'::character
varying))
hladrb11302 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1302'::character
varying))
hladrb11501 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1501'::character
varying))
hladrb40101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB4*0101'::character
varying))
hladrb50101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101'::character
varying))
iid btree (id)
mhc2db= \d local (t1)
Table public.local
Column | Type | Modifiers
+--+---
ce | character varying(6) |
sp | character varying(6) |
pos| integer |
id | integer |
Someone knows what is the problem?
Thanks
Pau
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
Re: [GENERAL] Making a query from 2 tables at same time
i test it and now the error is mhc2db= select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local as t1, precalc as t2 where t1.ce='ACIAD' and idr(t2.p1, t2.p4, t2.p6, t2.p7, t2.p9, 'HLA-DRB5*0101')2; ERROR: relation pssms does not exist CONTEXT: SQL statement SELECT score from PSSMS where AA= $1 and POS=1 and MOLEC= $2 PL/pgSQL function idr line 11 at select into variables pssm was a temporary table that i used to calculate the index in precalc table with idr function, should i rebuilt it? the problem is that if i can't use idr as an index it eill be to slow pau 2007/11/29, Richard Huxton [EMAIL PROTECTED]: Pau Marc Munoz Torres wrote: Hi everybody I'm doing a two table query as follow mhc2db= select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local as t1, precalc as t2 where t1.ce='ACIAD' and t2.idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101')2; and i get the following error ERROR: schema t2 does not exist but those tables exists!! and are as follow!! You have t2.idr(...) which is being read as a function idr in schema t2. Did you mean idr(t2.p1, t2.p4, ...)? -- Richard Huxton Archonet Ltd -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] Making a query from 2 tables at same time
Ok, I have two tables, first one, that i call precalc has the following structure id . serial p1 varchar p4 varchar p6 varchar p7 varchar p9 varchar and a numer of index that is a real number resulting of a function (function is called idr and i talk about it bellow) another table is local than has the following fields ce varchar sp varchar pos integer id integer id values for both tables are the same. idr function is a function that I wrote create function IDR(char,char,char,char,char,varchar(20)) returns real AS' DECLARE output real; P1 real; P4 real; P6 real; P7 real; P9 real; BEGIN select into P1 score from PSSMS where AA=$1 and POS=1 and MOLEC=$6; select into P4 score from PSSMS where AA=$2 and POS=4 and MOLEC=$6; select into P6 score from PSSMS where AA=$3 and POS=6 and MOLEC=$6; select into P7 score from PSSMS where AA=$4 and POS=7 and MOLEC=$6; select into P9 score from PSSMS where AA=$5 and POS=9 and MOLEC=$6; select into output P1+P4+P6+P7+P9; return output; END; ' LANGUAGE plpgsql IMMUTABLE; where PSSMS was a temporay table (it don't exist right now) now i would like to perform a select to get p1,p2,p6,p7,p9, sp and pos from those register that her value in the index is bigger than x mhc2db= select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local as t1, precalc as t2 where t1.ce='ACIAD' and idr(t2.p1, t2.p4, t2.p6, t2.p7, t2.p9, 'HLA-DRB5*0101')2; if i perfom a select like select * from precalc where dr(p1,p4, p6, p7, p9, 'HLA-DRB5*0101')2; or select * from local where ce='ACIAD'; works perfectely is it clear enough? i don't now if i make myself understand, any way, if it not, please, ask me!! thanks pau 2007/11/29, Richard Huxton [EMAIL PROTECTED]: Pau Marc Munoz Torres wrote: i test it and now the error is mhc2db= select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local as t1, precalc as t2 where t1.ce='ACIAD' and idr(t2.p1, t2.p4, t2.p6, t2.p7, t2.p9, 'HLA-DRB5*0101')2; ERROR: relation pssms does not exist CONTEXT: SQL statement SELECT score from PSSMS where AA= $1 and POS=1 and MOLEC= $2 PL/pgSQL function idr line 11 at select into variables pssm was a temporary table that i used to calculate the index in precalc table with idr function, should i rebuilt it? the problem is that if i can't use idr as an index it eill be to slow I think you need to take a step back and explain what it is you are trying to do - you shouldn't be using an external table in an indexed function at all. -- Richard Huxton Archonet Ltd -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
[GENERAL] select using an index
Hi
Recently i created an index in a table using a function (not a column) as
following
create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));,
where idr is a function that returns a real number,
as a result i got the following table
mhc2db= \d precalc;
Table public.precalc
Column | Type| Modifiers
-+---+--
id | integer | not null default
nextval('precalc_id_seq'::regclass)
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
h2iab btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)),
now, i would like to perform a query using this index, something like
Select * from precalc where h2iab2
but obviously h2iab is not a column...
some of you knows what i should do?
thanks
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
[GENERAL] indexing tables using my owns functions
Hi every body Recently i wrote my own function into postgesql... and it works perfecly!!, now i would like use it to index a table like this guy do at the link ( http://www.faqs.org/docs/ppbook/r24254.htm) but i get the following error mhc2db= create index h2iab on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb')); ERROR: functions in index expression must be marked IMMUTABLE mhc2db= in this indexation p1,p4,p6,p7,p9 are variables that depends on the field. could anyone help me? -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
[GENERAL] select from an index
Hi
Recently i created an index in a table using a function (not a column) as
following
create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));,
where idr is a function that returns a real number,
as a result i got the following table
mhc2db= \d precalc;
Table public.precalc
Column | Type| Modifiers
-+---+--
id | integer | not null default
nextval('precalc_id_seq'::regclass)
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
h2iab btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)),
now, i would like to perform a query using this index, something like
Select * from precalc where h2iab2
but obviously h2iab is not a column...
some of you knows what i should do?
thanks
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
[GENERAL] loading a funtion script from a file
Hi I've written a sql function in a text file, and now, i would like to upload into postgresql an execute, is there any command to do it? as far as I know in mysql exist source command, is there something similar in postgresql? Thanks -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] moving from mysql to postgree
Thanks every body, today i've created my first table with postgresql, what a mass with \d table and sequancial vs auto_increment data type in create... anyway, i will become an expert soon ;-) thanks again pau 2007/11/16, Merlin Moncure [EMAIL PROTECTED]: On Nov 15, 2007 10:44 AM, Pau Marc Munoz Torres [EMAIL PROTECTED] wrote: I'm moving from mysql to postgresql just now i I'm a bit lost, could anyone tell me some place with a comparative between postdresql and mysql commands, i think than mostly is the same think but, any way, do anything change ? IMO, he biggest differences between mysql and postgresql for beginners are: * database authentication (pg_hba.conf and user set up) * create/alter table syntax * dealing with timestamps/default expressions * update and delete statements that deal with more than one table postgresql has a _much_ better command shell than mysql. It has tab-completions for commands as well as database objects and has syntax help for all commands. This is an absolutely essential help to the beginner. (some of the gui tools, like pgadmin, have this as well but i use the shell almost exclusively). \h command in the shell will give you help for the command and \d table well 'describe' the table good luck!! merlin -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
[GENERAL] moving from mysql to postgree
Hi I'm moving from mysql to postgresql just now i I'm a bit lost, could anyone tell me some place with a comparative between postdresql and mysql commands, i think than mostly is the same think but, any way, do anything change ? pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
