I have a rather complicated query on my PostgreSQL database spanning 4 tables via a series of nested subqueries. However, despite the slightly tricky looking appearance and setup, ultimately it will return two columns (from the same table, if that helps the situation) based on that matching of two external pa…
Tag: postgresql
How to use SELECT DISTINCT with RANDOM() function in PostgreSQL?
I am trying to run a SQL query to get four random items. As the table product_filter has more than one touple in product i have to use DISTINCT in SELECT, so i get this error: for SELECT DISTINCT, …
PostgreSQL IF statement
How can I do such query in Postgres? IF (select count(*) from orders) > 0 THEN DELETE from orders ELSE INSERT INTO orders values (1,2,3);
Does PostgreSQL support “accent insensitive” collations?
In Microsoft SQL Server, it’s possible to specify an “accent insensitive” collation (for a database, table or column), which means that it’s possible for a query like to find a row with a Joao name. I know that it’s possible to strip accents from strings in PostgreSQL using the u…
How to populate a table’s foreign keys from other tables
I’ve got the following tables, of which translation is empty and I’m trying to fill: The source data to fill translation is a temporary table that I’ve populated from an external CSV file: What I’d like to do is to fill translation with the values from tmp_table. The translated field c…
How to update selected rows with values from a CSV file in Postgres?
I’m using Postgres and would like to make a big update query that would pick up from a CSV file, lets say I got a table that’s got (id, banana, apple). I’d like to run an update that changes the Bananas and not the Apples, each new Banana and their ID would be in a CSV file. I tried looking
Selecting by month in PostgreSQL
I want to select rows according to the month of a date or timestamp column like this: But I only get error messages in PostgreSQL. How can this be done? Answer You can use EXTRACT function, like this: Your problem comes from the fact that there is no such thing as Month function in PostgreSQL. Check online do…
PostgreSQL unnest() with element number
When I have a column with separated values, I can use the unnest() function: How can I include element numbers? I.e.: I want the original position of each element in the source string. I’ve tried with window functions (row_number(), rank() etc.) but I always get 1. Maybe because they are in the same row…
Get number of partitions in PostgreSQL database
What is the most efficient way to get the number of partitions created in the database? I am using PostgreSQL API for C++. Answer This is how you can select all the names of the table partitions: It can be used to count as well:
Change PostgreSQL columns used in views
I would like PostegreSQL to relax a bit. Every time I want to change a column used in a view, it seems I have to drop the view, change the field and then recreate the view. Can I waive the extra protection and just tell PostgreSQL to let me change the field and then figure out the adjustment to the