Hello. I have a really big database (running on PostgreSQL) containing a lot of tables with sophisticated relations between them (foreign keys, on delete cascade and so on). I need remove some data from a number of tables, but I'm not sure what amount of data will be really deleted from database due to cascade removals. How can I check...
Right now I have a log parser reading through 515mb of plain-text files (a file for each day over the past 4 years). My code currently stands as this: http://gist.github.com/12978. I've used psyco (as seen in the code) and I'm also compiling it and using the compiled version. It's doing about 100 lines every 0.3 seconds. The machine is a...
Hi, I'm a newbie to pgsql. I have few questionss on it: 1) I know it is possible to access columns by <schema>.<table_name>, but when I try to access columns like <db_name>.<schema>.<table_name> it throwing error like Cross-database references are not implemented How do I implement it? 2) We have 10+ tables and 6 of have 2000+ rows....
Hi! Google results on this one are a bit thin, but suggest that it is not easily possible. My specific problem is that I need to renumber the IDs in two tables that are related to each other such that table B has an "table_a_id" column in it. I can't renumber table A first because then its children in B point to the old IDs. I can't re...
How do the full text search systems of PostgreSQL and MySQL compare? Is any clearly better than the oder? In which way are they different? ...
I have a fairly good feel for what MySQL replication can do. I'm wondering what other databases support replication, and how they compare to MySQL and others? Some questions I would have are: Is replication built in, or an add-on/plugin? How does the replication work (high-level)? MySQL provides statement-based replication (and row-...
Hi, let's assume i have a self referencing hierarchical table build the classical way like this one: CREATE TABLE test (name text,id serial primary key,parent_id integer references test) insert into test (name,id,parent_id) values ('root1',1,NULL),('root2',2,NULL),('root1sub1',3,1),('root1sub2',4,1),('root 2sub1',5,2),('root2sub2',6,2...
Hi, I need to rename the database but when I do in PGAdmin : ALTER DATABASE "databaseName" RENAME TO "databaseNameOld" it told me that it cannot. How can I do it? (Version 8.3 on WindowsXP) Update The first error message : Cannot because I was connect to it. So I selected an other database and did the queries. I get a second error ...
I've seen a number of postgresql modules for python like pygresql, pypgsql, psyco. Most of them are Python DB API 2.0 compliant, some are not being actively developed anymore. Which module do you recommend? Why? ...
When I'm in Windows, I use the excellent MicroOLAP Database Designer for PostgreSQL, but its not open source or multiplataform. Do you know or can recommend me an alternative to this software, that I can use in Linux? EDIT: Just to clarify, I don't want to use wine to emulate MicroOlap for PostgreSQL, it doesn't work too well, I would p...
In SQL, how do update a table, setting a column to a different value for each row? I want to update some rows in a PostgreSQL database, setting one column to a number from a sequence, where that column has a unique constraint. I hoped that I could just use: update person set unique_number = (select nextval('number_sequence') ); but i...
The problem itself is simple, but I can't figure out a solution that does it in one query, and here's my "abstraction" of the problem to allow for a simpler explanation: I will let my original explenation stand, but here's a set of sample data and the result i expect: Ok, so here's some sample data, i separated pairs by a blank line -...
When paging through data that comes from a DB, you need to know how many pages there will be to render the page jump controls. Currently I do that by running the query twice, once wrapped in a count() to determine the total results, and a second time with a limit applied to get back just the results I need for the current page. This se...
In postgresql you have a wealth of optimizations at hand to configure it for your performance needs. The settings for memory usage are still easy enough, but with other options like the cost factors for CPU and IO which are used for the query optimizer are quite a mystery for me. I wonder if there is a program available which would do le...
It there a tool (preferably free) which will translate Oracle's PL/SQL stored procedure language into Postgresql's PL/pgSQL stored procedure language? ...
I have two tables: foos and bars, and there is a many-to-one relationship between them: each foo can have many bars. I also have a view foobars, which joins these two tables (its query is like select foo.*, bar.id from foos, bars where bar.foo_id=foo.id). EDIT: You would not be wrong if you said that there's a many-to-many relationship ...
Can anyone recommend for/against the time-travel functions in postgresql's contrib/spi module? Is there an example available anywhere? Tnx ...
I have a table that looks something like this: word big expensive smart fast dog 9 -10 -20 4 professor 2 4 40 -7 ferrari 7 50 0 48 alaska 10 0 1 0 gnat -3 0 0 0 The + and - values are associated with the...
I'm primarily interested in pgsql for this, but I was wondering if there is a way in any RDBMS to do an insert operation, without disabling and re-enabling any FOREIGN KEY or NOT NULL constraints, on two tables that refer to each other. (You might think of this as a chicken that was somehow born from its own egg.) For a practical examp...
I am currently working on a leave application (which is a subset of my e-scheduler project) and I have my database design as follows: event (event_id, dtstart, dtend... *follows icalendar standard*) event_leave (event_id*, leave_type_id*, total_days) _leave_type (leave_type_id, name, max_carry_forward) _leave_allocation (leave_alloca...