Re: [GENERAL] Problem with beta2 and shapshot ???
Dit you do an initdb as postgres? i.e. you have a: /usr/local/pgsql/data dir? Cheers Wim. - Oorspronkelijk bericht - Van: Greg Brzezinski [EMAIL PROTECTED] Aan: [EMAIL PROTECTED] Verzonden: Saturday, March 18, 2000 4:53 PM Onderwerp: [GENERAL] Problem with beta2 and shapshot ??? "make all" for Pgsql Beta2 and snapshot works fine. "make install" works fine but /usr/local/pgsql/bin/postmaster -S -i -D/var/lib/pgsql doesn't works... why...? --Greg--
Re: [GENERAL] select ... FROM ... WHERE .. IN (select ...) takes FOREVER... Can someone help me optimize this?
On Sat, Mar 18, 2000 at 09:34:37PM -0500, Chris Gokey wrote: I was hoping that someone could help me optimize this query. This takes FOREVER under PostgreSQL. select distinct data_center, center_long from de_center where de_center.de_id in ( SELECT distinct de_parameters.de_id FROM de_parameters WHERE de_parameters.topic = 'ATMOSPHERE') order by data_center; Not sure, but how about select data_center, center_long from de_center where exists ( SELECT * FROM de_parameters WHERE de_parameters.topic = 'ATMOSPHERE' and de_center.de_id = de_parameters.de_id ) order by data_center; ? (Wish I could test it first...) Cheers, Patrick
Re: [GENERAL] what is the most appropriate way to shutdown the postmaster?
- Original Message - From: Tatsuo Ishii [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, March 18, 2000 1:12 PM Subject: Re: [GENERAL] what is the most appropriate way to shutdown the postmaster? snip In 7.0 there will be a tool called "pg_ctl" that controls starting/restarting/shutting down postmaster. Moreover there will be "smart shutdown" mode in that postmaster waits untill all backends get logged off. Will postmaster also refuse to take connections once shutdown begins, and will it be possible to set a cut-off period, a la shutdown, after which all backends will be killed, e.g. pg_ctl --shutdown 10 (or whatever) postmaster stops accepting new connections. It has (say) 10 current connections in the next 10 minutes 8 connections log off. after 10 minutes, postmaster forcibly kills off the remaining processes. and exits. Yours, Moray
Re: [GENERAL] How to retrieve table definition in SQL
Hi, There are some system tables in any pg database which contain information about table/field names/types and descriptions. use -e with psql command, and look at the sql code when running /d commands. regards. Omid Omoomi From: Stan Jacobs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [GENERAL] How to retrieve table definition in SQL Date: Sun, 19 Mar 2000 03:40:43 -0800 (PST) Hi everyone, This probably isn't a Postgres-specific question, but I'm hoping that someone knows the answer to this off the top of their heads... :-) I'd like to retrieve the table definition via SQL. I'm using ColdFusion to access a PostgreSQL 6.5.3 database, and I'd like to retrieve the table info, field names/types/sizes, so that my Coldfusion page/script can dynamically build the html forms to edit the tables. Any ideas how to get to this in SQL? I have another C++ class which builds nice table headers with this info, but ColdFusion doesn't seem to do that with the returned data. Thanks! - Stan - __ Get Your Private, Free Email at http://www.hotmail.com
[GENERAL] Looking at pgsql
I'm a Sybase pmgmr by trade. I'm looking into creating an app on Linux. I don't have the $ for SAE so I'm looking at others. PGSQL has always been on my short list. Does it have the capability? Do you know of any sites that are dynamically generating pages from a PGSQL database? Can I interface JavaScript with pgsql. Do I need JDBC? Would I be better off using CGI? Are there any visual admin tools? How about other tools? Thanks for the help.
[GENERAL] Memory trouble
Hello I have some problem with memory leak using postgres 6.5 (?) on debian linux 2.1. I have writen following C program: #include "libpq-fe.h" #include stdio.h #include time.h PGconn* db; char d[1001]; char c[5000]; signed long r1,r2,r3,r4,r5; int i,p,q; PGresult* dbout; unsigned long er (unsigned long from,unsigned long to) {return (from+(to-from)*(float)rand()/RAND_MAX);}; int genchr (void) {do {p=(int)(40+82*(float)rand()/RAND_MAX);} while (((p41)(p48))||((p58)(p65))||((p90)(p97))); return (p);}; void main () {db = PQsetdb ("127.0.0.1","","","","Ttts"); for (q=1;q1000;q++) { r1 = er (1,30); for (i=1;i=r1;i++) {d[i-1]=genchr();}; d[r1]='\0'; sprintf (c,"select * from t1 where lower(c) ~~ lower ('%%%s%%') order by n1",d); dbout = PQexec (db,"BEGIN"); PQclear (dbout); dbout = PQexec (db,c); PQclear (dbout); dbout = PQexec (db,"END"); PQclear (dbout); }; PQfinish (db); } This program generates querys into db. This program is compiled with following settings: gcc test.c -I /usr/include/postgresql -lpq -lcrypt -o test -Wall Then I run postmaster (from postgres account): ./postmaster -i -p -D /var/postgres/data/ Then i run top and see that in system is 223308Kb of memory free, then i run test ... after test ends top indicates 103328Kb free, then i end the postmaster with CTRL C, top indicates 104132Kb free, then i end top disconnect from all consoles and again connect and run top the number is unchanged (+-50Kb). After reboot i have try it again, with the same result. I supposed that it is cache, but when i then start some stupid program which only want to allocate 20Kb this program fail on not enought memory. For this reason i think that it is not cache. But what is it then??? Where i'm making a mystake ??? Thanks for help. Ice Planet e-mail: [EMAIL PROTECTED]
[GENERAL] How to use bytea type?
Hi, Can somebody give me an example of how to insert a byte array for example { 12, 24 , 0, 3 , '\' , 40 } into a column declared as type bytea. Statements in psql is what I am looking forward to know Cheers, Radhesh Mohandas Residence: # Office: 341,#B,Rosedale Ave. # Jolley 541, Computer Science Dept. St.Louis,MO 63112 # One Brookings Drive,Washington University, St.Louis,MO - 63130. phone : 314-862-1418# 314-935-7543 Wealth is beyond what an individual can produce ??!!
[GENERAL] Performance Question ODBC vs C
Hi there, I have been doing some performance testing with ODBC and have found that ODBC is much slower than using C to call the Psql API I don't understand why the results are like this, I thought ODBC would slow things down a bit but not my much, all its doing in sending off SQL straight the server? Test Server: 486 50mhz, 16 mb Ram Client: P266, 32 mb Ram SQL: 1000 INSERT INTO's with quite a lot of fields. Read from a text input file ODBC Test Windows 98 running VB6 and the freeware ODBC driver (I forget the name) Using TCP/IP network. The VB program will do about 30 records per minute. C Test Linux C program, reads input file, calls API's. Run on Client PC so still communicates over the network. This setup will do 340 record per minute My only guess is that the following bottlenecks are in the system: VB6 code that reads the text file - unlikely, it run's very quick if you remove the db.Execute call. ODBC parsing, maybe Something crappy about Windows, more likely Any ideas? -- End of Martin's email. \0
Re: [GENERAL] How to retrieve table definition in SQL
This isn't quite what I'm looking for, though I can't run a script on that machine, so I need to retrieve it with an SQL query. Ie. "SELECT * from data_key_table_name_something"... *smile* Judging from the few responses so far, it doesn't sound like there's an easy way to do this. On Sun, 19 Mar 2000, omid omoomi wrote: Hi, There are some system tables in any pg database which contain information about table/field names/types and descriptions. use -e with psql command, and look at the sql code when running /d commands. regards. Omid Omoomi From: Stan Jacobs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [GENERAL] How to retrieve table definition in SQL Date: Sun, 19 Mar 2000 03:40:43 -0800 (PST) Hi everyone, This probably isn't a Postgres-specific question, but I'm hoping that someone knows the answer to this off the top of their heads... :-) I'd like to retrieve the table definition via SQL. I'm using ColdFusion to access a PostgreSQL 6.5.3 database, and I'd like to retrieve the table info, field names/types/sizes, so that my Coldfusion page/script can dynamically build the html forms to edit the tables. Any ideas how to get to this in SQL? I have another C++ class which builds nice table headers with this info, but ColdFusion doesn't seem to do that with the returned data. Thanks! - Stan - __ Get Your Private, Free Email at http://www.hotmail.com
[GENERAL] Results from delayed command
The list owner has approved your request. Here are the results: The following was not successfully removed from pgsql-general: [EMAIL PROTECTED] No matching addresses.
Re: [GENERAL] Re: [ADMIN] PostgreSQL Mailing Lists ...
Why am I the only one whose filters still work for all my lists, with a *very* simple rule of: :0: * ^TO_((pgsql-)?)general@(hub|postgresql).org $MAILDIR/folders/pg-general If someone wants to suggest to me what variable to change to set an 'X-Mailing-List' header, or some such, please feel free to do so ... I'm always open to ideas ... On Sun, 19 Mar 2000, Tatsuo Ishii wrote: Dear Hermit Hacker, wouldn't a "X-Mailing-List" header help these (pgsql) lists, to better process them with procmail and such? I agree with this. Marc, could you add something to header so that we could distinguish one from another lists? We used to have something like "[EMAIL PROTECTED]" in the sender field that was really usefull. -- Tatsuo Ishii Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
[GENERAL] C code for calculating edit distance
Hi, I just wanted to pass along the attached C routine for calculating the "edit" distance between two strings. Some others may find it useful. (I wrote this code because I needed to do a "fuzzy" join between two columns with small typographic errors between the corresponding keys.) Here's some output: select levenshtein_distance('test', 'texts'); levenshtein_distance 2 (1 row) Enjoy. 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/ /* Copyright (c) 2000 Timothy H. Keitt */ /* Licence: GPL version 2 or higher (see http://www.gnu.org/) */ #include "postgres.h" #define STATIC_SIZE 32 /* This must be changed if STATIC_SIZE is changed */ static int4 static_array[STATIC_SIZE][STATIC_SIZE] = { {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31}, {1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {5, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {8, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {9, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {11, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {12, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {13, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {15, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {16, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {17, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {18, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {19, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {20, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {21, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {22, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {23, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {24, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {25, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {26, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {28, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {29, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {30, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {31, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}}; /* Fast version for strings up to STATIC_SIZE characters */ int4 levenshtein_distance(text *s1, text *s2) { register int i, j, l, m, n, add, rows, columns; columns = VARSIZE(s1) - VARHDRSZ + 1; rows = VARSIZE(s2) - VARHDRSZ + 1; /* Use slower dynamically allocated version for larger strings */ if (columns STATIC_SIZE || rows STATIC_SIZE) return levenshtein_distance_dynamic(s1, s2); for (j=1; jrows; ++j) { for (i=1; icolumns; ++i) { if (VARDATA(s1)[i-1] == VARDATA(s2)[j-1]) add=0; else add=1; m = 1 + static_array[j-1][i]; l = 1 + static_array[j][i-1]; n = add + static_array[j-1][i-1]; static_array[j][i] = (m l ? (m n ? m : n): (l n ? l : n)); } /* next column (i)
Re: [GENERAL] what is the most appropriate way to shutdown thepostmaster?
[Cc'ed to hackers list] In 7.0 there will be a tool called "pg_ctl" that controls starting/restarting/shutting down postmaster. Moreover there will be "smart shutdown" mode in that postmaster waits untill all backends get logged off. Will postmaster also refuse to take connections once shutdown begins, Yes. and will it be possible to set a cut-off period, a la shutdown, after which all backends will be killed, e.g. Not possible in 7.0. However, it seems to be an interesting idea and I would try it after we shipp 7.0. (we are in the beta period of 7.0, it is not allowed to add new features). -- Tatsuo Ishii
Re: [GENERAL] Re: alter table
On Sun, Mar 12, 2000 at 05:57:50PM +0200, Raigo Lukk wrote: Hi alter table tmp add column last text; I had this same problem, turned out that PostgreSQL don't have this feature :-( Upgrade: ALTER TABLE tablename ADD COLUMN columnname columntype Has been a feature since version 6.5.X, at least. So only way is: DROP TABLE and then again CREATE TABLE with all the fields you need. This is still needed for DROP COLUMN but not ADD COLUMN. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Performance Question ODBC vs C
On Sun, 19 Mar 2000, martin wrote: Hi there, I have been doing some performance testing with ODBC and have found that ODBC is much slower than using C to call the Psql API I don't understand why the results are like this, I thought ODBC would slow things down a bit but not my much, all its doing in sending off SQL straight the server? Test Server: 486 50mhz, 16 mb Ram Client: P266, 32 mb Ram SQL: 1000 INSERT INTO's with quite a lot of fields. Read from a text input file ODBC Test Windows 98 running VB6 and the freeware ODBC driver (I forget the name) Using TCP/IP network. The VB program will do about 30 records per minute. C Test Linux C program, reads input file, calls API's. Run on Client PC so still communicates over the network. This setup will do 340 record per minute My only guess is that the following bottlenecks are in the system: VB6 code that reads the text file - unlikely, it run's very quick if you remove the db.Execute call. ODBC parsing, maybe Something crappy about Windows, more likely ODBC sucks on its own, but I'd bet the difference is that you open a transaction for insert in Linux and don't do that in Windows (how do you open a transaction in ODBC?). Difference is usually about 1:10 when you do inserts in transaction, and server doesn't have to fsync after each insert... -alex
Re: [GENERAL] How to retrieve table definition in SQL
Stan Jacobs wrote: This isn't quite what I'm looking for, though I can't run a script on that machine, so I need to retrieve it with an SQL query. Ie. "SELECT * from data_key_table_name_something"... *smile* Judging from the few responses so far, it doesn't sound like there's an easy way to do this. You only need to run the script on the machine to get the initial sql queries to which Omid referred. You can then take those and run them from any client. Psql just shows you an example of how it does what you're trying to do. Not sure how easy it is to get everything right, but psql and pgaccess both do what you seem to be trying to do. Here's a trimmed example from 7.0beta: % psql -d emsdb -E emsdb=# create table foo (id serial, t timestamp); CREATE emsdb=# \d foo * QUERY * SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_class WHERE relname='foo' * * QUERY * SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'foo' AND a.attnum 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum * ... Regards, Ed Loehr On Sun, 19 Mar 2000, omid omoomi wrote: Hi, There are some system tables in any pg database which contain information about table/field names/types and descriptions. use -e with psql command, and look at the sql code when running /d commands. regards. Omid Omoomi From: Stan Jacobs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [GENERAL] How to retrieve table definition in SQL Date: Sun, 19 Mar 2000 03:40:43 -0800 (PST) Hi everyone, This probably isn't a Postgres-specific question, but I'm hoping that someone knows the answer to this off the top of their heads... :-) I'd like to retrieve the table definition via SQL. I'm using ColdFusion to access a PostgreSQL 6.5.3 database, and I'd like to retrieve the table info, field names/types/sizes, so that my Coldfusion page/script can dynamically build the html forms to edit the tables. Any ideas how to get to this in SQL? I have another C++ class which builds nice table headers with this info, but ColdFusion doesn't seem to do that with the returned data. Thanks! - Stan - __ Get Your Private, Free Email at http://www.hotmail.com
Re: [HACKERS] Re: [GENERAL] what is the most appropriate way to shutdown the postmaster?
At 08:43 AM 3/20/00 +0900, Tatsuo Ishii wrote: and will it be possible to set a cut-off period, a la shutdown, after which all backends will be killed, e.g. Not possible in 7.0. However, it seems to be an interesting idea and I would try it after we shipp 7.0. (we are in the beta period of 7.0, it is not allowed to add new features). I suspect some folks would find it quite useful. AOLserver has a grace period feature for killing/restarting the server, for instance, and the main reason it exists is to let the admin specify an interval long enough for expected database queries to finish up (particularly transactions adding/updating data). If such a grace period is deemed useful in the web environment, I'd guess others might find it useful, too. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [GENERAL] How to use bytea type?
Afaik, not possible, at least I couldn't get any \0s into bytea column from psql nor from perl DBI interface. You need blobs if you want to store data which contains embedded nulls. Or so I think. -alex On Sun, 19 Mar 2000, Radhesh Mohandas wrote: Hi, Can somebody give me an example of how to insert a byte array for example { 12, 24 , 0, 3 , '\' , 40 } into a column declared as type bytea. Statements in psql is what I am looking forward to know Cheers, Radhesh Mohandas Residence: # Office: 341,#B,Rosedale Ave. # Jolley 541, Computer Science Dept. St.Louis,MO 63112 # One Brookings Drive,Washington University, St.Louis,MO - 63130. phone : 314-862-1418 # 314-935-7543 Wealth is beyond what an individual can produce ??!!
Re: [GENERAL] How to use bytea type?
Afaik, not possible, at least I couldn't get any \0s into bytea column from psql nor from perl DBI interface. You need blobs if you want to store data which contains embedded nulls. Or so I think. -alex Double-backslashes are the trick. test= insert into vv values ('ab\\000d'); INSERT 27467 1 test= select * from vv; x - ab\003d ab\003d ab\000d (3 rows) test= -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] How to use bytea type?
Hi, Can somebody give me an example of how to insert a byte array for example { 12, 24 , 0, 3 , '\' , 40 } into a column declared as type bytea. Statements in psql is what I am looking forward to know Use the string 'abc\003d'. Double-backslashes are optional. Bytea outputs data with double-backslashes. test= insert into vv values ('ab\003d'); INSERT 27465 1 test= select * from vv; x - ab\003d (1 row) test= insert into vv values ('ab\\003d'); INSERT 27466 1 test= select * from vv; x - ab\003d ab\003d (2 rows) -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] Granting Privileges to Groups
I have created a group in postgres by inserting a group name into pg_group as suggested in the on-line documentation. I then add a user to this group using the appropriate create user syntax. Finally, I grant privileges to the group for a specific table. The system responds affirmatively up this point. When I then try to access this table as a user named in the group I get id errors. Grants seem to work nicely for individual users , but not for groups. Anyone else had this problem? Am I doing something wrong? Is this a bug? Any help would be appreciated? Thanks, Brad Rogers
[GENERAL] Alternate locations
I have attempted to configure PostgreSQL as described on the website to use a different location to no avail. Any hints, suggestions or instructions would be appreciated. RedHat 6.1 and the version of PostgreSQL that came with it. Michael Black
Re: [GENERAL] Max Length for VARCHAR?
Hi Jan, We had something similiar to this in the past. The entire text was broken down into lines (80 characters each) and stored as individual rows in a table. The problem with this approach was that you can't search for phrases that span rows, so I'd like to be able to store the entire text inside 1 row and be able to search it. Oracle has a LONG datatype which provides this kind of funcionality. I was hoping that PostgreSQL had something similiar. We are rewriting portions of our legacy system using Java and trying to provide as much portability to RDBMSs as possible. Although our main production machine is Oracle, we'd like to make this run under PostgreSQL as well. I guess our only option is to go back to breaking things up into lines again (atleast for the PostgreSQL implementation) and providing special code that works for this database. I just hate cluttering code with special conditions (i.e., if System.getProperty("DATABASE").equals("postgreSQL") then do the following). After 7.0 is out, I'll continue the TOAST project. This will break the size limits for variable size attributes and make them virtually unlimited (new limits will be based on available per process memory, so U can create more swap to increase it). Stay tuned! If you don't mind to have a somewhat crippled DB schema during one release (7.0 until TOAST), let's see how we can simulate LONG columns using a set of rules and triggers. Wait some hours and I'll tell ya how it could work - if what I have in mind works at all :-). Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #= [EMAIL PROTECTED] (Jan Wieck) #