Tag: postgresql

Subquery on SELECT with conditional WHERE clause after it

So I have three tables, news : topics : and news_topics : And I want to return it like these : I succeed on returning that value using this query But there is one flaw to that query, on the above result there are one row that contains null topics. When I add AND topics IS NOT NULL after it

PostgreSQL select record with multiple condition

postgresql select sql where-clause

I am trying to select the record but not getting the record I wanted. here is my sqlfiddle http://sqlfiddle.com/#!17/5296a/1 so when I selects the record using below query it gives me result with the title DDDDD. it should give me GGGGGGGG. I wanted to check if there is any record with author_id=3 and greater than the post id=3 if not

SQL selecting date by month and year but select another month/year rows by column relation

postgresql sql

I have the following example in my database: workshop_class_id workshop_session_id workshop_class_date 209 29 2021-06-20 210 29 2021-06-21 213 31 2021-06-30 211 30 2021-06-30 214 31 2021-07-01 212 30 2021-07-03 219 33 2021-07-12 I would like to select by month and year, for example June 2021, but if the rows have relation with workshop_session_id and even if the date it’s not

Define enum as the column names of table PostgreSQL

postgresql sql

Is there a way to define an enum using a table’s column names? I have a users table defined as follows: And then I want to define a required_profile_fields column in another table to be an enum containing only the column names in that public.users table: Is there a way that I can define that profile_fields as an enum from

Is it possible to fetch and compare an element which resides in a nested Json string format column in database? Via SQL Query

java postgresql spring-boot sql

I have ‘a unique-id’. I want to fetch records from table on basis of that unique-id. I have a column named “request body” that contains a nested json string which is of type text. Is there any way i can compare ‘unique-id’ with the ‘unique-id’ inside the json string cloumn i-e request body? Answer Apologies, I am new to stackoverflow.

How to check if two dates are the same with timestamp

postgresql sql

I have two dates. One I get from the database, the other is which one I want to pass. How can I check if these two are the same? These are timestamp with time zones In this case they are the same. Answer

How can I query with NOT IN in a many-to-many relationship including NULLs

postgresql sql

Given I have 3 tables and I need to retrieve all the rows where there is no relationship with a specific record and include the rows where there are zero relationships, how can I build this query? I need all books where the author Dewey (id = A2) has no relation at all. Because B4 has a relation with author

Postgresql SELECT sample with FOR loop

postgresql sql

I have a task where I need to create a 1000 sample row limit query for each attribute selected. So for example if I have: I would need to turn this in to: Problem is that I have a lot of these attributes and that I might be adding more attributes later. Is there a better way of writing this?

Express-Postgres fails to apply array filter: error: operator does not exist: integer[] && text[]

express javascript node.js postgresql sql

The same query works on the terminal. But for some reason I’m getting an error: pg.query is unable to process the expression path && Array[$1] using this: https://www.postgresql.org/docs/9.2/functions-array.html Answer This should work: Why? Short answer: type resolution. Long answer: Concluding from the error message that comments.comment_id is of type integer. Forming an array from it with ARRAY[comment_id] produces an integer