[SQL] sql
I have 3 tables. table_a marca 15145 1455 1333 table_b data 11-01-2002 11-02-2002 table_c marca data 15145 11-01-2002 15145 11-02-2002 1455 11-01-2002 1333 11-01-2002 1333 11-02-2002 After interogation of the tables I need this kind of result: marca data 1455 11-02-2002 (I want to receive the records which are a combitation of fields of table_a and table_b and that are not in the table_c) How can I do that? Thanks!
Re: [SQL] sql
On Monday 09 Dec 2002 10:40 am, cristi wrote: > (I want to receive the records which are a combitation of fields of table_a > and table_b and that are not in the table_c) The following is one way. Not necessarily the most efficient, but it should be clear enough. Basically it builds the product of table_a,table_b then uses a LEFT JOIN with WHERE to find items that don't match. You'll want to test it against your real data to see if it's fast enough. richardh=> SELECT * FROM table_a; a --- 1 2 3 (3 rows) richardh=> SELECT * FROM table_b; b 2002-01-01 2002-02-02 2002-03-03 (3 rows) richardh=> SELECT * FROM table_c; ca | cb + 1 | 2002-01-01 1 | 2002-02-02 2 | 2002-02-02 (3 rows) richardh=> \d view_ab View "view_ab" Column | Type | Modifiers +-+--- a | integer | b | date| View definition: SELECT table_a.a, table_b.b FROM table_a, table_b; richardh=> SELECT a,b FROM view_ab LEFT JOIN table_c ON a=ca AND b=cb WHERE ca IS NULL or cb IS NULL ORDER BY a,b; a | b ---+ 1 | 2002-03-03 2 | 2002-01-01 2 | 2002-03-03 3 | 2002-01-01 3 | 2002-02-02 3 | 2002-03-03 (6 rows) -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] ISNULL FUNCTION
HI, HERE IS AN ALTERNATIVE TO USE THE SQL SERVER ISNULL() FUNCTION select case when FIELD_NAME isnull then 'EXPRESION' else FIELD_NAME end from calfiscal where impuesto = 1 Ahora podés usar Yahoo! Messenger desde tu celular. Aprendé cómo hacerlo en Yahoo! Móvil: http://ar.mobile.yahoo.com/sms.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] ISNULL FUNCTION
HI, HERE IS AN ALTERNATIVE TO USE THE SQL SERVER ISNULL() FUNCTION select case when FIELD_NAME isnull then 'EXPRESION' else FIELD_NAME end from calfiscal where impuesto = 1 Ahora podés usar Yahoo! Messenger desde tu celular. Aprendé cómo hacerlo en Yahoo! Móvil: http://ar.mobile.yahoo.com/sms.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] ISNULL FUNCTION
--- Héctor Iturre <[EMAIL PROTECTED]> wrote: > HI, >HERE IS AN ALTERNATIVE TO USE THE SQL SERVER > ISNULL() FUNCTION > > > select case when FIELD_NAME isnull then 'EXPRESION' > else FIELD_NAME end > from calfiscal > where impuesto = 1 try using SELECT coalesce(field_name,'EXPRESSION') FROM calfiscal WHERE impuestor = 1 OR SELECT case when FIELD_NAME IS NULL then 'EXPRESION' else FIELD_NAME end from calfiscal where impuesto = 1 regards, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Adding foreign key constraint post table creation
All, A couple of novice questions: I would like to modify an existing TABLE by addinga new column (FOREIGN KEY): type_id int not null, foreign key (type_id) references cvterm (cvterm_id), Will this work ( running PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96): ALTER TABLE contig ADD COLUMN type_id int; ALTER TABLE contig ADD CONSTRAINT cvtermfk FOREIGN KEY (type_id) references cvterm (cvterm_id); I would like to load data into the table below from a file lacking the timestamp fields, where the file structure is: COPY table FROM STDIN; 1 feature_typetypes of features \N 2 3'-exon \N 1 . . . \. This fails as the timestamp fields are 'not null'. Othere than generating INSERT stmts for the data how else could I enter the data? create table cvterm ( cvterm_id serial not null, primary key (cvterm_id), termname varchar(255) not null, termdefinition text, termtype_id int, foreign key (termtype_id) references cvterm (cvterm_id), timeentered timestamp not null default current_timestamp, timelastmod timestamp not null default current_timestamp, unique(termname, termtype_id) ); regards, Charles ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Rules/Trigges Trade-offs
Josh, Thanks for the info. I need to change an insert into an update when the key already exists. I have been using a rules to test it on a small set (table) and it works. "Rules can't use indexes" just scared me. I will have to test on a larger set. Also, I had the impression that if a trigger returned NULL, one would get the equivalent of "DO NOTHING". Am I wrong with that assumption? JLL Josh Berkus wrote: > > Bruce, Richard, > > > Triggers are mostly for testing/modifying the row being > > inserted/updated, while rules are better for affecting other rows or > > other tables. > > Hmmm. Thought that there were also some other criteria: > > 1) Rules can't use indexes to do their processing, so Rules which query large > secondary tables can be a bad idea (maybe this has changed?) > > 2) Only Rules can "DO INSTEAD"; thus, only Rules are good for defining > Read/Write views. > > 3) There are no AFTER Rules, making, for example, a rule with a table check on > the new data impractical, so you'd want to use Triggers or Constraints > > etc. > > There are, IMHO, some things Rules are better for, and some things Triggers > are better for. I tend to use all Triggers except for updatable views, > simply because using a mix of Rules and Triggers can be very hard to keep > track of, but YMMV. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Default Permissions (repost from Novice)
On Monday 09 Dec 2002 6:51 am, Michael Weaver wrote: > How can I set default permissions on tables, so that I don't have to > manually set them for every table? Don't know if you got an answer in novice, but your best bet is one of the GRANT ... TO ALL type solutions mentioned in the last week or so. See my PostgreSQL Notes linked to from http://techdocs.postgresql.org -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Rules/Trigges Trade-offs
Ian, > Anyway, I have a similar requirement, to intercept insert/update/delete and redirect the data if a condition is met. Right now I am trying an INSTEAD rule that puts the condtion in the WHERE of the rule definition. It seems to work OK, but if the condition has exeptions where I would like to throw an error, I can't. And since it is an INSTEAD, I can't have a trigger on the table fire to find the exception cases. Urgh. I'd suggest a "DO INSTEAD SELECT some_function(NEW)" where some_function is a function that tests for errors, throws and exception if necessary, otherwise inserts. It's tricky, but probably the best way to get trigger + rule functionality at once. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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
Re: [SQL] Rules/Trigges Trade-offs
Ian, You're welcome. Replying to the list for the edification of other users. -Josh > I just figgered it out. I declared the function as > > create function some_func(test) returns int ... > > where test is the name of a table. The values are passed as a tcl array. I will see if RECORD works too. I wonder if that would eliminate the problem I ran into where I had dropped and recreated the table. The function barfed since the OID for the user defined type "test" did not exist. > > This will work slick. Thanks! > > >>> Josh Berkus <[EMAIL PROTECTED]> 12/09/02 12:29PM >>> > Ian, > > > That makes sense! Is that psuedo code, or is there a way to send a function > the entire NEW array without specifying each column name and datatype > specifically. > > > > I am using pltcl, if that matters.. > > Not sure about pltcl. I'd say, try it, declare the function as: > > CREATE FUNCTION some_func (RECORD) and see how things go. > > -- > -Josh Berkus > > __AGLIO DATABASE SOLUTIONS___ > Josh Berkus >Complete information technology[EMAIL PROTECTED] > and data management solutions (415) 565-7293 >for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > > > -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Rules/Trigges Trade-offs
Ian, > Thanks! I would have, but my messages bounce from SQL, even though I am subscribed ( I get the messages, for crying out loud!) send an e-mail to [EMAIL PROTECTED] > > PS RECORD doesn't work but I think that is because I am on 7.2.1 OK. I may have tested that on 7.4 devel by accident. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] Problem with a lookup table! Please help.
Hi, In my capacity as a vet student, I'm trying to create a database of antibiotics. The way that I have set it up so far is to have one main table listing the antibiotics versus their respective efficacies against the four major groups of bacteria. Due to the way that my PHP frontend works, I have assigned a number to the efficacy - 1 being excellent and 5 being poor efficacy against the particular bacterium. However, I now want to have a new table which converts numbers into words. The problem is this, if I join the main table with the "translation" lookup table, the column names for each of the four categories in the main default to the column name in the lookup table and hence are all the same. What SQL expression should I use to translate the cryptic numbers into plain english whilst preserving the column headings in the main table? Regards, Chris J
Re: [SQL] [OT] Inventory systems (private)
Ries,
Sorry I missed a few days here. I was busy elsewhere.
Anyway, the speed issue is fixed with indexes. Once you know
the kind of queries you will be making, create an optimized index for
each one of those queries.
You probably want to have a separate table for storing
the attribute names. I.e. instead of using a text variable
in each record, use a unique id which references an entry
in an attributes table. This won't affect the speed of the
system but it will save some space. Or if nothing else, it will
be more elegant.
Cheers,
Troy
>
> Troy, Andy,
>
> you both are right and it was my first tought. My only concern is that would
> this system be fast enough for large tables (for me large is around 250.000
> unique items) and thus my attribute table would be around 2.500.000 and
> 5.000.000 entrys. A record for one attribute is small I think around 128
> byte in size.
>
> One thing is that every attribute must be in it's own domain. For weight for
> example I have three different meanings ( 1] Pull weight 2] push weight 3]
> weight of the items itself) but using the method troy suggested that would
> not be a problem if I create some sort of a domain table.
>
> Anyway so far thangs for the quick responses, I've got something to work on.
>
> best regards,
> Ries van Twisk
>
>
>
>
> -Oorspronkelijk bericht-
> Van: Troy [mailto:[EMAIL PROTECTED]]
> Verzonden: dinsdag 3 december 2002 15:47
> Aan: Ries van Twisk
> CC: [EMAIL PROTECTED]
> Onderwerp: Re: [SQL] [OT] Inventory systems (private)
>
>
> Ries,
>
> One solution is to create a table such as follows:
>
> CREATE TABLE inventory (id serial, product text,
> PRIMARY KEY (id)
> ) ;
> CREATE TABLE attributes (prodid int4, textkey text, textvalue text,
> int4value int4,
> FOREIGN KEY (prodid) REFERENCES inventory (id) MATCH FULL ON DELETE CASCADE
> ON UPDATE CASCADE
> );
>
> INSERT INTO inventory (id, product) VALUES (100, 'Tire');
>
> INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Type',
> 'matchbox tire');
> INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Color',
> 'black');
> INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
> 'Diameter', 12, 'mm');
> INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
> 'Weight', 20, 'g');
> CREATE INDEX textkeys ON attributes USING btree (prodid);
> CREATE INDEX textkeys2 ON attributes USING btree (prodid,textkey);
>
> To select diameter for product id 100 (Tire):
> SELECT prodid FROM attributes WHERE prodid = 100 AND textkey = 'Diameter';
>
> To select several:
> SELECT prodid,textkey,textvalue,int4value FROM attributes WHERE prodid = 100
> AND textkey IN ('Diameter', 'Weight', 'Color', 'Type');
>
>
> and so on.
>
> The indexes are just a fast guess. You would need to look at the queries
> you generate and decide which indexes are needed.
>
>
>
> Cheers,
>
>
> Troy
>
> Troy KorjuslommiTksoft Inc.
> [EMAIL PROTECTED]
>
>
>
>
> >
> > Hi All,
> >
> > is there any whitepaper, document or website that can point me to how to
> > setup a inventory system?
> > I'm particulary interested how other people solve the problem of a unknown
> > number of attributes to a inventory item.
> >
> > example:
> > BAL<-- Inventory Item
> > - Color <- Attribute
> > - Diameter <- Attribute
> > - Weight <- Attribute
> >
> > Car<-- Inventory Item
> > - Speed <- Attribute
> > - Size <- Attribute
> > - Weight <- Attribute
> > - Color <- Attribute
> >
> > Computer<-- Inventory Item
> > - Brand <- Attribute
> > - Weight <- Attribute
> > - Windows/Linux <- Attribute
> >
> >
> > I can ofcource add any number of columns to a table but for a lot of items
> > there will be a lot of NULL values and currently I don't know how many
> > attrubutes one item can have (possible between 10 and 20). This can even
> > change in feature opon request.
> >
> > Ries
> >
> >
> > ---(end of broadcast)---
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problem with a lookup table! Please help.
Chris, > In my capacity as a vet student, I'm trying to create a database of antibiotics. The way that I have set it up so far is to have one main table listing the antibiotics versus their respective efficacies against the four major groups of bacteria. Due to the way that my PHP frontend works, I have assigned a number to the efficacy - 1 being excellent and 5 being poor efficacy against the particular bacterium. However, I now want to have a new table which converts numbers into words. The problem is this, if I join the main table with the "translation" lookup table, the column names for each of the four categories in the main default to the column name in the lookup table and hence are all the same. What SQL expression should I use to translate the cryptic numbers into plain english whilst preserving the column headings in the main table? Please post your table definitions as SQL statements. -- -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] Problem with a lookup table! Please help.
Use the AS keyword to introduce a column alias. Select thisverlongtablename.thefirstfield as title, ... from JLL Josh Berkus wrote: > > Chris, > > > In my capacity as a vet student, I'm trying to create a database of > antibiotics. The way that I have set it up so far is to have one main table > listing the antibiotics versus their respective efficacies against the four > major groups of bacteria. Due to the way that my PHP frontend works, I have > assigned a number to the efficacy - 1 being excellent and 5 being poor > efficacy against the particular bacterium. However, I now want to have a new > table which converts numbers into words. The problem is this, if I join the > main table with the "translation" lookup table, the column names for each of > the four categories in the main default to the column name in the lookup > table and hence are all the same. What SQL expression should I use to > translate the cryptic numbers into plain english whilst preserving the column > headings in the main table? > > Please post your table definitions as SQL statements. > > -- > -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]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problem with a lookup table! Please help.
Hi,
Thanks for your reply. Table definitions are:
CREATE TABLE tblantibiotics ('Antibiotic'
varchar(50),'Activity_against_grampos' int,'Activity_against_gramneg'
int,'Activity_against_aerobes' int,'Activity_against_anaerobes' int);
CREATE TABLE efficacy ('Efficacy_code' int,'Plain_english' varchar (10));
In table efficacy, 'Efficacy_code' contains a 1 - 5 scale of efficacy. Each
string in 'Plain_english' is a plain English descriptor of the value in
'Efficacy_code'. For example, 1 => Excellent, 2=> Good, etc etc. What I
need to do is to substitute the integers set in the "Activity..."
columns in tblantibiotics with the strings in 'Plain_english' according to
the integer set in 'Efficacy_code'. Any ideas?
Chris J
- Original Message -
From: "Josh Berkus" <[EMAIL PROTECTED]>
To: "Chris Jewell" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, December 09, 2002 9:08 PM
Subject: Re: [SQL] Problem with a lookup table! Please help.
Chris,
> In my capacity as a vet student, I'm trying to create a database of
antibiotics. The way that I have set it up so far is to have one main table
listing the antibiotics versus their respective efficacies against the four
major groups of bacteria. Due to the way that my PHP frontend works, I have
assigned a number to the efficacy - 1 being excellent and 5 being poor
efficacy against the particular bacterium. However, I now want to have a
new
table which converts numbers into words. The problem is this, if I join the
main table with the "translation" lookup table, the column names for each of
the four categories in the main default to the column name in the lookup
table and hence are all the same. What SQL expression should I use to
translate the cryptic numbers into plain english whilst preserving the
column
headings in the main table?
Please post your table definitions as SQL statements.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] ISNULL FUNCTION
- Original Message - From: "Héctor Iturre" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, December 09, 2002 11:30 AM Subject: [SQL] ISNULL FUNCTION > HI, >HERE IS AN ALTERNATIVE TO USE THE SQL SERVER > ISNULL() FUNCTION > > > select case when FIELD_NAME isnull then 'EXPRESION' > else FIELD_NAME end > from calfiscal > where impuesto = 1 > > Ahora podés usar Yahoo! Messenger desde tu celular. Aprendé cómo hacerlo en Yahoo! Móvil: http://ar.mobile.yahoo.com/sms.html > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html Segue um exemplo que funciona satisfatoriamente. Recrie esta função alterando os parametros (text, text e RETURNS text) para os tipos que lhe for necessario. CREATE FUNCTION isnull(text, text) RETURNS text AS 'SELECT (CASE (SELECT $1 is null) WHEN true THEN $2 ELSE $1 END) AS RESULT' LANGUAGE 'sql'; COMMENT ON FUNCTION isnull(text, text) IS 'Retorna o 2º arg se o 1º for nulo'; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem with a lookup table! Please help.
On Mon, 2002-12-09 at 20:58, Chris Jewell wrote: > ... > However, I now want to have a new table which converts numbers into > words. The problem is this, if I join the main table with the > "translation" lookup table, the column names for each of the four > categories in the main default to the column name in the lookup table > and hence are all the same. What SQL expression should I use to > translate the cryptic numbers into plain english whilst preserving the > column headings in the main table? You just need to name the columns: SELECT t.antibiotic, e1.plain_english AS "Activity against grampos", e2.plain_english AS "Activity against gramneg", e3.plain_english AS "Activity against aerobes", e4.plain_english AS "Activity against anaerobes" FROM tblantibiotics AS t, efficacy AS e1, efficacy AS e2, efficacy AS e3, efficacy AS e4 WHERE t.activity_against_grampos = e1.efficacy_code AND t.activity_against_gramneg = e2.efficacy_code AND t.activity_against_aerobes = e3.efficacy_code AND t.activity_against_anaerobes = e4.efficacy_code; Note that you must use double quotes to quote identifiers, not single quotes as you did in your table creation definitions, which won't work in PostgreSQL. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "I beseech you therefore, brethren, by the mercies of God, that ye present your bodies a living sacrifice, holy, acceptable unto God, which is your reasonable service." Romans 12:1 ---(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
