[SQL] how to solve this problem
Dear users, I have this problem I have a table where there are 20 columns named vinc1, vinc2, vinc3, vinc4, etc the values contained into each column are simply 1 or 0 (each column is dichotomic) 1 means presence 0 means absence I would obtain a column (new_column) containg the name of the columns, comma separated, where the value is = 1 for example: vinc1 vinc2 vinc3 vinc4 new_column 1 0 1 0 vinc1,vinc3 0 0 0 1 vinc4 0 1 1 1 vinc2,vinc3,vinc4 can someone help me to find the best way to obtain this result??? thank you very much Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: 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] how to solve this problem
Just a thought, Could you achieve that same result using the binary representation of an integer? Regards, Richard --- ivan marchesini <[EMAIL PROTECTED]> wrote: > Dear users, > I have this problem > > I have a table where there are 20 columns named > vinc1, vinc2, vinc3, vinc4, etc > > the values contained into each column are simply 1 or 0 (each column is > dichotomic) > 1 means presence > 0 means absence > > I would obtain a column (new_column) containg the name of the columns, > comma separated, where the value is = 1 > > for example: > > vinc1 vinc2 vinc3 vinc4 new_column > 1 0 1 0 vinc1,vinc3 > 0 0 0 1 vinc4 > 0 1 1 1 vinc2,vinc3,vinc4 > > can someone help me to find the best way to obtain this result??? > thank you very much > > Ivan > > > > > > > > -- > Ivan Marchesini > Department of Civil and Environmental Engineering > University of Perugia > Via G. Duranti 93/a > 06125 > Perugia (Italy) > e-mail: [EMAIL PROTECTED] > [EMAIL PROTECTED] > tel: +39(0)755853760 > fax: +39(0)755853756 > jabber: [EMAIL PROTECTED] > > > > > ---(end of broadcast)--- > TIP 1: 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 > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] how to solve this problem
Sorry, I'm not able to understand how I can use this solution!!! can you better explain me your suggestion??? Thanks very much Il giorno gio, 13/04/2006 alle 06.51 -0700, Richard Broersma Jr ha scritto: > Just a thought, > > Could you achieve that same result using the binary representation of an > integer? > > Regards, > > Richard > > --- ivan marchesini <[EMAIL PROTECTED]> wrote: > > > Dear users, > > I have this problem > > > > I have a table where there are 20 columns named > > vinc1, vinc2, vinc3, vinc4, etc > > > > the values contained into each column are simply 1 or 0 (each column is > > dichotomic) > > 1 means presence > > 0 means absence > > > > I would obtain a column (new_column) containg the name of the columns, > > comma separated, where the value is = 1 > > > > for example: > > > > vinc1 vinc2 vinc3 vinc4 new_column > > 1 0 1 0 vinc1,vinc3 > > 0 0 0 1 vinc4 > > 0 1 1 1 vinc2,vinc3,vinc4 > > > > can someone help me to find the best way to obtain this result??? > > thank you very much > > > > Ivan > > > > > > > > > > > > > > > > -- > > Ivan Marchesini > > Department of Civil and Environmental Engineering > > University of Perugia > > Via G. Duranti 93/a > > 06125 > > Perugia (Italy) > > e-mail: [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > tel: +39(0)755853760 > > fax: +39(0)755853756 > > jabber: [EMAIL PROTECTED] > > > > > > > > > > ---(end of broadcast)--- > > TIP 1: 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 > > > > -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] on select rule
Hi If I can not do insert/update action with on select rule, how can I execute some procedure or any sql statement on select event? WBR ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] how to solve this problem
I have a table where there are 20 columns named vinc1, vinc2, vinc3, vinc4, etc the values contained into each column are simply 1 or 0 (each column is dichotomic) 1 means presence 0 means absence I would obtain a column (new_column) containg the name of the columns, comma separated, where the value is = 1 for example: vinc1 vinc2 vinc3 vinc4 new_column 1 0 1 0 vinc1,vinc3 0 0 0 1 vinc4 0 1 1 1 vinc2,vinc3,vinc4 can someone help me to find the best way to obtain this result??? thank you very much Here's a brute-force method. Maybe someone else has a more elegant way. More info on the nature of your data and what you are trying to obtain from it would help in finding such elegance. select substr( case when vinc1 = 1 then ',vinc1' else '' || case when vinc2 = 1 then ',vinc2' else '' || ... case when vinc20 = 1 then ',vinc20' else '' ,2) as new_column ... As to the binary representation mentioned elsewhere, the idea is that you can view vinc1-vinc20 as bits in a binary number equal to vinc1 + 2*vinc2 + 4*vinc3 + 8*vinc4...2^19*vinc20. Whether or not this is useful depends on what you are trying to do. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] on select rule
am 13.04.2006, um 18:46:02 +0300 mailte Sergey Levchenko folgendes: > Hi > > If I can not do insert/update action with on select rule, how can I > execute some procedure or any sql statement on select event? With a TRIGGER. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Simple plpgsql question
Hi,
I have, what I hope to be, a simple question about plpgsql.
I have a trigger on a table right now that updates a count everytime
that a new record is entered into a database (or removed).
What I'd also like to do is have it create a new row in a different
table using the automatically assigned id as a reference, but I'm
unsure of how to obtain the id of the newly created row in the first
table.
Example:
CREATE TABLE system_info (
id serial PRIMARY KEY,
name varchar(255),
value varchar(255)
);
INSERT INTO system_info (name,value) VALUES ('total_users','0');
CREATE TABLE master (
id serial PRIMARY KEY,
name varchar(32) NOT NULL CHECK ( name <> ''),
UNIQUE(name)
);
CREATE TABLE slave (
id serial PRIMARY KEY,
master_id integer REFERENCES master (id),
additional_info text
);
CREATE OR REPLACE FUNCTION update_users() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
UPDATE system_info SET value=(value::integer)-1 WHERE name =
'total_users'
RETURN OLD;
ELSEIF TG_OP = 'INSERT' THEN
UPDATE system_info SET value=(value::integer)+1 WHERE name =
'total_users';
INSERT INTO slave (master_id) VALUES (THIS IS WHAT I NEED TO KNOW);
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_users AFTER INSERT OR DELETE ON master
FOR EACH ROW EXECUTE PROCEDURE update_users();
The part I need to know is the INSERT INTO statement in the procedure.
Any help would be great.
Thanks!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
