I'm performing a regular expression match on a column of type "character varying(256)" in postresql 8.3.3. The column currently has no indices. I'd like to improve the performance of this query if I can. Will adding an index help? Are there other things I can try to help improve performance? ...
Hi, I have a service running and inserting data (a lot of data). Sometime, and this is only about few weeks, I receive this error: ERROR: XX001: could not read block 2354 of relation 1663/17633/17925: read only 0 of 8192 bytes. This error is from the Npgsql connector of PostGresql: Exception trace: at Npgsql.NpgsqlConnector.Chec...
I'd like to migrate an existing MySQL database (around 40tables, 400mb data) to Postgres before it gets bigger. I searched the web and tried some migration-scripts (some of them can be found here). None of them works seamlessly - if it would be just a few glitches I had to fix manually, it wouldn't be a problem, but the resulting dumps d...
My googling-fu is failing me. How to know if a PostgreSQL trigger is disabled or not? ...
SELECT count(*) FROM table WHERE column ilike '%/%'; gives me the number of values containing "/" How to do the same for "\"? ...
How to prevent recursive execution of trigger? Let's say i want to construct a "tree-able" description on chart of account. So what I do is when a new record is inserted/updated, I update the the parent record's down_qty, so this would trigger the update trigger recursively Right now, my code is ok, i do this: I put this on UPDATE tri...
I have a utility in my application where i need to perform bulk load of INSERT, UPDATE & DELETE operations. I am trying to create transaction around this so that once this system is invoke and the data is fed to it, it is ensured that it is either all or none added to the database. The concern what is have is what is the boundary condit...
Since I struggled a bit with this one and couldn't find a good online source with simple steps, here it is. ...
I'm setting up a large database that will generate statistical reports from incoming data. The system will for the most part operate as follows: Approximately 400k-500k rows - about 30 columns, mostly varchar(5-30) and datetime - will be uploaded each morning. Its approximately 60MB while in flat file form, but grows steeply in the ...
How to specify custom constraint for Django model? Like that create table t ( login varchar(10), unique ( upper(login) ) ); ...
I have an interesting problem which I've been looking into and would appreciate some advice: I'm trying to create a tool which mimics the basic capabilities of a requirements management tool as part of a company project. The basic design is a Windows Explorer-like setting of folders and documents. Documents can be opened in a GUI, edit...
I am trying to do the schedule for the upcoming season for my simulation baseball team. I have an existing Postgresql database that contains the old schedule. There are 648 rows in the database: 27 weeks of series for 24 teams. The problem is that the schedule has gotten predictable and allows teams to know in advance about weak parts...
Hi. Can anyone tell me how to enable logging of all SQL executed by Postgres (8.3)? Thanks! Edited (more info) I changed these lines : log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_statement = 'all' And restart postgres service... But no log was created... I´m using Windows Server 2003 Any ide...
I have a timestamp with timezone field in PostgreSQL. When I update this field, I use something like this: $date = date('Y-m-d H:i:s'); Although the SQL works fine, the date saved seem a little bit different to a classic timestamp with timezone date. Example: Default value set to "now()": date 2009-04-06 14:39:53.662522+02 Update ...
Hiya, I've been asked to support and take on a PostgreSQL app, but am a MySQL guy - is this a realistic task? ...
Is there a way to force clients to disconnect from PostgreSQL? I'm looking for the equivlent of DB2's force application all. I'd like to do this on my development box because when I've got database consoles open, I can't load a database dump. I have to quit them first. ...
I am trying to get the number of students enrolled in courses via a single SQL statement, but not using sub-queries. So far I can only figure out how to do it using sub-queries. Is there another way? Consider the following database setup: create table student (id integer not null primary key); create table course_enrolment (student in...
Hi, I'm looking for a way to script postgreSQL schema changes in an idempotent manner. In MSSQL I could do something like this: if(not exists(select * from information_schema.columns where table_name = 'x' and column_name = 'y')) begin alter table x add y int end go PostgreSQL doesn't seem to allow ad-hoc pl/pgsql in the same wa...
Hi, I'm using PostgreSQL DB in my application, I used to create special windows user account to run the DB service. Now I need to run PostgreSQL service under Local System account! Is there a configuration in PostgreSQL to specify the user account which the service runs under? Thanks, ...
Hi everyone, I'm new to persevere, I did the helloWorld tutorial, very impressive btw but I'm now trying to connect to a real postgres database and find myself struggling having the driver recognized when I have the feeling it should be dead simple. I dropped all the jdbc driver for postgres I had to the persevere/lib folder: postgr...