How to delete rows from my customer table depending on values from another table, let’s say orders? If the customer has no active orders they should be able to be deleted from the DB along with their rows (done using CASCADE). However, if they have any active orders at all, they can’t be deleted. …
Tag: postgresql
insert values in the last column for my table from another table
Hi I’m trying to insert values in the last column for my table from another table but am getting error ERROR: null value in column “name” violates not-null constraint DETAIL: Failing row contains (ddf1caf0-26c2-49e1-8a73-64227eae1f50, null, null, null, null, null, 2532). Answer I suspect tha…
SQL’s union default order by clause?
I noticed when I am combining two tables via union that the order is not what I expected. calculus_class stats_class When I combine the two tables above I expect the results to be in order from top to bottom like this: This is the result I received using DBeaver PostgreSQL: Answer Actually, you are using unio…
How to write a SQL query which uses the JSON format of table a_json to populate table b with its respective values in postgresql
I have a file named data.json. The structure is like this: { “PrimKey1”: { “layout”: “normal”, “col1”: “PrimKey1”, “__colll2__”: “sometext”, “col3”: 9, “OTHERCOLUMN”:”dontneedthis”, “&…
Reset Postgres auto-increment value to 0
I’m having a table where I sometimes enter data in inline editors where I manually add the id value which is usually auto-incremented. In a case where id 4 was auto generated using scripts and then …
How do I get rid of this SQL error from the end of my query when inserting within a procedure?
Not sure if this is even possible but im getting a weird error at the end of my query. Any idea why? Here is the code: CREATE OR REPLACE PROCEDURE NewUser (g_name VARCHAR, f_name VARCHAR, u_password …
Can’t access to schema in postgres
Lets say that I made this “simple” implementation of a DB in Postgres postgres=# CREATE ROLE my_role; CREATE DATABASE my_db; GRANT ALL ON DATABASE my_db TO my_role; …
In pgadmin EXPLAIN ANALYSE , exclusive vs inclusive
As showed below, I tried to optimise my query based on EXPLAIN ANALYSE function of Pgadmin. Can someone experienced with it tell me the difference between INCLUSIVE and EXCLUSIVE in Timings? And why …
Postgres – Geospatial Search in LINQ or SQL with PostGis
I am using PostGis to search for all points within a given range, relative to a given origin. I am looking at all the documentation provided by PostGis but can’t seem to figure out how would I come up …
How to ignore duplicates without unique constraint in Postgres 9.4?
I am currently facing an issue in our old database(postgres 9.4) table which contains some duplicate rows. I want to ensure that no more duplicate rows should be generated. But I also want to keep the duplicate rows that already has been generated. Due to which I could not apply unique constraint on those col…