Re: [GENERAL] Public Database of zip code information
i believe that you can get that info from www.allstats.com. we get our zipcode info from them quarterly. mikeo At 05:03 PM 9/20/00 -0700, Adam Haberlach wrote: Sometime in the past year, someone mentioned a database that contained zipcode, lat/long, and city information--does anyone else remember this, or should I check into a drug clinic? I'm pretty much looking for a way to correlate zip, city, and/or geographic locations. -- Adam Haberlach| A billion hours ago, human life appeared on [EMAIL PROTECTED] | earth. A billion minutes ago, Christianity http://www.newsnipple.com | emerged. A billion Coca-Colas ago was '88 EX500 | yesterday morning. -1996 Coca-Cola Ann. Rpt.
[GENERAL] Cache lookup for language failed 17533070
hi, can anyone help me on this?
tig3prod=# update cust set agt_id = 'DEFAULT';
sorry to resend this but with the mail-list problems i haven't
seen it and don't know if anyone else has either...
ERROR: fmgr_info: Cache lookup for language failed 17533070
tig3prod=#
my guess is something's missing. might anyone help me out
with a suggestion on where to look?
tig3prod=# \d cust
Table "cust"
Attribute |Type | Modifier
---+-+--
cust_id | varchar(15) | not null
cut_id| varchar(15) | not null
cust_name | varchar(50) | not null
cust_division | varchar(6) |
cust_svc_start_dt | date| not null default now()
cust_svc_end_dt | date|
cust_valid| char(1) | not null default 'Y'
cust_bill_loc_id | varchar(6) | not null
wu_id | varchar(10) | not null default 'TEST'
cust_timestamp| timestamp | not null default now()
agt_id| varchar(10) | default 'DEFAULT'
rse_id| integer |
bd_id | varchar(6) | not null
cust_email| varchar(50) |
cust_stream | integer | default nextval('cust_stream_seq'::text)
br_cycle | integer |
cust_qr_reports | varchar(20) |
cust_qr_sent | timestamp |
TIA,
mikeo
Re: [GENERAL] hidden data fields
you could "hide" or mask columns through views... mikeo At 02:10 PM 8/22/00 -0700, Mike Sears wrote: >>>> In mysql you can make some data apear to be garbled or hiddne, I'm wondering if this can be done using psql?
Re: [GENERAL] Field names
try this : select attname from pg_attribute where attrelid = (select oid from pg_class where relname = 'your table name') order by attnum; you can further develop a query from this... HTH At 12:14 PM 8/18/00 -0700, Mitch Vincent wrote: I know this has been asked and answered before but I can't seem to find the answer anywhere.. I would like to get the names of the fields in a table (not the values) in a query. That's possible with PostgreSQL, isn't it? Thanks! -Mitch
Re: [GENERAL] adding column constraint
alter table cust add constraint fk_cust_bd_id foreign key (bd_id) references bill_dist (bd_id); At 12:57 PM 7/26/00 -0700, Timothy H. Keitt wrote: Can someone give an example of how to add a foreign key constraint to an existing table? (Pgsql and the man page differ and neither syntax seems to work.) I've tried: alter table mytable add constraint col foreign key references reftable with no luck. Tim -- Timothy H. Keitt National Center for Ecological Analysis and Synthesis 735 State Street, Suite 300, Santa Barbara, CA 93101 Phone: 805-892-2519, FAX: 805-892-2510 http://www.nceas.ucsb.edu/~keitt/
[GENERAL] drop foreign key
hello, after one has put a foreign key constraint on a table, how can one drop that constraint??? mikeo
Re: [GENERAL] adding column constraint
almost, ALTER TABLE cust# the table to be alteredyes ADD CONSTRAINT fk_cust_bd_id # name of the constraint (see tgconstrname column in pg_trigger) FOREIGN KEY (bd_id) # column in cust to be FK'd to bill_dist REFERENCES bill_dist (bd_id); # specifies the column in bill_dist to use? yes mikeo At 02:26 PM 7/26/00 -0700, Timothy H. Keitt wrote: Hmmm... I got it to work, but using a slightly different syntax. Let me see if I understand your example: ALTER TABLE cust# the table to be altered ADD CONSTRAINT fk_cust_bd_id # the column in cust to add the constraint? FOREIGN KEY (bd_id) # foreign key in bill_dist? REFERENCES bill_dist (bd_id); # specifies the column in bill_dist to use? Oddly, I put the foreign table name in place of fk_cust_bd_id and the local column name where you have (bd_id). It seemed to work. Is the identifier after ADD CONSTRAINT a noop? BTW, the problem with the example in the man page is that its impossible to tell which identifiers are table names and which are column names and which column names go with which table, etc. The format above (with comments) would help a lot. (Or use identifiers like the_table_to_be_altered and so on.) Tim mikeo wrote: alter table cust add constraint fk_cust_bd_id foreign key (bd_id) references bill_dist (bd_id); At 12:57 PM 7/26/00 -0700, Timothy H. Keitt wrote: Can someone give an example of how to add a foreign key constraint to an existing table? (Pgsql and the man page differ and neither syntax seems to work.) I've tried: alter table mytable add constraint col foreign key references reftable with no luck. Tim -- Timothy H. Keitt National Center for Ecological Analysis and Synthesis 735 State Street, Suite 300, Santa Barbara, CA 93101 Phone: 805-892-2519, FAX: 805-892-2510 http://www.nceas.ucsb.edu/~keitt/ -- Timothy H. Keitt National Center for Ecological Analysis and Synthesis 735 State Street, Suite 300, Santa Barbara, CA 93101 Phone: 805-892-2519, FAX: 805-892-2510 http://www.nceas.ucsb.edu/~keitt/
[GENERAL] can disks be read only?
hi, we wish to have a database that is history so we want to make the disks read only after loading. would this cause any problems with POSTGRES? it's on a linux 6.2 box with POSTGRES 7.0.2. what we're looking for here is that if the system crashes we can do a fast boot and not have the system do an FSCK. one company bought another and we're going to warehouse the purchased company's data for reference. they'll be no additions to it once loaded. thanks, mikeo
[GENERAL] foreign key lost
hi, i accidently deleted a foreign key trigger from pg_trigger and now cannot get at the table. i get the message: ERROR: RelationBuildTriggers: 1 record(s) not found for rel feature_code can anyone give me a shovel :) or a little help to get out of this please? TIA, mikeo
Re: [GENERAL] foreign key lost
thanks, i just discovered that before i got your message. now that i have that shovel, maybe i should whack myself with it!!! :) thanks again, mikeo At 02:34 PM 7/7/00 -0400, Tom Lane wrote: mikeo [EMAIL PROTECTED] writes: hi, i accidently deleted a foreign key trigger from pg_trigger Did you reach into pg_trigger and delete the record yourself, or did a system bug cause the problem? and now cannot get at the table. i get the message: ERROR: RelationBuildTriggers: 1 record(s) not found for rel feature_code can anyone give me a shovel :) or a little help to get out of this please? You'll need to tweak the table's pg_class entry so that its reltriggers column shows the same number of triggers that are actually there for it in pg_trigger. A quick "UPDATE pg_class" should get you out of it. regards, tom lane
[GENERAL] pg_shadow constraint ?
hi, i have a question about pg_shadow and constraints on it. someone left our employ and i did a dropuser on his id. later, when i did a pg_dump none of the tables created by him were dumped. i added him back thru createuser so that i could do a valid pg_dump. i tried to create a unique index on pg_shadow on usesysid with the intention of creating a foreign key from pg_class to the shadow table to save me from myself. :) create unique index usesysid_idx on pg_shadow(usesysid); CREATE then i attempt to create a foreign key from pg_class: alter table pg_class add constraint fk_relowner foreign key (relowner) references pg_shadow(usesysid); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: Index 'pg_shadow_name_index' does not exist so i do a \d on pg_shadow: testx=# \d pg_shadow ERROR: Index 'pg_shadow_name_index' does not exist there were no indexes on pg_shadow when i started and i don't know why i would be getting this index error message after creating a unique index. i'm guessing that i cannot create such an index on pg_shadow because it causes some sort of internal problems. what i wanted to do was create a "STOP" for attempting to drop a user if that user still owns objects? with a lot of databases i thought it'd be easier for the system to tell me if such a situation existed than my searching through for that info. any suggestions would be welcome! mikeo
Re: [GENERAL] responses to licensing discussion
At 02:19 PM 7/5/00 -0500, [EMAIL PROTECTED] wrote: Jan Wieck wrote: I'm in doubt why none of the other open source projects ever felt the need to enforce license agreement in this way while most commercial players do. Maybe it's something we don't have to worry about, but what if so? What if we all have already one foot in jail and just don't know? This is exactly the the kind of sentiment that the UCITA proponents sought to make as widespread as possible. Oh boy, what about all the patches, modules, whatnot I contributed to other open source projects during the past 20 years? Can I sleep well tonight? They thought about that, too. UCITA is designed to be applied retroactively, so you can sleep well knowing that there's nothing you can do to prevent the Maryland residents from suing you for the damages they suffered from your code over the last 20 years. Now if it is true that the UCITA was meant to be a weapon of intimidation, it seems to have started working: everybody is at least concerned, if not scared. But it definitely goes overboard with its retroactive capability, which actually makes it less intimidating: what's the use in worrying about the future if we all have one foot in jail because of our deeds in the past? Back to work, folks ... --Gene not being from maryland but, i would think that the constitution's prohibition against ex post facto laws would prevent retro-active applications of laws, if the usa actually followed the constitution; but that's another topic... mikeo
[GENERAL] trigger question
hi,
i've created a function as follows:
drop function rates_hist_function();
CREATE function rates_hist_function()
returns opaque
as 'BEGIN
if ( old.rt_valid ''P'' or new.rt_valid not in
(''Y'',''N''))
then
new.rt_timestamp = now();
insert into rates_hist values (
new.cut_id,new.ct_key,new.rtm_id,new.rt_sell_factor,
new.rt_sell_msg_cost,new.rt_sell_init_sec,new.rt_sell_init_cost,
new.rt_sell_addl_sec,new.rt_sell_addl_cost,new.rt_buy_factor,
new.rt_buy_msg_cost,new.rt_buy_init_sec,new.rt_buy_init_cost,
new.rt_buy_addl_sec,new.rt_buy_addl_cost,new.rt_valid,new.rse_id,
new.wu_id,new.rt_timestamp, new.rt_usoc_def_factor
);
end if;
return new;
END;'
language 'plpgsql';
and i call it from this trigger:
drop TRIGGER rates_hist_trigger on rates;
CREATE TRIGGER rates_hist_trigger
after insert or update on rates
for each row
execute procedure rates_hist_function();
when i attempt to insert a row into the rates table using this statement:
insert into rates(cut_id,ct_key,rt_valid,...,rt_timestamp,rt_usoc_def_factor)
values ('mikeo',123456,'x',...,now(),1.35);
i get this error:
ERROR: record old is unassigned yet
since this trigger is for both insert or update, why does it expect the "OLD"
value to already exist, as it would not for insert? second, is there a way
to tell a function that it's inserting or updating, as in oracle's
"if updating..."?
thanks,
mikeo
[GENERAL] does vacuum rebuild index?
hi, we have a 34 million row table. after many inserts, updates, deletes the performance degraded so we vacuumed the table. the output indicated that the index was also vacuumed but the size didn't change. it was still 80m so we dropped and recreated it reducing it to 20m. no big deal but is it common for a vacuum to "miss" the index, so to speak? i saw that someone else asked a similar question on 6/4 in the general archives but i found no reply to it. is this a common occurrence or am i doing something wrong or maybe should be doing something different? this is a 7 column index on an 11 column table. xxx1 | varchar(15) | xxx2 | bigint | xxx3 | varchar(15) | xxx4 | varchar(15) | xxx5 | bigint | xxx6 | float8 | xxx7 | float8 | xxx8 | float8 | xxx9 | date| xxx10| integer | xxx11| bigint | the index is on columns 1-5,10,11 thanks, mikeo
Re: [GENERAL] Re: [SQL] oracle rownum equivalent?
At 06:47 PM 6/7/00 -0400, Cary O'Brien wrote: thanks for the response. oid is equivalent to oracle rowid. I think there is a fundamentel difference between oid and rownum. Oid is just a serial number. Rownum is a long string that tells oracle where exactly the row is. So *I think* rownum can be used for fast lookups, where oid, unless indexed, can't. Other than that they are the same pretty much. -- cary i'll have to respectfully disagree with you on your interpretation of rownum. in oracle, rownum tells only the relative position of a row in a result set. also, it is an integer value starting at 1 up to nrows retrieved and is used, for the most part, to limit the result set and not for fast lookups. it also can be used in a DML statement within a function, such as mod(), to aid in generating a value. the point here is moot though, as postgres doesn't have an equivalent and i'll have to learn to live without that small piece of oracle and enjoy what i see as the greater benefits of postgres. :) mikeo
[GENERAL] TOP SESSIONS?
hi, in oracle you would use these two cursors to determine who was connected and what they were doing. select distinct s.sid sid, s.serial# serial, s.status status, osuser, spid , count(o.sid) counter, s.username username, s.program program, sql_address from v$session s, v$open_cursor o, v$process p where s.sid = o.sid(+) and paddr = addr group by s.sid,s.serial#, s.status , osuser, spid ,s.username, s.program ,sql_address order by 1,3 select distinct name from sys.audit_actions, v$sqltext where address = ? and action = command_type does anyone know what tables in postgres would give me the same or similar information? TIA, mikeo
[GENERAL] line type
hi, we're looking at migrating from ORACLE to postgres in the very near future and we've run into a small problem. there's a data type defined "LINE". we have named one of our tables as "LINE" also and it would require a great deal of code changes to rename that table. is it possible to simply "turn off" the line type? any help is appreciated. thanks, mikeo
