I want to count certain values in all tables of a schema that contain a column that can contain those values. Was hoping to use a LATERAL join to loop over all tables, but I'm running into issues: select fully_qualified_table_name,…
I have a text field in a table that contains JSON data as well as XML data. As I want to work with XML data only if it's valid XML, I want a way to make sure I can cast…
CREATE VIEW A1 AS SELECT client_ID , COUNT(dog_id) FROM test_clients GROUP BY client_ID HAVING COUNT(dog_id)=2; CREATE VIEW A2 AS SELECT filial , COUNT(A1.client_ID) FROM A1 JOIN test_clients USING (client_ID) GROUP BY filial HAVING COUNT(A1.client_ID)>10; SELECT COUNT(filial) FROM A2; As far…
I wanted to retrieve all the vertices from two different graphs I've created (one is called family_tree and the other is taxonomy_biology) and used the following command to do so : SELECT * FROM cypher ('family_tree' AND 'taxonomy_biology', $$ MATCH…
I have a table name ar for column operation in it I can allow only specific values ('C', 'R', 'RE', 'M', 'P'). I have added a check constraint for it. Requirement: I need to insert 1 million records in the…
I don't understand this comment: My understanding is that read-write transactions carry some overhead, but that you don't incur this overhead until you actually write something. In other words, in terms of performance, a READ ONLY transaction should be the…
I have a financial system where users have tokens and can add transactions. The system has to calculate the balance and mean acquisition price of each token. Data integrity is of utmost importance in the system and it should be…
I'm using PostgreSQL and I have a table with the following columns: id, distance, and length. I want to order the table by distance and create a new column called cum_length using a window function. I also want to filter…
PostgreSQL 15.0 I want to make a query that concats two different columns into one. Desired outcome I showed in the exapmle (it's not a real one but it would be useful to understand on this example). I've used CONCAT…
I'm starting learn Go and SQL. I tryed to make initial migration using golang-migrate in my go project. Database is postgresql This is migration file: CREATE TABLE users ( id serial not null unique, name varchar(255) not null, username varchar(255)…