Skip to content

Tag: postgresql

how to convert integer minutes to interval in postgres

I’m trying to convert minutes which are in integer to interval in postgres Is there any function that will help me to convert it to interval or should i have divide it by 60 and get the final result Answer Fastest way is with make_interval So it looks like this (as suggested by @Teddy) or, Not to say th…

iBATIS and dblink?

I am using PostgreSQL, and I use the dblink feature to update data on remote database. My project using iBATIS to work with database. I need to run the query statement with parameter but the problem is the db_link require SQL to be enclosed in quote. And iBATIS do not understand it.. I have iBATIS sql: But du…

PostgreSQL Partial Indexes and UPSERT

After googling a lot my question is described below: Attempting to insert values: Results in: Altho this works(per spec): PostgreSQL documentation stands that it should work PostgreSQL v9.5 My aim is to find way to create unique index on this table on multiple nullable columns and update old rows with new one…

How do I access a field of nested user defined types?

How do I access the fields of user defined types when they are nested ? When I tried it using dot notation it failed: For example, how do I “compile” this code ? Trying to run it: Answer Looks like a PL/PgSQL deficiency. In normal SQL you can do so with but pl/pgsql doesn’t accept the same f…

PostgreSQL – Insert data into multiple tables simultaneously

I am having a data insertion problem in tables linked by foreign key. I have read in some places that there is a “with” command that helps in these situations, but I do not quite understand how it is used. I would like to put together four tables that will be used to make a record, however, that a…

List all foreign keys PostgreSQL

I need a query that returns: “table_name”, “field_name”, “field_type”, “contraint_name” until now i have: Answer A foreign key may be based on multiple columns, so conkey and confkey of pg_constraint are arrays. You have to unnest the arrays to get a list of col…

Postgres Left Join with where condition

I need to left join two tables with a where condition: time_table record_table I need to get all those records which are present under given date range. In the above example, I need those records that lie under range for rid = 2 only. Hence the output for the above query needs to be: Answer left join two tabl…

sql SUM: change null with 0

I have a query like this: And another like this: I want to select from those tables, with condition But I also want to change the null record which appears with 0, I’ve tried COALESCE but it still doesn’t give me the right result. Answer In general you would first set NULL values to 0 and then SUM…