I have a table of store information describing which stores are linked with one another. The data might look like this: | store_id | link_num | linked_store | | 1 | 1 | 10 | | …
Tag: postgresql
How to flat a subarray in Postgres?
select array[[1,2],[2,3]] Output: -[ RECORD 1 ]——– array | {{1,2},{2,3}} How do I flat the array, so I can then unnest? Expected {1, 2, 2, 3}
PostgreSQL(function) – Implementation of where clause to get rows by a value(concatenate) in the JSONB column
I am trying to get the rows from a table where I want the condition to check for a value from a jsonb column. The column stores the data as: In the function, I check for the value using: but I want the 420 to be replaced with “ID” which I pass through the function. The only way I came
SQL statement to Create Role fails on Postgres 12 using Dapper
I am running Postgres 12 on Windows and have a .Net Core app which uses Dapper as an ORM: The following query works fine: Now I’m trying to execute an sql statement that would create a role: This query fails with the following exception: Npgsql.PostgresException: ‘42601: syntax error at or near &#…
I cant do a select by one of its parameters even if it exists in the database. Encoding issues? PostgreSQL
I have a postgresql database snapshot with the following structure But it has some weird issues, for example I have this data Some example of data is: Well the problem is now that I do this select 0 returns But if i do It does return the result What is going on? It works with some addresses, but doesn’t…
merge values in select
I have next part of query: SELECT types, count FROM … Result is next: types count soft 3 lite soft 2 middle soft 7 hard soft 2 other 5 what I need is to merge results …
Why were all rows deleted when trying to delete old rows with date query
I have a table with column name date of type timestamp I’m trying to delete old rows with the following query: delete from my_table where date<(now()-'30 days'::interval) but all rows are deleted. …
Capture logging for stored procedure in Postgresql
We are working on a reporting project where we are executing stored procedures to perform transformations on the source data and convert it into the desired format. The transformed data will be inserted into a different table. We would like to know what are the best practices that needs to be implemented in t…
Postgresql: more than one row returned by a subquery used as an expression?
I have a query to find the youngest generation in a family tree. This is my database ID NAME PARENT_ID 1 A 0 2 B 1 3 C 1 4 D 2 5 E 3 6 F 3 7 G …
How to sort numbers last in PostgreSQL?
Found many related answers, but nothing that did this. How to sort by numbers last: And, preferably (but not necessary) PostgreSQL 11.9 Also, probably don’t want to use a regex for performance reasons. Don’t want to create an index either… Answer I think a regex is the right way to do this: …