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 such as CartItemss and Users. Is there a nice way to
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 anyone help me to solve this
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 to it, right? Postgres
(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 rows using json_build_object
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 ‘ ) + …