postgresql

How do I check that I removed required data only?

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...

How do you make Python / PostgreSQL faster?

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...

Q's on pgsql

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....

How do I disable referential integrity in Postgres 8.2?

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...

PostgreSQL's and MySQL's full text search

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? ...

Which database has the best support for replication

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-...

cloning hierarchical data

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...

PostGreSQL - Rename database

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 ...

Python PostgreSQL modules. Which is best?

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? ...

Can you recommend a PostgreSQL Visual Database Designer for Linux?

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...

How to update unique values in SQL using a PostgreSQL sequence?

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...

Selecting unique rows in a set of two possibilities

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 -...

Best way to get result count before LIMIT was applied in PHP/postgres

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...

How to optimize PostgreSQL Configuration? (Is there a tool available?)

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...

Tool for translation of Oracle PL/SQL into Postgresql PL/pgSQL

It there a tool (preferably free) which will translate Oracle's PL/SQL stored procedure language into Postgresql's PL/pgSQL stored procedure language? ...

Updating two tables in a many-to-one relationship through a view in PostgreSQL

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 ...

Time Travel functions in postgresql

Can anyone recommend for/against the time-travel functions in postgresql's contrib/spi module? Is there an example available anywhere? Tnx ...

SQL Absolute value across columns

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...

Can/how do you add a circle of references without breaking RI?

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...

electronic leave application database design

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...