I have a star schema here and I am querying the fact table and would like to join one very small dimension table. I can’t really explain the following: EXPLAIN ANALYZE SELECT COUNT(impression_id), …
Tag: postgresql
Temp sequence in postgres view
I have a table that I order a certain way often. That ordering is the basis of a derived column we can call rank. If I query the entire table, I can have the database calculate that rank for me: This yields useful results like: With that result set I can determine the rank of any foo. However this requires
How to visualize database tables in postgresql using pgAdmin?
I am trying to visualize tables and their relations using pgAdmin. I have understood that there is a query visualizer tool available for pgAdmin. However, that only is useful if you are dealing with queries. My main goal is to generate a graphical representation of all the tables available in database. Answer…
How to avoid multiple function evals with the (func()).* syntax in a query?
Context When a function returns a TABLE or a SETOF composite-type, like this one: the results can be accessed by various methods: select * from func(3) will produce these output columns : select func(3) will produce only one output column of ROW type. select (func(3)).* will produce like #1: When the function…
PostgreSQL – dump each table into a different file
I need to extract SQL files from multiple tables of a PostgreSQL database. This is what I’ve come up with so far: However, as you see, all the tables that start with the prefix thr are being exported to a single unified file (db_dump.sql). I have almost 90 tables in total to extract SQL from, so it is a…
Avoid division by zero in PostgreSQL
I’d like to perform division in a SELECT clause. When I join some tables and use aggregate function I often have either null or zero values as the dividers. As for now I only come up with this method …
How to find out 2nd highest salary of employees?
Created table named geosalary with columns name, id, and salary: name id salary patrik 2 1000 frank 2 2000 chinmon 3 1300 paddy 3 1700 I tried this below code to find 2nd …
Group query results by month and year in postgresql
I have the following database table on a Postgres server: I would like to create a query that gives the SUM of the Sales column and groups the results by month and year as follows: Is there a simple way to do that? Answer At the request of Radu, I will explain that query: to_char(date,’Mon’) as mo…
Change values before insert into table / PostgreSQL
Ive got the following problem. I want to change some values of INSERT statement, e.g. there’s a query: INSERT INTO table(a,b) values(x,y); But table has also another column c. I want to check (…
Get data from both table, where data isn’t common for this tables
I have 2 tables TABLE jt1 and TABLE jt2 I need get names from both tables, which is not common for both tables, that is result must be This is my query, but may be there is better solution for this? Answer BTW: the naive solution could probably be faster: BTW: I purposely use UNION ALL here, because I know