[SQL] How to dump views definition in one schema?
Good morning, Can pg_dump or other command could dump all views definition in one schema. I was trying to alter column types for several tables, and I have very complicated view dependencies. What I try to do is: (1). Back all views def (2). alter columns (3). re-create views from (1) I'd like to know how to dump all views in one schema? Thanks, -- Lu Ying -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] update from join
I know I should be able to do this but my brain's mashed today I have a stock table with s_stock_no varchar primary key s_vin varchar s_updated timestamp s_supercededboolean It is possible for the same vin to exist on stock if we have sold and then bought back a vehicle, e.g. as a part exchange. Every time a vehicle is inserted/updated the s_updated field is update. How can I update the table so that for each s_vin, if a record does not have the most recent s_updated value, s_superceded is set to true? I can get the most recent value by running: select * from (select s_vin, count(s_updated) as numb, max(s_updated)::timestamp as latest from stock group by s_vin) foo where numb > 1; but I can't seem to get how I can convert this to an update statement. The num > 1 simply removed all vehicles with only one record. I seem to think I need an update. from. statement -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] update from join
I wonder if this works: update stock s set s_superceded = true where s.s_updated < (select max(t.s_updated) from stock t where t.s_vin = s.s_vin) On Thu, May 14, 2009 at 7:27 AM, Gary Stainburn < [email protected]> wrote: > I know I should be able to do this but my brain's mashed today > > I have a stock table with > > s_stock_no varchar primary key > s_vin varchar > s_updated timestamp > s_supercededboolean > > It is possible for the same vin to exist on stock if we have sold and then > bought back a vehicle, e.g. as a part exchange. > > Every time a vehicle is inserted/updated the s_updated field is update. > > How can I update the table so that for each s_vin, if a record does not > have > the most recent s_updated value, s_superceded is set to true? > > I can get the most recent value by running: > > select * from (select s_vin, > count(s_updated) as numb, > max(s_updated)::timestamp as latest > from stock > group by s_vin) foo > where numb > 1; > > > but I can't seem to get how I can convert this to an update statement. The > num > > 1 simply removed all vehicles with only one record. > > I seem to think I need an update. from. statement > > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] alter column from varchar(32) to varchar(255) without view re-creation
Emi Lu wrote: PostgreSQL 8.0.15. Is there a way that I can easily alter column type from varchar(32) to varchar(255) but do not have to worry about views dependent on it? You should test it carefully and it is considered a bad practice - I'll probably get sued for recommending this :-), but you may try: SELECT * from pg_attribute where attname = 'colname' and attrelid = (SELECT oid FROM pg_class WHERE relname='_tablename'); UPDATE pg_attribute set atttypmod = 4+_new_length_in_your_case_255 where attrelid = _attrelid_from_above_ and attname = 'colname'; Tried this, it worked for table but not view. E.g., T1(col1 varchar(64)... ); create view v1 as select * from T1; update pg_attribute set atttypmod = 4+ 128 where \d T1 col1 varchar(128) == [OK] \d v1 col1 varchar(64) == [did not change?] So, it does not really update all dependencies? Thanks a lot! -- Lu Ying -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
