sql
Safely rename tables using serial primary key columns
serial is not an actual data type. The manual states: The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns The pseudo data type is resolved doing all of this: create a sequence named tablename_colname_seq create the column with type integer (or int2 / … Read more
Why are there gaps in my IDENTITY column values?
The identity property on a column does not guarantee the following: Uniqueness of the value – Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index. Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent … Read more
Delete duplicate rows (don’t delete all duplicate)
Try the steps described in this article: Removing duplicates from a PostgreSQL database. It describes a situation when you have to deal with huge amount of data which isn’t possible to group by. A simple solution would be this: DELETE FROM foo WHERE id NOT IN (SELECT min(id) –or max(id) FROM foo GROUP BY hash) … Read more
Custom SERIAL / autoincrement per group of values
This has been asked many times and the general idea is bound to fail in a multi-user environment – and a blog system sounds like exactly such a case. So the best answer is: Don’t. Consider a different approach. Drop the column category_id completely from your table – it does not store any information the … Read more
GROUP BY and aggregate sequential numeric values
Identifying non-consecutive values is always a bit tricky and involves several nested sub-queries (at least I cannot come up with a better solution). The first step is to identify non-consecutive values for the year: Step 1) Identify non-consecutive values select company, profession, year, case when row_number() over (partition by company, profession order by year) = … Read more
Stored procedure or function expects parameter which is not supplied
In my case I received this exception even when all parameter values were correctly supplied but the type of command was not specified : cmd.CommandType = System.Data.CommandType.StoredProcedure; This is obviously not the case in the question above, but exception description is not very clear in this case, so I decided to specify that.
SQL performance on LEFT OUTER JOIN vs NOT EXISTS
Joe’s link is a good starting point. Quassnoi covers this too. In general, if your fields are properly indexed, OR if you expect to filter out more records (i.e. have a lots of rows EXIST in the subquery) NOT EXISTS will perform better. EXISTS and NOT EXISTS both short circuit – as soon as a … Read more
How to find all connected subgraphs of an undirected graph
Here is a variant that doesn’t use cursor, but uses a single recursive query. Essentially, it treats the data as edges in a graph and traverses recursively all edges of the graph, stopping when the loop is detected. Then it puts all found loops in groups and gives each group a number. See the detailed … Read more
Conditional Count on a field
I think you may be after select jobID, JobName, sum(case when Priority = 1 then 1 else 0 end) as priority1, sum(case when Priority = 2 then 1 else 0 end) as priority2, sum(case when Priority = 3 then 1 else 0 end) as priority3, sum(case when Priority = 4 then 1 else 0 end) … Read more