Assuming I have this schema: create table rental ( id integer, rental_date timestamp, customer_id smallint, return_date timestamp, ); Running this query returns strange …
Tag: postgresql
Efficient way to map new values to a sql query (i.e 4 columns each with same credentials)
First I am new to Postgres so I may be describing this problem incorrectly so please point me to an answer if it has already been answered. I have a database with 10 columns. Lets say 4 columns all use the same codes for values (i.e the letter a, b, d, e) I want to rename all of these in
SQL – how to limit query with LIKE to select a certain number of digits after the %?
I’m trying to parse model names from a table using LIKE. Each model value looks like new_model_01215, new_model_01557 etc. Where new_model_01 part will be the same for all values. And there will …
PostgreSQL string_agg, default value when no value found
I have a table like this: date number system_id 1 33.1 1 2 24.2 1 3 14.1 1 4 15.5 1 5 1113 1 1 4513 2 2 53….
Is it possible to improve the performance of this subquery?
For my application I have a topic table and a vote table. The vote options are -1, 0, and 1. I’m trying to find the amount of times each topic was voted -1 and 1. So to find out, I am doing many …
How do I generate an array containing the numbers 1 to n in Postgres
I’m hoping there exists something like this: > SELECT nrange(10) {1, 2, 3, 4, 5, 6, 7, 8, 9, 10} Similar to the range function in languages like Python and Racket. Of course, if no such function …
How to pulls related values in PostgreSQL?
Here is my problem. I have two tables like user and customer. Users can be related to some customer(s). For example, I have a user who relates to two customers, and the other user relates to three …
Postgres database migrations with vault
I am currently using vault to generate database credentials. The service itself handles migrations against the database. I end up with tables owned by the generated vault role. the database …
Using replace() inside a loop
I am trying to copy indexes from materialized views onto tables. This is the script I am trying to use: DO $$ declare indexdefname record; a text; b text; c text; begin for indexdefname in …
How to join generate_series and an array
I want to generate a list of columns using generate_series() and joining it to a specific array (a list of value, e.g. [1, 2, 3]), and my expected result looks like this: Day | Code 2019-08-01 …