Learning CTE right now, the following query is super basic and it doesn’t really have any useful value but I don’t understand why it doesn’t work The error I’m getting is “ERROR: column “cte_actors” does not exist” Currently using postgres 14 and DBeaver Answer You need to SELECT from the CTE:
Tag: postgresql
Grouped aggregate counts for each date in a series of dates
I am trying to get grouped task counts by state over a series of dates using the following tables: There isn’t a consistent daily “state” record for each task because task_logs only has entries for when a task changes state. This means I have to get the last “state change” log for each task prior to a specified date. I’ve
Replace all email addresses after ‘@’ in PostgresSQL
I have a list of emails. I want to change all of them to test emails for my test system e.g. sam@gmail.com to sam@test.com. I don’t want to use actual emails as those emails are valid and it will be bad for users to receive such email. Is it possible to change all emails at once in a single query?
Continuous subset and ranking using sql
I have a dataset like below: Now, I need the output as below: For this purpose, I wrote a query but it is not giving me the desired sequence. My query is as below: It generates the output as below: But in the rank section I need something as below: So, what exactly am I doing wrong here? Here are
How do I use lag to get the previous row before a specific time window of data?
Every day I create a table that looks like this: user_id received_at age_pref ethnicity_pref 1 10:01 18-28 open_to_all 2 10:05 18-23 open_to_all 1 10:08 18-30 open_to_all 2 10:07 18-25 Hispanic/Iatino 3 10:09 56-33 White It’s a table that lists the actions a user takes from 10am-11am. As you can see, there are 3 distinct user IDs. Using this, I’m trying
Postgres: Searching all sub-arrays of an array
I have a Postgres table with a json column, named raw_data, that is structured like this: [{“id”:1234, “name”:”John Doe”, “purchases”:12}, {“id”:1234, “name”:”Jane Doe”, “purchases”:11}] The number of sub-arrays can differ. It may be 1 or 20 or any number. I want to perform a query where I can return the entire table row if, say, ‘John Doe’ occurs in the
PostgreSQL- If data in table, delete for stored procedure
I have this procedure, which I call with CALL pr_calc_quarter(2,2022), that insert data into a table erp.tb_quarter from a query. I need to add code to check that if there is already data in the table, to delete it before inserting new values. I was thinking of inserting the code at the beggining or before the insert into, but I
PostgreSQL get all records with most recent date
I have this query that will run on very large data sets and it is too slow. table A looks something like this pk columnA columnB columnC 1 5/6/2022 1234 1 2 5/6/2022 1234 2 3 5/5/2022 0000 3 4 5/3/2022 0000 4 There are about 1000 distinct entries in columnB and many orders of magnitude more in the table.
Sql / Finding Min Max values for each elements of another column
Learning sql, as per title trying to print min and max values of a given column (sal) for each values in another (nodept). Using this query so far: Which works but only returns absolute min max and not min max for every entry in nodept. Documentation says it should be a simple matter of adding GROUP BY to the query
Remove schema name from DBeaver’s query
I am new to DBeaver and I am using it for the postgres client. I was using Navicat before and in navicat the query simply used to run without schema name as SELECT * FROM TABLE_NAME . However, I am having issues with the Navicat and I recently switched on DBeaver(22.0.4 mac). With DBeaver, I cannot execute query without schema