This is what my employee table looks like where manager_id is Foreign Key to the same table it references to employee_id and indicates which employee has manager and who is. I did self left join and the result was what was expected. But this query gives me So I don’t understand why it returns something …
Tag: postgresql
Pgsql find similar records in database
I have a table that has the layout of this sensor_id time value 1 2020-12-22 09:00:00 20.5 1 2020-12-22 10:00:00 21.5 1 2020-12-22 11:00:00 22.5 1 2020-12-22 12:00:00 23.5 2 2020-12-22 09:00:00 …
What is the best way to get a value from a reference table to update a foreign key relationship
I have a mapping table that maps an int to a string for example I have another table that references this one with a foreign key relationship create table products ( product_name varchar (50), …
Execute a query for multiple sets of parameters with psycopg2
I have a table that I want to query, but I want to make many specific queries and return a table of any results that have met their condition, and ignore queries that do not exist. Hopefully this illustrates what I’m trying to achieve here… I want to perform a series of select statements which eac…
Sql memory issue
I have created a new postgres server and when I trying to execute this sql command: table1 and table2 contains around 300 000 records I got an error like this: Any ideas ? Regards, Arrmlet Answer Your join conditions are probably not correct. You can calculate how many rows there are by using: My guess is tha…
How to get value from a query of another table to create a new column (postgresql)
I am new to postgres and I want to be able to set value to Y if order (order table) is a first month order (first month order table) first month order table is as per below. It will only show the order placed by user the first time in the month: order table is as per below. It shows
Get word frequency rankings of words in postgreSQL and filter by label
I have a postgreSQL database with the following table: The following data is inserted: I have the following query that fetches the frequency of each word in the table and ranks them, grouping by day: Result: I would like to group by the label so that the new query shows the word rankings filtered by label lik…
How to fetch the the second row
I have a query below which is used to make a view. This query pulls the latest record of the report_id. One report_id can have multiple unique report_info_id This results in data something like Lets take the example of last record where report_id= 130 Now it is required that in first query I want such data if…
How can I get the names of authors of books with the shortest titles from this database?
Let’s suppose I have a database with the following tables And I want to get the names of authors of books with the shortest titles. I think I could take isbn numbers of books with the shortest titles, get the author_id and from this get the name. I would be glad for any help with this problem. Answer Wh…
Postgres get all elements where value matches from jsonb array
I think I am trying something simple, but after hours I just can’t get it to work. I have a table which contains a tags column, which is a jsonb array and looks like this: I now want to write a query which returns the full object to me, when the name matches the search string. So far I came