I have a small database in PostgresSQL. And I connected to it via R-studio and try to retrieve data from database. I wrote a command, but it doesn’t work. And I don’t know what’s the problem. Please, help me. So, the code works well. But these two codes fail Where con is the postgres connect…
Tag: postgresql
Find data from json/jsonb column by array in Postgresql
I have a column with json/jsonb data. Database is postgresql. column data sample {“postId”: “p522”, “groupId”: “g11”, …} {“postId”: “p5ds”, “groupId”: “g234”, …} {“postId”: “p5cz”…
Select data from three tables only if one of them has reference to parent table
I have three tables person, his cars, his houses. And i need select persons with his cars and houses if one of childs(car, house) table have reference to parent(person). I tried with join, but don’t know how use OR in this condition. and result should be Answer You can left join twice and ensure that on…
Trying to filter null values in aliased column – postgresql?
have the following postgresql command: I would like to add in a where clause to filter out where the link value is not null – so it contains a value. when i run the following i get an error message: column link not found any ideas on how to solve this problem? Answer In a SQL SELECT query, the logical
PostgreSQL parse countries in array against the countries table
We have content and country tables. Country is pretty simple: country_name column defined as string: Albania, Belgium, China, Denmark etc… Content is a table with half a million of rows with various data with countries column defined as array text[]. Each value there has a number of countries concatenat…
I have 3 table and need exclusive left join
Table_1: Table_2: Table_3: Using 2 left joins I got this: But I do group over the first column and 8 is accumulated twice, I wish to get: I also wish to avoid UNION ALL because my first table is actually a complex query, I wish to avoid its recalculation. https://dbfiddle.uk/?rdbms=postgres_12&fiddle=d601…
Handle Group by different way in postgres
This query getting result like below But i want group by hour like Answer you can use case when hour to merge multi hours to time range then group it.
reuse of subqueries with different WHERE clause
I have a query which I looks schematically like this WITH sub_base AS ( SELECT type, CASE WHEN a < 10 THEN 'GOOD' WHEN a BETWEEN 10 AND 100 THEN 'OK' ELSE 'BAD' END AS a_q, … CASE WHEN …
Grant connect to all DATABASES
I’ve seen there’s a way to grant select to all tables within a database, however I can’t seem to find a way to grant connect to all databases. I can’t manually do: so on so forth, as I have many databases. Is there an equivalent to something like: GRANT CONNECT ON DATABASE * TO readonl…
SQL: Having multiple conditional Averages between two tables
I have two tables Sample from e.g. T1 ———————- ID || Operation Name | ———————-| 101 || QA | ———————- Sample from T 2 ————————-…