I have postgres DB which has multiple schemas, is there a way (query) which I can run to figure out if a function exists in a specific schema or in other words print a list of schema names under which a given function exists. Answer You can adapt following query:
Tag: postgresql
postgres12 alter table with type cast syntax error
I’m trying to alter a table with values like below. The data type is a string but I want to change it to numbers. ALTER TABLE data ALTER COLUMN value TYPE NUMERIC(7,2) USING value::numeric ERROR: …
Query updated records in Database
Currently all our tables have a created_at and updated_at timestamp, e.g. The updated_at field is modified with every INSERT. This allows us to query all Cart’s which have been updated since a particular point in time with something like this: However this would not capture updates to FK relationships s…
How to fetch a column name which has maximum value from each group in postgres?
I got stack when i try to return maximum value in this query. Here is my query It is showing me the output like this according the query But i want the maximum product_service_name. my expected output is I tried with max(ps.product_service_name) but it did fetch unexpected result. it will be really helpful if…
Adding checks in postgres table where data is already incorrect
This seemed to be a useful question to me and didn’t find any written information about it anywhere so thought that this might be too obvious. Just want to confirm this: If I alter a postgres table to add a check but the data that is already present is inconsistent with this check, nothing will happen t…
(Postgresql) How to get all match from array of integer
I want all row matching with group_id pass as input ex. Here is my table:- id | name | group_id —-+—————-+———- 1 | Alice John | {1,2,3} 2 | joshn shukla | {1,…
Find all records that have X tags ordered by ascending time, BUT, the ones that match the most tags should be grouped first
Ok, this is a little hard to explain, and the title is not the best :/ but, basically, I have 3 tables and I’m trying to write a query that returns them with 2 levels of ordering. The first by the amount of “tags” each row has, and the second by when that row was created. And I would like
Migrating data from old table to new table Postgres with extra column
Table Structure: Old Table Structure: New Table Structure: Query: INSERT INTO hotel (id, name, hotel_type, active, parent_hotel_id) SELECT id, name, hotel_type, active, parent_hotel_id FROM …
Postgres get multiple rows into a single json object
I have a users table with columns like id, name, email, etc. I want to retrieve information of some users in the following format in a single json object: Wherein the users unique id is the key and a json containing his information is its corresponding value. I am able to get this information in two separate …
Postgres converting double double precision to text creates ‘1.50000000000’
Working with postgres and I need to convert total minutes into the format of 1h 1m. I’m doing this via the following code replace( (119 / 60 + (119 % 60) / 100.0)::text, ‘.’, ‘h ‘ ) + …