Can I create a user defined function in Postgres either through the C-Language Function API or by using pl/pgsql which accepts a callback function as parameter? As far as I see there is no way to do this through the C-Language API since it only accepts sql datatypes and there is no datatype for function. But …
Tag: postgresql
Best way to delete millions of rows by ID
I need to delete about 2 million rows from my PG database. I have a list of IDs that I need to delete. However, any way I try to do this is taking days. I tried putting them in a table and doing it in batches of 100. 4 days later, this is still running with only 297268 rows deleted.
Computed / calculated / virtual / derived columns in PostgreSQL
Does PostgreSQL support computed / calculated columns, like MS SQL Server? I can’t find anything in the docs, but as this feature is included in many other DBMSs I thought I might be missing something. Eg: http://msdn.microsoft.com/en-us/library/ms191250.aspx Answer Up to Postgres 11 generated columns a…
Extract first numeric part of field
I have a database (Postgres 7.4) field for address Example Data The queries I get all but I don’t want 123 main street I get the results I want My question is how do I just match the numeric part of the address? Answer Returns 1 or more digits from the start of the string. Leave out the anchor ^
How to COUNT duplicate rows?
I want to be able to create a histogram out of a tuple containing two integers values. Here it is the query: For this input: Would be: How can I create a histogram of those values? In other words, how can I count how many times a row has a duplicate? Answer The question leaves room for interpretation. This te…
How to return result of a SELECT inside a function in PostgreSQL?
I have this function in PostgreSQL, but I don’t know how to return the result of the query: But I don’t know how to return the result of the query inside the PostgreSQL function. I found that the return type should be SETOF RECORD, right? But the return command is not right. What is the right way …
What are the pros and cons of performing calculations in sql vs. in your application
shopkeeper table has following fields: Let’s say, I have the above table. I want to get the records for yesterday and generate a report by having the amount printed to cents. One way of doing is to perform calculations in my java application and execute a simple query and then loop through the records a…
How to alter a column’s data type in a PostgreSQL table?
Entering the following command into a PostgreSQL interactive terminal results in an error: What is the correct command to alter the data type of a column? Answer See documentation here: http://www.postgresql.org/docs/current/interactive/sql-altertable.html
Filtering Django Query by the Record with the Maximum Column Value
Is there an easy way to filter a Django query based on which record has a max/min value in a column? I’m essentially asking these questions, but in the specific context of Django’s ORM. e.g. Say I have a model designed to store the historical values of everyone’s phone numbers. with the reco…
Postgres: Add constraint if it doesn’t already exist
Does Postgres have any way to say ALTER TABLE foo ADD CONSTRAINT bar … which will just ignore the command if the constraint already exists, so that it doesn’t raise an error? Answer This might help, although it may be a bit of a dirty hack: Then call with: Updated: As per Webmut’s answer bel…