I am new to PostGIS. I have a set of (thousands) of Linestrings and associated multiple points along the line. I want to divide each line into a set of line segments consisting of only 2 points. I find a few answers, But none of the answers I want. for example https://gis.stackexchange.com/questions/21648/exp…
Tag: postgresql
postgresql total column sum
SELECT I have a SELECT like this. I need to get something like the one shown above. That is, I need to get the total of one column. I’ve tried a lot of things already, but nothing works out for me. Answer If I understand correctly you want a result where extra row with aggregated value is appended after…
Getting value error for INT when posting DataFrame values to psycopg2
I’m trying to insert a data to postgresql from a dataframe like this But getting this error code My sql table was created like this Answer The error says what’s the problem – the program expects integer and you pass list of integers there. It’s because execute is used to insert single …
How to compare multiple rows
I have a table with data like following, want to return those group_id with unique data. Both group_id 3 and 4 have two component 123 and 456, so they are “duplicated”, we just need to return the smaller group_id, that’s 3. Also group_id 5 doesn’t have a duplication, it can be returned…
Different behaviour of Numeric columns in postgres and Snowflake
I have a tbale both inm snowflake and postgres in postgres the numeric column is defined as numeric(20,6) in snowflake also it is defined as numeric(20,6) but when I am doing the sum of that column I am getting value like : Now again I jumped into snowflake and changed the column definition to (20,5) then, I …
How to select a row, lock it, update it, then select again?
I have a table with these 3 columns: task (string) status (string) date (datetime) I want to write a query that does the following: Selects the first row WHERE status != “In-Progress” Sorted by Date (oldest first), and Locks it – so other computers running this query concurrently can’t…
crosstab in PostgreSQL
I have the following tables: and How can I query the database so that it returns the table where n ranges over the rows of the table sensor and ‘abbrv $i’ is replaced by the corresponding value in the table sensor? The following query works up to a certain extent: I need to know how many rows ther…
Create query builder that the source table (FROM) is a join table in TypeORM
I’m trying to implement the following SQL in TypeORM using QueryBuilder: The entities are: When you create a query builder you have to use some entity or table but the join table is “hidden” by TypeORM I know I can replace the inner join table order and it will solve the problem but I’…
How to fetch all rows where an array contains any of the fields array elements
I have a table that has a column video_ids, it is of a bigint[] type. I would like to find all the rows that have any of the elements from the array passed in a select statement. So, if I have a row that has a video_ids field that looks like this: I would like to fetch it if I
Filtering out objects from multiple arrays in a JSONB column
I have a JSON structure with two arrays saved in a JSONB column. A bit simplified it looks like this Schema: Since each row (data column) can be fairly large, I’m trying to filter out matching item objects and group objects from the items and groups arrays. My current query is this which returns rows co…