What’s the easiest way to create a new Postgres scheme inside the database on the runtime and also, create the tables written inside a SQL file? This is a Spring boot application and the method receives the schema name that needs to be created for the db. Answer Although it sounds like this would be a c…
Tag: sql
Find groups that are missing values of one column
Hopefully someone can send some light on an issue I’m trying to resolve. Types Codes I would like to be able to determine those Code/ID pairs that are missing a particular type. The result of this query should yield. Answer You need conditional aggregation for this: Another option is to cross join the p…
I am dropping the with check option constraint from view but it is not being enforced
So I am stuck in a problem. I defined a view with the WITH CHECK OPTION constraint, now i wish to delete it, but even after dropping the constraint i get the error :=ora-01402: view WITH CHECK OPTION where- clause violation. this is the sequence of SQL commands i am executing – I cant figure why this is…
Error(8,1): PLS-00103: Encountered the symbol “SET”
I’m trying to create a function using oracle, it should be working fine but i keep getting the following error: Error(8,1): PLS-00103: Encountered the symbol “SET” here is my Function: Answer The problem is where do you put this command: SET SERVEROUT ON; cause its inside the function declar…
How do i write this in python and preferably in pandas?(Assume that i am dealing with a dataframe)
This is the code that i am trying to convert to Pandas: Assume the following column for input data frame: geo | region | sub region | txn_date | revenue | profit. Columns in output dataframe : geo | region | ytd_rev | py_ytd_rev| total_profit Answer I believe you need GroupBy.agg with named aggregation and ne…
Use extra columns in INSERT values list in ON CONFLICT UPDATE
I have an UPSERT query where I want to insert value y value in column b, but if it already exists I want to update it with value z. How can I achieve this? P.S: A simple value list (without select) did not work because we cannot have more columns in values list than we are inserting. Answer In the
SQL performance impact on multiple columns in constraint or unique index
I created a table in postgres that includes various columns that based on an import from a csv. Since the csv can be re-uploaded with some changes and I don’t want the re-upload to create duplicates on rows that didn’t change, I added a constraint and unique index with majority of the columns incl…
Group query in subquery to get column value as column name
The data i’ve in my database: I ran a query to group by id and status to get the below result: I want to use the above query as subquery to get the result below, where the distinct status are column name. Any other approach to get the final data is also appreciated. Let me know if need more info.
Delete a users data from SQL using PHP
Hi im trying to delete a users booking detials when the user clicks delete in my bookingbeforedeltion.php file but for some reason when I test my php file once I click delete it goes to my delete.php screen and says it failed to delete from database and has the error Undefined index: rn. Is my rn not defined?…
How to read from the two same dates without input the time
I have a query like below. When I submit a date like the following patients.created_at BETWEEN ‘2018-05-22’ AND ‘2018-05-22’ it doesn’t return anything but if I enter BETWEEN ‘2018-05-22’ patients.created_at ‘AND’ 2018-05-23′ it returns a value. I th…