PostgreSQL Database Forum

Hi, Our new Postgres database includes a table with about 1 mil records. So far, I've been impressed with the performance of queries against 1 mil rows. Unfortunately I just found out that the size of the table will increase to 3 mil records. Can I expect Postgres to return results on a query with a basic join against a 3 mil record...

Hi, I'm trying to extract element of a directory path stored in the db with substring "/help/support/index/time.jsp" and i want to extract the 1st, 2nd and 3rd parts 1st = help, 2nd = support, 3rd = index

Hi, In my PC, eth0 is not up because of IP address clash (another PC is using the same IP address). I tried to execute a file which has set of insert commands. After executing the file, when I go and see the database, no rows are inserted. I redirected the output to another file. It shows success for all inserts, but no rows in database. This...

I have been using the EXPLAIN ANALYZE command to debug some performance bottlenecks in my database. In doing so, I have found an oddity (to me anyway). The "19ms" total runtime reported below actually takes 25 seconds on my computer (no other CPU intensive processes running). Is this normal for EXPLAIN ANALYZE to report a total runtime so...

I ran a home-grown self continuous stress test tool against my 7.4.2 database. I banged 'ps' (running freebsd) while it was active and witnessed several of the 'back end' postgres processes exeucting queries, commits, inserts, etc....(the actual work the processes were doing was listed in the 'ps' output) however, if I checked...

Hi, I am working on converting Informix database to Postgre. I have one question for fetching records using PostgreSQL. The record I am fetching is a variable size text string. Before fetching the record, a preallocated static buffer size will have to be declared beforehand in the declaration section. Since the record sizes vary a lot,...

Hello, I am new to the list, my apology if this question is beyond the scope or charter of this list. My questions is: What is the best method to perform an aggregate query to calculate sum() values for each distinct wid as in the example below, but except for all wid's (not just WHERE wid='01/1-6-1-30w1/0'). Also, performance wise,...

I recently upgraded an application from postgresql 7.4.2 to 7.4.5, and now I am having timezone nightmares. I checked on the old server, and found that in the postgresql.conf, the timezone was set for unknown. The timezone portion of the records was being set to GMT+06 ( for reference, I am in GMT-06 ). Now, on the old server, wheni pulled...

Hi! I'm thinking about creating a table listing my shares (say 'Shares') and then update its price column from a .txt file (say 'Prices') downloaded from an internet site periodically. Recalling past readings my first idea would be to use something like: => update table Shares set price = (select price from Prices) where Shares.name =...

Everyone, I am about to begin a project which will use Java (JDBC) and postgresql. In most Java projects, I do not embed SQL code, but create functions which have the SQL logic. I call the functions from within my Java code. For selects, the functions return refcursors, which the Java code can get results from. However, a seperate team...

It appears that the Slony list isn't accepting traffic/posts at all. I posted a message yesterday (2004-08-24) and haven't even received it myself at this point (2004-08-25). Just thought I'd give a heads up, in case now one knew. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster

Hi, I was wondering if it's possible to order the result set by some of the set contained in an IN clause. For example. SELECT * FROM v_fol_unit_pub_utmpt WHERE folder_folder_object = 100120 AND unit IN ( 90072, 90005, 90074, 90075 ) AND unit_pub_type IN ( 2 ) AND utmpt IN ( 1 ); Results in.

Sorry if this question has been asked before. I tried to search in postgres mailing lists at http://archives.postgresql.org/pgsql-general/ just now and it gave me error "An error occured! Can not connect to search daemon". Anyway I installed tsearch2 and openFTS and was able to do some searches. However, when I checked searching partial...

Hi folks, I'm new to PostgreSQL and am busy tring to work with it. Of pl/pgsql I know even less and that's the part I have a question on right now. I have this nice example to get me started with pl/pgsql....... I have a table with the schedule of a service my sport team wants to offer:

Hi all! After a very long struggle I finally succeded in transferring my old *.dbf file and the relating *.dbt (alias memo fields) to a pg table. For the time being I put the memo field in a separate table having two fields only (i.e.: n_memo integer, memo text) which can be related to the main table in a view.

Hi, after seeing some mysterious messages about missing from clauses I did turn on query logging. Unfortunately am I now more confused then before. Aug 25 09:22:38 balder postgres: LOG: query: SELECT * FROM LOGIN Aug 25 09:22:38 balder postgres: where USERNAME='ulrich' Aug 25 09:22:47 balder postgres: NOTICE: Adding missing

Hello, I'm new to the list and did not find a suitable answer to my question so here it is: I try to select the rows of a table where the content of a varchar-column is empty ('') and PostgresQL is doing a seqscan. I've tried this on a PostgresQL-Serverversion 7.3.4 and 8.0 beta1.

Hello, a question about a tool or a possibility how could something work. following situation: we have a database which is full of very sensitive information and needed that db to use our online website. but now we move the website to a server outside our office and needed to replicate only some datas to the online db. with the tool slony i...

Just wondering why the 7.4.5 tarball doesn't include the JDBC driver source. Was this an error or do we now have to download the driver separately from jdbc.postgresql.org? -M@ ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail...

I just noticed that from a C or C++ program using libpq or libpq++, I can send *one* command that contains several SQL statements separated by semicolon. Something like: PgDatabase db (" .... "); const char * const sql = "insert into blah (...); insert into blah (...)";

Hi, Not exactly a showstopper, but I noticed this behaviour: db=# create table f1 (id int, value int); CREATE TABLE db=# insert into f1 select 1 as id, null; INSERT 25456306 1

Hi all! Testing a script where I need to make sure that postgresql is running before passing a <psql dbasename -c "insert into ..." > instruction I faced this curious behaviour: This is the relevant content of the script: ------------------ #!/bin/bash /usr/lib/postgresql/bin/pg_ctl status -D /var/lib/postgres/data >/dev/null 2>&1 rtn=$?

Dear All, I have upgraded Postgresql from 7.3 to 7.4. Starting pg brings error: The database is in an older format that cannot be read by version 7.4 of PostgreSQL dpkg-upgrade postgresql fails I tried postgresql-dump. While dumping it brings the following error: FATAL: unsupported frontend protocol However the dump file is generated.

Hi, I want to use Postgres as a hosted solution, and I want the Postgres server to have a different database per hosted client. What is the practical limit to the number of different databases a single server can support? Thanks, Guy. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and...

Hi, I am trying to install OPenacs with postgres 7.4.3 as the database. The openacs requires plpgsql to be installed. When I try to do this by running "CREATELANG plpgsql template1". I get a message file plpgsql not found in lib directory.