Im trying to write a SQL left outer join query where the left rows are selected based on the sum of a field in rows in the other (right) table. The other table has an id field that links back to the …
Tag: postgresql
Postgres: how to check for records in time buckets for multiple values
I have a POSTGRES table with roughly the following shape: sensor_id | recorded_at ———-+——————– A | 2020-01-01 00:00:00 A | 2020-01-01 00:03:00 B | …
How to sum a column in Postgres
I create this table and inserted in it. I use PostgreSQL. How to write a query to have what i need. I need total amount of each column. below it Thanks. CREATE TABLE testTable ( id INT, …
Query result with added column number auto incremented dynamically
How do I add extra column to get row number added to my query result and increment dynamically with query resulted rows. I have tried using How to generate serial number in a query? But the result is like below Sr.No is getting scuffled. Answer If you want the sequence number to be the same as the result set,…
sql duplicates within duplicates
Having a hard time wording my issue. But finding duplicates within duplicates is about as close as i can get without an example. ID is the id of the row. stringA,stringB are just arbitrary strings. dID,oID are FK relationship to other tables. I am trying to create a query that will give me all the rows where …
More than one row returned by a subquery when using STRING_AGG function
I get an error when trying to execute SELECT query in PostgreSQL 11 And authors_string should be fetched as a string value. It throwns an error ERROR: more than one row returned by a subquery used as an expression I guess it happens because of GROUP BY in subquery and can be handled with row_to_json function,…
Sequelize foreign key reference composite primary key
i’m trying to create with sequelize (postgre) ‘Ingredient’ table, with columns normalizedName/userId where normalizedName is unique per userId. The second table is ‘IngredientQuantity’, with columns …
extract “spells” from observed dates in PostgreSQL
I have a table like this | winner | year | |——–|——| | alice | 1999 | | bob | 2000 | | bob | 2001 | | alice | 2003 | | alice | 2005 | | alice | 2007 | I’d like to infer non-…
Postgres SQL query across different schemas
We have multiple schemas, I would like to run a simple count query across schemas such as: SELECT COUNT(col_x) FROM schema1.table WHENRE col_x IS NOT NULL I saw that I’m able to get all the schemas …
making sure only an existing user in the table can add a record in PostgreSQL
I have something like fb groups, where people can be moderators. If a user wants to add a moderator to a certain group, I’d like to check that first of all he himself is a moderator of this group. …