I need to be able to store a large list of ordered items in the DB. So far that’s straight-forward: In queries, I always need to get just a few items (filtered based on OtherFields) but in the correct order. Easy as well, putting an index on Position and using “order by Position”. Now the pr…
How to show row numbers in PostgreSQL query?
I’d like to show the observation number for each record returned by a PostgreSQL query. I think in 8.4 windowing functions can perform this capability.
SQL How do I query a many-to-many relationship
How do I select posts that contain a specific tag if there is a many-to-many relationship between posts and tags? The problem I am having is that because of the where tag.name = ‘xxx’, only that tag is selected. I want to select all posts that have the tag specified, together with all of their tag…
Aggregating save()s in Django?
I’m using Django with an sqlite backend, and write performance is a problem. I may graduate to a “proper” db at some stage, but for the moment I’m stuck with sqlite. I think that my write performance problems are probably related to the fact that I’m creating a large number of ro…
SQL – Multiple one-to-many relationships
Is it possible somehow to do multiple one-to-many relationships between 2 tables? Like: Table abc abcID defID message Table def defID abcID message If yes how can I then make a new abc entry with …
How to remove not null constraint in sql server using query
I am trying to remove not null constraint in sql server 2008 without losing data.
Slow simple update query on PostgreSQL database with 3 million rows
I am trying a simple UPDATE table SET column1 = 0 on a table with about 3 million rows on Postegres 8.4 but it is taking forever to finish. It has been running for more than 10 min. Before, I tried to run a VACUUM and ANALYZE commands on that table and I also tried to create some indexes (although
Measuring the complexity of SQL statements
The complexity of methods in most programming languages can be measured in cyclomatic complexity with static source code analyzers. Is there a similar metric for measuring the complexity of a SQL …
How to write a query to produce counts for arbitrary value bands?
My table had 3 fields: id and unit. I want to count how many ids have <10, 10-49, 50-100 etc units. The final result should look like: This is the query that returns each id and how many units it has: How can I build on that query to give me the category, countIds result? Answer edit: changed sum() to
PostgreSQL Index Usage Analysis
Is there a tool or method to analyze Postgres, and determine what missing indexes should be created, and which unused indexes should be removed? I have a little experience doing this with the “profiler” tool for SQLServer, but I’m not aware of a similar tool included with Postgres. Answer I …