I have a table named visiting that looks like this: id | visitor_id | visit_time ------------------------------------- 1 | 1 | 2009-01-06 08:45:02 2 | 1 | 2009-01-06 08:58:11 3 | 1 | 2009-01-06 09:08:23 4 | 1 | 2009-01-06 21:55:23 5 | 1 | 2009-01-06 22:03:35 I want to work out a sql...
I was screwing around in psql and renamed the template0 and template1 before realizing their usage. Now, I am getting a "permission denied to copy database 'template1'" from inside psql and form command-line when I try to recreate template1. To save time, is there anything else I need to know about template1 vis a vis OS read/write per...
Hi there, I have been running an UPDATE on a table containing 250 million rows with 3 index'; this UPDATE uses another table containing 30 million rows. It has been running for about 36 hours now. I am wondering if their is a way to find out how close it is to being done for if it plans to take a million days to do its thing, I will kil...
Right now we are using PostgreSQL 8.3 (on Linux) as a database backend to our Ruby on Rails web application. Considering that on PostgreSQL database we actively use row level blocking and PL/PGSQL, what can we employ to secure our data -- I mean tools, packages, scripts, strategies -- to successfully replicate the database and build mul...
I have a query like this: SELECT t1.id, (SELECT COUNT(t2.id) FROM t2 WHERE t2.id = t1.id ) as num_things FROM t1 WHERE num_things = 5; The goal is to get the id of all the elements that appear 5 times in the other table. However, I get this error: ERROR: column "num_things" does not exist SQL state: 42703 I'...
This took me a while to figure out and I found the answer on a foreign-language wiki a number of weeks ago, and it was very helpful, so I thought I would share. ...
Hi again, I have a PostgreSQL table that looks like this: A -> B A -> C A -> G A -> H B -> O B -> K Where "->" separates two columns where the first points to the second (hyperlinks). Now I would like to take all distinct values in the first column and assign them an ARRAY containing all values to which they point to in the second co...
Hi again, I am currently cleaning up a table with 2 indexes and 250 million active rows and approximately as many dead rows (or more). I issued the command VACCUM FULL ANALYSE from my client computer (laptop) to my server. It has been going about its business for the last 3-4 days or so; I am wondering if it will end anytime soon for I ...
I am totally new to postgresql. How can I do a condition to create a table only if it does not already exists? Code example appreciated. ...
The following query will display all Dewey Decimal numbers that have been duplicated in the "book" table: SELECT dewey_number, COUNT(dewey_number) AS NumOccurrences FROM book GROUP BY dewey_number HAVING ( COUNT(dewey_number) > 1 ) However, what I'd like to do is have my query display the name of the authors associated with the dupl...
I'm investigating a PostgreSQL-backed application. CPU usage is constantly over 50% on a modern Xeon with 4GB RAM. Of that 50% CPU utilization, 67% is "user" and 33% is "system" (this is a Linux machine.) The system is not waiting on I/O at all. I'm wondering how I can see how this CPU time breaks down. The queries are mostly ad-hoc...
I'm looking for some quick thoughts about a business application I am looking to build. I'd like to separate the three layers of presentation, domain logic, and data using PHP, Python, and PostgreSQL, respectively. I would like to hear, possibly from other folks who have gone down this path before, if there are problems with this approa...
With xml like: <a> <b> <c>1</c> <c>2</c> </c>3</c> </b> I'm trying to create an xpath expression (for a postgresql query) that will return if is a particular value and not that is all three values. What I currently have (which does not work) is: select * from someTable where xpath ('//uim:a/text()', job, ARRAY[ ARRAY[...
We're migrating MySQL to PostgreSQL. I can easily audit the schema and the SQL statements used throughout the (REALbasic) program. Most of the SQL is composed by building string variables. I already know about needing to replace our use of SELECT LAST_INSERT_ID() with a SERIAL column with UNIQUE constraint. What, if any, differences b...
Let's say I have the following objects: squirrel_table - name - country_of_origin - id nut_table - id - squirrel_who_owns_me[fk to squirrel] I want to retrieve a list of all squirrels in a particular country. The returned squirrel objects can be in a QuerySet, but do not need to be. A list will suffice. Each squirrel will...
On another thread I saw a answer to a sql problem using Oracle's analytics. Is there a equivalent in PostgreSQL or generic SQL? ...
I have a table named "buildings" that contains a varchar(50) field named "use". The table already has several thousand records, however the "use" values are all null. I would like to update these this table with randomly chosen values from a list of strings (e.g., warehouse, office, market, retail, workshop). I would also like to leave...
I am using Ruby 1.8.6 and rails 2.1.1 (I have tested rails 2.2.2 as well). I have been using Sqlite3 and I recently migrated to Postgresql 8.2.6 for production use. Now this error shows up. The interesting thing is that when I copy the insert statment and use psql to directly insert it into postgres it works fine. I guess the problem...
Essentially I need a count of each Entries Comments: SELECT e.*, COUNT(c.id) as comments FROM blog_entry e LEFT JOIN blog_comment c ON e.id = c.entry_id GROUP BY e.id, e.name, e.name_slug, e.date_published, e.category, e.image, e.body, e.is_published, e.views, e.subscription_sent ORDER BY e.date_published DESC LIMIT 15; But I don't kn...
I am not too familiar with database triggers and/or views. I am currently using PostgreSQL and HSQL; although the database is not too important. I am just wondering if any database offers something like this: I have an (example) table like this: CREATE TABLE DUMMY_TABLE (ID INTEGER, NUMBER INTEGER); I created a view like this: CRE...