After altering the default privileges on a Postgres database object, how can you view them? For instance, if you grant all privileges to role_name for all tables created in the schema schema_name: Answer Using a SQL query Where the value of defaclobjtype is r = relation (table, view), S = sequence, f = functi…
Tag: postgresql
How to update a table if values of the attributes are contained within another table?
I’ve got a database like this one: I’m trying to create a query that would enable me to update the value of the status attribute inside the incident table whenever the values of all of these three attributes: tabor_vatrogasci, tabor_policija, and tabor_hitna are contained inside the izvještaj_tabo…
PostgreSQL: FOREIGN KEY/ON DELETE CASCADE
I have two tables like here: So the logic is simple, after user removes all book under category x, x gets removed from cats, i tried method above but doesn’t work, after i clean table book, table category still populated, what’s wrong? Answer A foreign key with a cascade delete means that if a rec…
How to delete a particular label from ltree in Postgres table?
How to delete a particular label from ltree in Postgres table? I have a table? Test table: I want to pass a userid to a query, to remove it from every path in the table. For example, if I pass 101, then 123.101.103 should update as 123.103. Is it possible to do this directly? Or shall I update path using
Transpose table with “list” of the same attributes
Is it possible to transpose a table with repeated columns? Existing table: user_id question_id body 1 1 ‘Text1 1’ 1 1 ‘Text1 1-2’ 1 2 …
Insert values if records don’t already exist in Postgres
I’d like to get this working, but Postgres doesn’t like having the WHERE clause in this type of insert. Answer In Postgres, there is a really nice way to do that: hope that helps.-
PostgreSQL – repeating rows from LIMIT OFFSET
I noticed some repeating rows in a paginated recordset. When I run this query: I get: Next query: I get: Why does “foo” appear in both queries? Answer Why does “foo” appear in both queries? Because all rows that are returned have the same value for the status column. In that case the d…
UPDATE rows with values from the same table
I have a table like this: I want to copy the last 3 values and at the end my table will look like this: Is it possible? Answer Use a self-join: If there are gaps in the ID space, generate gapless IDs with the window function row_number(). I do that in a CTE, because I am going to reuse the
Join a count query on generate_series() and retrieve Null values as ‘0’
I want to count ID’s per month using generate_series(). This query works in PostgreSQL 9.1: This is my output: But what I want to get is this output (‘0’ value in January): Months without id should be listed nevertheless. Any ideas how to solve this? Sample data: Answer Untangled, simplified…
Query using ILIKE with IN
Is it possible to run a query using ILIKE with the IN function? For example: I want to run the results returned from the nested query through the ILIKE function. Is there a way to do this? Answer Can be simpler: You can check the resulting query plan with EXPLAIN ANALYZE. You may need to add leading and trail…