[GENERAL] Does PostgreSQL support EXISTS?
The Subject says its all. --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 4: Don't 'kill -9' the postmaster
[GENERAL] Thread or not threads?
I have 4 columns in a table, id, sub_id, timestamp and value. The primary key is id, sub_id and timestamp combine. I need to insert many rows (may be 10 thousands every 4 minutes) as fast as I can to the same host, same port, same database, same table. A. Open only one JDBC (Java Database Connective) connection, have multiple threads (similar to UNIX child process) to do the insert. Note, too many threads will cause the system out of memory! B. Open only one JDBC connection, have only one single thread to do the insert. C. Open multiple JDBC connections threads, each one of them handle the data insert. D. Please tell me your way, the much better way. Currently I am doing A. I have to limited the number of threads that won't cause the system out of memory. But there is big bottleneck there. The UNIX system scheduling can only do one insert a time (I think). I am wonder choose C will be better? Tell me the D. Thank you very much in advance! P.S. The database server is PostgreSQL 7.x. and the UNIX box is Redhat Linux 6.5. -- Why we want to teach our babies to talk and walk, then later we tell them sit down!, be quiet! ? Democracy is not a better way for a solution, it is just another way to spread the blames. --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
[GENERAL] Re: Does PostgreSQL support Constant Expression (Alias Name)?
Thank you very much to all helped me. You save me a lot of time. (*^_^*) 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] store procedure in pl/pgsql
Juan Ramn Cortabitarte wrote: Hello, I'm trying to do some store procedure in pl/pgsql but the sql server says me: Error: ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the created procedural languages. Im using Red Hat Linux 7.0 and Postgress 7.02. i executed: #createlang plpgsql dbhtc createlang: missing required argument PGLIB directory createlang --username=postgres --dbname=yourdb --pglib=/usr/lib/pgsql plpgsql yourdb If you don't see plpgsql.so in PGLIB directory, then you better re-install your PostgreSQL. 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] PL/PGSQL Programming Guide/Reference Guide Want
Please tell me where and how I get the PL/PGSQL Programming Guide and Reference Guide? Because the online documentation pages are not enough examples. Thank you very much! -- Why we want to teach our babies to talk and walk, then later we tell them "sit down!", "be quiet!" ? Democracy is not a better way for a solution, it is just another way to spread the blames. --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
[GENERAL] Row ID and auto-increment?
If I create a table like create table tablename ( aNuminteger not null, namevarchar(10) ); If I do select * from tablename; q1. Is there such thing rowid similar to Oracle in PostgreSQL? q2. How do I make aNum auto increment by 1? Need to write a trigger? how to write that? I want to enforce column aNum 0,1,2,.n. I want to prevent data entry people input 0,1,4,5,8,...n. Thank you very much in advance! --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
[GENERAL] Re: How to make PostgreSQL JDBC drive get PGTZ?
I solved this problem by execute a SQL statement below after JDBC connection. SET TIME ZONE 'GMT'; --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
[GENERAL] Re: Unrecognized language plpgsql when CREATE FUNCTION?!
Be default PL/PGSQL is not installed in each database. You must type createlang --host=hostname --port=5432 --username=postgres --dbname=yourdb --pglib=$PGLIB plpgsql yourdb To verify type createlang -l yourdb --Raymond begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;home:ICQ #: 16722494 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 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, W/OH2=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;30256 fn:Raymond Chui end:vcard
[GENERAL] ON DELETE CASCADE and TRIGGER
I have three tables: CREATE TABLE table1 ( idchar(8) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE table2 ( idchar(8) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES table1 (id) ON DELETE CASCADE ); CREATE TABLE table3 ( idchar(8) NOT NULL, codechar(2) NOT NULL, ordersinteger NOT NULL, PRIMARY KEY (id,code,orders), FOREIGN KEY (id) REFERENCES table2 (id) ON DELETE CASCADE ); Now you can see I must insert a row in table1 1st, then insert a row in table2, then insert the rowS in table3. That is OK Now I want to delete an id in all three tables. How can I delete a row in table1 trigger to delete rows in table2, table3? Since I can't put "ON DELETE CASCADE" for PRIMARY KEY in table1. Now I can only delete a row in table2 which trigger to delete rowS in table3. If I want to CREATE TRIGGER BEFORE DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE table1_trigger(arg); What I suppose pass to the arg in table1_trigger()? Thank you very much in advance! -- Why we want to teach our babies to talk and walk, then later we tell them "sit down!", "be quiet!" ? Democracy is not a better way for a solution, it is just another way to spread the blames. --Raymond begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;home:ICQ #: 16722494 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 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, W/OH2=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;30256 fn:Raymond Chui end:vcard
[GENERAL] Why pg_dump doesn't dump the foriegn keys?
I do pg_dump -u -s dbname db_schema I look at that db_schema file, I only see the primary keys but no foreign keys. I don't understand what is OID use for. Will -o option dump the foreign keys for me? Thank you! -- Why we want to teach our babies to talk and walk, then later we tell them "sit down!", "be quiet!" ? Democracy is not a better way for a solution, it is just another way to spread the blames. --Raymond begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;home:ICQ #: 16722494 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 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, W/OH2=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;30256 fn:Raymond Chui end:vcard
[GENERAL] TODAY and CURRENT?
I have a table with columns datetime timestamp, valuefloat(8) I want to delete rows 10 days older or 10 hours older by delete from tablename where datetime TODAY-10; or delete from tablename where datetime between CURRENT-10 and CURRENT; So are there key words TODAY, CURRENT in PostgreSQL? Thank you! begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;home:ICQ #: 16722494 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 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, W/OH2=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;30256 fn:Raymond Chui end:vcard
[GENERAL] How to import/export data from/to an ASCII file?
For example create table testTable ( id integer, name char(20) ); an ASCII file format with field separator "|" is 1|Hello| 2|Again| .. There is a way to do this in Oracle, Sybase, Informix and MySQL. But I want to know how to do this in PostgreSQL. Please don't tell me use pg_dump, because it is not a correct answer for my question! Thank you! begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;home:ICQ #: 16722494 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 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, W/OH2=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;30256 fn:Raymond Chui end:vcard
