I am using Sql-Server and Postgresql, usually when I need to run multiple commands, I open a new connection, and foreach needed query task I run a command (inserting 1000 rows, for example). Is it bad if I run multiple query commands in a single command(queries separated by comma) vs previous behavior? Answer…
Tag: postgresql
Get all posts with sum of votes and if current user voted each post
If have the following two PostgreSQL tables: Post table: postid | title | author | created Vote table: postid | username | vote where vote is equal to 1 if the user voted the post up, 0 if the user did not vote and -1 if the user voted the post down. I want to receive now for every post
join without a join in PostgresSQL
I have code with the following psql query that I try to understand (It was simplified for the question): Is it the same as doing a join, or am I missing something here? Answer You are doing an old school implicit join. This is the syntax used before the ANSI-92 SQL standard which started recommending explicit…
Giving a specific number to fields in my Db
I want to give specific unique numbers to the following field in my db. The column looks like: August 2020 August 2020 August 2020 August 2020 September 2020 September 2020 September 2020 October 2020 …
How to keep only one entry among several in PostgreSQL database
I have a database that monitors a network (snapshots table, that contains a snapshot_date column). This production database was flooded by a faulty crontab, resulting in many snapshots for the same device every day. I don’t won’t to remove everything, but i want to keep only one snapshot per snaps…
Calculating average with biginteger time intervals using TimescaleDB
I have a schema with the following fields: Name of row | Type ————————–+——– name | string value1 | numeric …
How to select size of json array in postgres?
I’ve been fighting this for a long time, but I am not able to construct a query, which would select (and preferably also order by) a count of items in an array. I do have table data like this: And what did I try… also But it sais I’ve tried a lot of different formats but coming from the MS
Group by portion of field
I have a field in a PostgreSQL table, name, with this format: JOHN^DOE BILLY^SMITH FIRL^GREGOIRE NOEL^JOHN and so on. The format is LASTNAME^FIRSTNAME. The table has ID, name, birthdate and sex …
How do I select just mutual rows in SQL?
I have a table with a source_id and destination_id and a message and I want to group messages together. There can only be one message between a given source_id and destination_id, but I only want rows that have a mutual response for a given ID (say id 1). In the following examples, I want rows #1, #2, #4 and …
Column must appear in group by or aggregate function in nested query
I have the following table. I am trying to query the following: For each year that appears in the Fights table, find the city that held the most fights. For example, if in year 1992, Jersey held more fights than any other city did, you should print out (1992, Jersey) Here’s what I have so far but I keep…