Re: [SQL] Transpose rows to columns
Hi David On Jan 13, 2004, at 10:12 AM, David Witham wrote: Hi, I have a query that returns data like this: cust_id cust_name month costrevenue margin 991234 ABC 2003-07-01 10 15 5 991234 ABC 2003-08-01 11 17 6 991234 ABC 2003-09-01 12 19 7 991235 XYZ 2003-07-01 13 21 8 991235 XYZ 2003-08-01 12 19 7 991235 XYZ 2003-09-01 11 17 6 I want to turn it around so it displays like this: 991234,ABC,2003-07-01,10,15,5,2003-08-01,11,17,6,2003-09-01,12,19,7 991235,XYZ,2003-07-01,13,21,8,2003-08-01,12,19,7,2003-09-01,11,17,6 (I've used commas to shorten the layout for the example) Does anyone have some ideas on how to do this? I'd suggest looking at tablefunc in /contrib. It includes crosstab functionality that you might find useful. I don't think it'll do exactly what you describe here, but something quite similar. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Transpose rows to columns
Hi, I have a query that returns data like this: cust_id cust_name month costrevenue margin 991234 ABC 2003-07-01 10 15 5 991234 ABC 2003-08-01 11 17 6 991234 ABC 2003-09-01 12 19 7 991235 XYZ 2003-07-01 13 21 8 991235 XYZ 2003-08-01 12 19 7 991235 XYZ 2003-09-01 11 17 6 I want to turn it around so it displays like this: 991234,ABC,2003-07-01,10,15,5,2003-08-01,11,17,6,2003-09-01,12,19,7 991235,XYZ,2003-07-01,13,21,8,2003-08-01,12,19,7,2003-09-01,11,17,6 (I've used commas to shorten the layout for the example) Does anyone have some ideas on how to do this? Thanks, David Witham Telephony Platforms Architect Unidial Ph: 03 8628 3383 Fax: 03 8628 3399 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Unique key field or serverl fks ?
Hi, I would like to know opinions about which approach is better: Having a table with a field that works as a unique key, or having several fks that work as a combined key ( all the fks fields )? Depends on the particular situation, you'll need to give details of the tables and their place in your system. There are two reasons I've seen given for using an artificial (substitute) primary key: 1. It's "lighter" than several other fields (especially where they are text) 2. The natural primary key has meaning to the users, and the users will tend to get it wrong. The second is probably the more persuasive - the first can definitely have costs as well as benefits. Ok, thanks for answering, example: Articles Table articleid Warehouses Table warehouseid Locations per warehouse Table warehouseid (fk) locationid description Articles Per warehouse warehouseid (fk) articleid (fk) locationid (fk) stock In the Articles per warehouse the "primary key" is the cominbation of 3 fks. Is that kind of situation acceptable or it could be better to have a unique primary key field in addition to the fks? This "gets worse" if another table has information binded to the articles per warehouse, it has to reference the 3 fks in addition to its own key fields. Thanks in advance, K. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Triggers
On Monday 12 January 2004 16:14, beyaRecords - The home Urban music wrote: > Hi, > does postgresql support the ability to email as in SQL Server? I want > to create a trigger which on input of a record will send out an email. > Is this possible? Have a look in the mailing list archives - this has been covered recently. I seem to recall there is an add-on that does just this. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Adding a column to a VIEW which has dependent objects.
On Sat, 10 Jan 2004, Tom Lane wrote: > Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: > > How do people extend a parent view which has > > lot of dependent views? > > The parent view cannot be dropped because that will > > require recreating a dozen of dependent views. > > You're out of luck, you'll have to drop and remake them all. > In future we could think about some kind of ALTER VIEW ADD COLUMN > operation, but it ain't there now. > > (I suppose if you were really desperate you could think about manually > hacking the system catalogs, but this would be pretty risky on a > production database.) > > > Also is there an easy way of dumping the definitions > > of all the dependent views of a given object. > > You can chase the links in pg_depend to see what the dependent objects > are, but extracting their definitions would be a tad harder ... Note that the definitions for views are stored in pg_views as well. ---(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] Triggers
Dear Uzo , Hi, does postgresql support the ability to email as in SQL Server? I want to create a trigger which on input of a record will send out an email. Is this possible? http://pgmail.sourceforge.net/ is what you need. Regards, Vishal Kashyap ---(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] Triggers
beyaRecords - The home Urban music wrote: > does postgresql support the ability to email as in SQL Server? I want > to create a trigger which on input of a record will send out an > email. Is this possible? Write a trigger function in, say, PL/PerlU or PL/sh and have it send the email with the usual Perl or shell commands. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Triggers
Hi, does postgresql support the ability to email as in SQL Server? I want to create a trigger which on input of a record will send out an email. Is this possible? Uzo ---(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] Problem with NOT IN portion of query.
When grilled further on (Mon, 12 Jan 2004 07:28:09 +0100), Tomasz Myrta <[EMAIL PROTECTED]> confessed: > Dnia 2004-01-12 05:04, U¿ytkownik Robert Creager napisa³: > > > > SELECT date_trunc( 'hour', "when" )::timestamp AS > > period FROM readings WHERE period NOT IN (SELECT "time" FROM > > hour.summary_period) GROUP BY period ORDER BY period; > > > > The error is: > > > > ERROR: column "period" does not exist > > > Your problem has nothing to "NOT IN". > Your query works fine, when you remove column alias from WHERE clause - > it's beacause WHERE clause is executed *before* data output (and before > column aliases). You can still use column aliases in "GROUP BY" and > "ORDER BY". Thanks for the info. So now I have: SELECT p.period FROM (SELECT date_trunc( 'hour', "when" )::timestamp AS period FROM readings GROUP BY period) AS p WHERE p.period NOT IN (SELECT "time" FROM hour.summary_period) ORDER BY p.period; which appears works as expected. Anything obviously goofy with the above query? Cheers, Rob -- 05:57:10 up 14 days, 19:47, 4 users, load average: 2.17, 2.07, 2.04 pgp0.pgp Description: PGP signature
Re: [SQL] Unique key field or serverl fks ?
On Monday 12 January 2004 05:51, katarn wrote: > Hi, > > I would like to know opinions about which approach is better: > > Having a table with a field that works as a unique key, or having > several fks that work as a combined key ( all the fks fields )? Depends on the particular situation, you'll need to give details of the tables and their place in your system. There are two reasons I've seen given for using an artificial (substitute) primary key: 1. It's "lighter" than several other fields (especially where they are text) 2. The natural primary key has meaning to the users, and the users will tend to get it wrong. The second is probably the more persuasive - the first can definitely have costs as well as benefits. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Select into
On Monday 12 January 2004 01:09, beyaRecords - The home Urban music wrote: > Hi, > I want to copy a sequence of rows from one table into another table. I > have tried select into but this will only work if the table you are > copying to does not already exist. How do I copy to a table that does > already exist? Something like: INSERT INTO table_a (a,b,c) SELECT d,e,f FROM table_b WHERE f=1; You could replace any of the columns in the SELECT by an expression if you wanted to. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Insert into
Hi,
i've structure table like below and want to load data from temp table into table below :
dwnc=> \d cust_lo_dim
Table "biosadm.cust_lo_dim"
Column | Type | Modifiers
---+---+--
lo_key | integer | not null default nextval('clo_seq'::text)
lo_no | character varying(25) | not null
lo_date | date |
rcvdate | date |
lo_status | character varying(15) |
Indexes: cust_lo_dim_pkey primary key btree (lo_key)
my temp table as below:
dwnc=> \d custlo_temp;
Table "biosadm.custlo_temp"
Column | Type | Modifiers
--+---+---
lono | text |
lodate | text |
rcvdate | text |
loamount | numeric(10,2) |
custname | text |
status | text |
My SELECT STATEMENT :
dwnc=> insert into cust_lo_dim
dwnc-> (lo_no,lo_date,rcvdate,lo_status)
dwnc-> select c.lono,c.lodate,c.rcvdate,c.status
dwnc-> from custlo_temp c ;
ERROR: column "lo_date" is of type date but _expression_ is of type text
You will need to rewrite or cast the _expression_
Questions :
1) How to rewrite /cast the _expression_ above ??? same goes to others column .
2) lo_key is the column which values comes from sequence clo_seq. what should i do first b4 insert into cust_lo_dim
i appreciate for the help. TQ
