[SQL] alter column from varchar(32) to varchar(255) without view re-creation
Good morning, I am using 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? If Psql could support column alter without views' dependencies, that will be great! 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
Re: [SQL] alter column from varchar(32) to varchar(255) without view re-creation
On Tue, Feb 24, 2009 at 4:27 PM, Emi Lu wrote: > Good morning, > > I am using 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'; Regards, Marcin -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Best practices for geo-spatial city name searches?
Hello, I use PostgreSQL and the "cube" type to perform geo-spatial zipcode proximity searches. I'm wondering about the best practices also supporting a geo-spatial distance search based on a city name rather than zipcode. In our original data model, we used a 'zipcodes' table, with the zipcode as the primary key. This can of course contain a "City Name" column, but there is a problem with this, illustrated a "Nome, Alaska" case. Nome's zipcode is 99762. It maps to multiple cities including Diomede, Alaska and Nome, Alaska. In the data model described, only the "Diomede" row is imported, and the other rows, including the "Nome, Alaska" row are dropped. So if you try to search for Nome, Alaska, you won't find anything. One solution would be to have a "cities" table, with the city/state as the primary key, and a zipcode as an additional column. Then, by joining on the zipcodes table, the coordinates for a city could be found. Is there any other way I should be considering data modelling to support searches on zipcodes and cities? Thanks! Mark -- . . . . . . . . . . . . . . . . . . . . . . . . . . . Mark StosbergPrincipal Developer [email protected] Summersault, LLC 765-939-9301 ext 202 database driven websites . . . . . http://www.summersault.com/ . . . . . . . . -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Best practices for geo-spatial city name searches?
On Tuesday 24 February 2009 08:19:56 am Mark Stosberg wrote: > Hello, > > I use PostgreSQL and the "cube" type to perform geo-spatial zipcode > proximity searches. I'm wondering about the best practices also supporting > a geo-spatial distance search based on a city name rather than zipcode. > > In our original data model, we used a 'zipcodes' table, with the zipcode as > the primary key. This can of course contain a "City Name" column, but > there is a problem with this, illustrated a "Nome, Alaska" case. Nome's > zipcode is 99762. It maps to multiple cities including Diomede, Alaska and > Nome, Alaska. > > In the data model described, only the "Diomede" row is imported, and the > other rows, including the "Nome, Alaska" row are dropped. So if you try to > search for Nome, Alaska, you won't find anything. > > One solution would be to have a "cities" table, with the city/state as the > primary key, and a zipcode as an additional column. Then, by joining on the > zipcodes table, the coordinates for a city could be found. > > Is there any other way I should be considering data modelling to support > searches on zipcodes and cities? > > Thanks! > > Mark > > > -- > . . . . . . . . . . . . . . . . . . . . . . . . . . . >Mark StosbergPrincipal Developer >[email protected] Summersault, LLC >765-939-9301 ext 202 database driven websites > . . . . . http://www.summersault.com/ . . . . . . . . I don't know if this is any help. I recently used google to obtain the longitude and latitude and then used simple math to determine the distance between the locations to determine proximity searches. Like finding the closes store. -- John Fabiani -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Best practices for geo-spatial city name searches?
> -Mensaje original- > From: Mark Stosberg > > Hello, > > I use PostgreSQL and the "cube" type to perform geo-spatial > zipcode proximity searches. I'm wondering about the best > practices also supporting a geo-spatial distance search based > on a city name rather than zipcode. > > In our original data model, we used a 'zipcodes' table, with > the zipcode as the primary key. This can of course contain a > "City Name" column, but there is a problem with this, > illustrated a "Nome, Alaska" case. Nome's zipcode is 99762. > It maps to multiple cities including Diomede, Alaska and Nome, Alaska. > > In the data model described, only the "Diomede" row is > imported, and the other rows, including the "Nome, Alaska" > row are dropped. So if you try to search for Nome, Alaska, > you won't find anything. > > One solution would be to have a "cities" table, with the > city/state as the primary key, and a zipcode as an additional > column. Then, by joining on the zipcodes table, the > coordinates for a city could be found. > > Is there any other way I should be considering data modelling > to support searches on zipcodes and cities? > You absolutely need zipcode as a primary key? If you must enforce non duplicate entries use country + state + county + city_name instead. You might still need to throw zipcode into the PK for certain cities (worldwide). Otherwise, latitud & longitude provide a better natural key, or simply use a non data related sequential bigint. Regards. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Add column by using SELECT statement
Hi all, I was wondering how I can add a column and populate it by some query. For example: TblA (Id, fld1) TblB(Id, fld1, fld2) I have a query: SELECT b.fld2 FROM tblB b WHERE condition1 what I want to do is add a column in tblA: fld2 and polpulate the newly added field with the query on tblA.Id=tblB.Id Any advice? Any input would be much appreciated. Thanks a lot John
Re: [SQL] Add column by using SELECT statement
In response to John Zhang : > Hi all, > > I was wondering how I can add a column and populate it by some query. > > For example: > TblA (Id, fld1) > TblB(Id, fld1, fld2) > > I have a query: > SELECT b.fld2 > FROM tblB b > WHERE condition1 > > what I want to do is add a column in tblA: fld2 > and polpulate the newly added field with the query > on tblA.Id=tblB.Id > > Any advice? Any input would be much appreciated. ALTER TABLE and UPDATE: test=# create table tabla (id int, f1 int); CREATE TABLE test=*# create table tablb (id int, f1 int, f2 int); CREATE TABLE test=*# insert into tabla values (1,1); INSERT 0 1 test=*# insert into tabla values (2,2); INSERT 0 1 test=*# insert into tabla values (3,3); INSERT 0 1 test=*# insert into tablb values (1,1,1); INSERT 0 1 test=*# insert into tablb values (2,2,2); INSERT 0 1 test=*# insert into tablb values (3,3,3); INSERT 0 1 test=*# alter table tabla add column f2 int; ALTER TABLE test=*# commit; COMMIT test=# update tabla set f2= tablb.f2 from tablb where tabla.id=tablb.id; UPDATE 3 test=*# select * from tabla; id | f1 | f2 ++ 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3 (3 rows) HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
