Loading
Forums in PostgreSQL
Aggregate query on a tree implementation as a relation (sql table)
Given the following tree structure in a SQL table, and assuming the data is consistent (there are no rows with the same name, but different parents): | name | parent | value | |------|--------|-------| | a | null | 10 | | b | a | 15 | | b | a | 4 | | c | a | 15 | | d | a | 10 | | e | b | 5 | | f | b | 5 | | g | null | 20 | I am looking for a query to sum up all sub-categories of a given node, like this: | name | parent | value | |------|--------|-------| | a | null | 64 | | b | a | 29 | | c | a | 15 | | d | a | 10 | | e | b | 5 | | f | b | 5 | | g | null | 20 | So, I can make only the first level of summation, and I can think of joining this to table itself on parent and and sum again... but I am looking for a solution for trees of unspecified depth. For the level 1 I have for example: SELECT NAME, PARENT, SUM(VALUE) AS VALUE FROM TEST GROUP BY NAME, PARENT ORDER BY NAME ASC;
SQL Join in LINQ
Hi, I watched your post SQL Join in LINQ (LINQ to Entity / LINQ to SQL) how can I do this in PostgreSQL thanks
column varchar(4000) is giving me a error of snapshot unknown
Hi guys. I have a column with varchar(4000) and the out is giving me a error of snapshot unknown What's the best datatype to store large data in postgres
how I can change the PK from one column to another
Could you explain to me how I can change the PK from one column to another, without deleting the column? I made a mistake in the column when assigning the PK and I have to change it
way to monitor the progress of foreign key constraint creation progres
Hello Everyone, is there a way to monitor the progress of foreign key constraint creation progress.?
how to optimize the query
I just tested it myself: WITH arr AS ( SELECT array_agg((random()*18000000)::bigint) AS id FROM generate_series(0, 10000) ) SELECT * FROM arr JOIN foxo.file ON file.id = ANY(arr.id) Query complete 00:01:13.853
Can any one say where is the mistake I did
I have created a function in such way that it call triggers where it needs to update the a column in same table after the update But after update clause is not working for me Where as my tigger is working for before update and insert clause create or replace function inbound.update_lastmodified_date() returns trigger LANGUAGE plpgsql AS $$ BEGIN new.last_modified_date=now(); RETURN new; END; $$; create or replace trigger test_trigger after update on inbound.testtriggers for each ROW EXECUTE PROCEDURE inbound.update_lastmodified_date() This is plpgsql code, Can any one say where is the mistake I did. Because the column is not get updated with current time after the update happening on table
How to get values which are not in English language?
How to get values which are not in English language? Data stored in mix language in the table. Please help
Can anyone help me with the query
able1: user_info = user_id, user_scubscription_type(trial/paid) table1 user_id = primary key table2 user_id = foreign key objective: To identify the 3rd highest feature is being used by trial users table2: feature_usage = feature_id, user_id, Apart from using limit what's the other way of writing query I think we can even use row number function rt Can anyone help me with the query
Hi any solution to this where I need to change
with median as (select lat_n, row_number () over (order by lat_n asc) as row_asc,row_number () over (order by lat_n desc) as row_desc from station ) select round(avg(lat_n),4) from median where abs(row_asc-row_desc) <= 1 ERROR 1690 (22003) at line 1: BIGINT UNSIGNED value is out of range in '(median.row_asc - median.row_desc)' while doin the same in postgresql the error shows- ERROR: function round(double precision, integer) does not exist LINE 7: select round(avg(lat_n),4) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 152
Could you please help me to resolve this error
Connection to localhost:5432 refused. Check that the hostname and port are correct and the postmaster is accepting TCP/IP connection.
column rollback to it's default value after a specific amount of time
hi, is there a way that i could set a timer so a column on a row would rollback to it's default value after a specific amount of time ? in postgres
Which emp made more number of sales using joins with customer table
Hi how to write a query to know who is the employee who made more number of sales using joins with customer table
error: row is too large: size 1.., maximum size 81...
Hello everyone!. I have a doubt to see if you can solve it for me. I am trying to import a .csv file using the \COPY command, however I get the following error: row is too large: size 18448, maximum size 8160. As I see it, the only possible solution would be to increase the blocksize, but the only way I see doing so is uninstalling postgres and reinstalling it from scratch compiling from the source code. Is there any way to make it less "invasive"?
best approach to design a table to insert an image in a postgresql tbl
Hi Team, could you please advise what would be the best approach to design a table to insert an image / picture in a postgresql table
Bulk Insert with txt file
In postgreSQL, Can any provide bulk insert example with txt file , delimeter with colon (:)
I'm getting an error:= column "row_number" does not exist
Select v_max_count +row_number, '1', Current_date From dual; I'm getting an error:= column "row_number" does not exist, Please help me
