This is my query within my controller (index action): @tags = ActiveRecord::Base.connection.execute( <<~SQL SELECT t.id, t.name, t.member_tags_count, ( SELECT json_agg(mt.member_id) as member_ids FROM member_tags mt WHERE mt.tag_id = t.id) FROM tags t ORDER BY LOWER(t.name) SQL ) render json: @tags…
Working with external Postgres DB, I have only read permissions, so I have to deal with a poorly designed tables. I have a table: CREATE TABLE table ( user_id uuid NOT NULL, column_a boolean DEFAULT false, column_b boolean DEFAULT false,…
I have an orders table that has a primary key id column, an order_id column, and a created_date column: =================================== ORDERS =================================== id | order_id | created_date ----------------------------------- 1 | 178 | 2022-11-16 09:25:11 2 | 182 | 2022-11-18 08:44:19…
I am trying to open a random .sql file off the internet using the following command: psql -h localhost -d database_name -U postgres < file_name.sql But when I run this command I just get errors like the following: invalid command…
I'm using psycopg2 library to connection to my postgresql database. Every time I want to execute any query, I make a make a new connection like this: import psycopg2 def run_query(query): with psycopg2.connect("dbname=test user=postgres") as connection: cursor = connection.cursor() cursor.execute(query)…
How to read JSON object matching query params? I want to filter data for video urls which only contains query params from metadata column and replace it by the removing the query params. metadata {"video-url":"xyz.com/video/xy4jnj?pubtool=oembed","provider":"some-video","video-id":"x8cse6q"} {"video-url":"xyz.com/video/x8cse6q?pubtool=oembed","provider":"some-video","video-id":x8cse6q} {"video-url":"xyz.com/video/x8cse6q","provider":"some-video","video-id":"x8cse6q"} select * from…
I am getting the error below when I try creating a database. Here is the database.yml: default: &default adapter: postgresql pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 development: adapter: postgresql encoding: unicode database: MyDatabase host: localhost pool: 5…
So I got a super weird problem here. I spun up a new server to restore a backup (tried taking a new snapshot and restoring a backup few times since original server is doing well). create index on companies ((linkedin_name(company…
I try to create a relation between two tables in a Spring Boot Application. I have the following Code: @Entity @Table(name = "account") @Getter @Setter @AllArgsConstructor @NoArgsConstructor @Builder public class Account { @Id @Column(name="ID", nullable = false, updatable = false)…
I am using PostgreSQL 11.2. I have replication slots setup. I am able to commit to a table and see it on the standby. I have few more standbys. How can I see from the master what other standbys I…