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 maybe I’m missing something?
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 are not supported – as defined in the SQL standard and implemented by some RDBMS including
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 test
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 to do this?
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 and convert amount to cents in
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 records: Now say I wanted to find everyone’s
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 below suggesting: That’s probably fine in