[SQL] When PostgreSQL compliant JDBC 2.0?
Currently the JDBC drive in PostgreSQL 7.0, 7.1 are not support batch updates. I am wonder anybody know is there other PostgreSQL JDBC drive support batch updates? Thanks! --Raymond begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;work:(301)713-0624 Ext. 168 x-mozilla-html:TRUE url:http://members.xoom.com/rchui/ org:NWS, NOAA version:2.1 email;internet:[EMAIL PROTECTED] title:SA, DBA note:ICQ #: 16722494 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, OH=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;-6384 fn:Raymond Chui end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Why lost all foreign key constraits after cluster?
After I performed cluster indexname on tablename; All the foreign key constraints are lost on that table, why is that? I get messages like NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "tablename" during cluster I don't want to lost the foreign key constraints on the table. How do I prevent that? Thank you! --Raymond begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;work:(301)713-0624 Ext. 168 x-mozilla-html:TRUE url:http://members.xoom.com/rchui/ org:NWS, NOAA version:2.1 email;internet:[EMAIL PROTECTED] title:SA, DBA note:ICQ #: 16722494 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, OH=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;-6384 fn:Raymond Chui end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Mirroring the database?
Does the latest PostgreSQL 7.1.2 support database mirroring? I have machine A, B and C, they all have the same database and tables. Machine A is the operational machine, machine B and C are backup. If users do INSERT, UPDATE and DELETE in machine A, I want have the same SQL statements in machine B and C. 1. I can do pg_dump or COPY every hour. 2. I can use PerlDBI or JDBC open multiple connections for each SQL statement. 3. But I prefer if the system auto mirroring the database, then I can do nothing. All I need is set the auto mirroring configure. Please tell me how to do in 3. above. Thank you in advance! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Where is pgplsql.so located in 7.1.2?
When I do createlang -U postgres -d mydbname pgplsql mydbname I got can't find /usr/share/pgsql/plpgsql.so error message. But there is no plpgsql.so file in /usr/share/pgsql directory! I just installed RPM binary 7.1.2 for Redhat 6.2 In 7.0 the pgplsql.so file is located in /usr/lib/pgsql/ So I do createlang -U postgres -d mydbname --pglib=/usr/lib/pgsql pgplsql mydbname Then I got A function named plpgsql_call_handler already exists. Installation abort. But createlang -l mydbname shows there is nothing there. Shall I do drop function plpgsql_call_handler; ? Or where I can find plpgsql.so file for 7.1.2 in /usr/share/pgsql/ ? Please don't tell me I need to compile PostgreSQL by myself order to get all *.so files. --Raymond ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] The numbers are not match in 7.1.2
When I do select * from pg_database; I got datname | ... | datlastsysoid mydbname | ... | 18539 But When I cd $PGDATA/data/base/ ls -la I got 1 18719 18725 Where is 18539 ??!! I think mydbname is 18725 When I cd 18725 ls There are a lot of numbers. How do I figure out which number is which table_name? BTW, what is/are the advantage(s) used the numbers instead real text name like 7.0.x used to do? --Raymond ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to execute a system file in procedure?
I would like execute a system command or my program or my shell script in procedure. How do I do that? For example, CREATE FUNCTION myname() RETURN OPAQUE AS ' BEGIN IF count(NEW.aColumn) >= 600 THEN RAISE EXCEPTION ''Hello, it is time to re-index the table.''; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER AFTER INSERT OR UPDATE ON mytable FOR EACH ROW EXECUTE PROCEDURE myname(); But I prefer to execute a system command like mail mail -s "Re-Index" [EMAIL PROTECTED] < `echo "It is time to re-index the table"` ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] CURRENT_TIMSTAMP
I created a column, dada type timstamp with time zone
and with default CURRENT_TIMSTAMP
it shows me the default is
default ('now'::text)::timstamp(6) with time zone
Then when I insert a row, the default timestamp value is
-mm-dd HH:MM:ss.m+00
where m is milliseconds.
How do I make default only -mm-dd HH:MM:ss+00 ?
Thank Q!
begin:vcard
n:Chui;Raymond
tel;work:301-713-0640 x168
x-mozilla-html:FALSE
url:http://www.nws.noaa.gov/
org:NWS, NOAA;OHD13
version:2.1
email;internet:[EMAIL PROTECTED]
title:CS
adr;quoted-printable:;;1325 East-West Highway=0D=0ASSMC2, Room 8112;Silver Spring;MD;20910-3282;U.S.A.
fn:Raymond Chui
end:vcard
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Timestamp problem
I am use PostgreSQL 7.2.1 in Redhat Linux 7.2 Java 1.4.x When I do JDBC String sql = "SELECT datetime_column FROM mytable"; Timestamp ts = resultSet.getTimestamp(2); If the timestamp return format like 2003-07-15 13:20:00.20+00 then is OK. But if timestamp return like 2003-07-15 13:20:00.421+00 Then I got SQLException Bad Timestamp Format at 23 in 2003-07-14 14:45:00.421+00 What is 23? How do I resolve this problem? Thank Q! -- Raymond Chui NWS at NOAA 301-713-0640 ext 168 ---(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
[SQL] Why table has drop, but the foreign key still there?
Here are the simple things I did
create table state (
state_code char(2) not null,
state varchar(15) not null,
primary key (state_code)
);
create table whitepage (
user_id char(8) not null,
email varchar(50),
telephone char(16) not null,
contact_namevarchar(30) not null,
cityvarchar(20),
state_code char(2),
primary key (user_id),
foreign key (state_code) references state (state_code)
);
insert into state (state_code,state) values ('GU','Guam');
drop table whitepage;
delete from state where state_code = 'GU';
ERROR: Relation "whitepage" does not exist
Why I got this error message??!!
The whitepage table already drop. When the table drop, will the
foreign key constraint also drop??
Now, order for me delete a row from state table, I HAVE TO
re-create whitepage table. That's silly!!!
What shall I do? How do I delete a row in state without
re-create the whitepage table???
Thank you very much in advance!
---(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
