Relation does not exist The cause of error: There is no table created with the specified name. Check the table name or use IF EXISTS. Wrong alter ALTER TABLE test.clients RENAME TO customers; Messages ERROR: relation “test.clients” does not exist Correct alter ALTER TABLE IF EXISTS test.clients RENAME TO customers; Messages NOTICE: relation “clients” does not exist, skipping… Read More »
Constraint of relation does not exist The cause of error: There is no constraint created with the specified name. Check the constraint name. Wrong drop ALTER TABLE test.customers DROP CONSTRAINT fk_address_test; Messages ERROR: constraint “fk_address_test” of relation “customers” does not exist Correct drop ALTER TABLE test.customers DROP CONSTRAINT fk_address2; Messages ALTER TABLE
Constraint for relation already exists The cause of error: There is already a constraint created with the same name. Check the constraint name or drop the existing constraint. Error message example ALTER TABLE test.customers ADD CONSTRAINT fk_address FOREIGN KEY (address_id) REFERENCES test.customer_address (id); Messages ERROR: constraint “fk_address” for relation “customers” already exists
Multiple primary keys for table are not allowed The cause of error: There is already a primary key created. Check the primary key or drop the existing primary key. Create table and primary key CREATE TABLE test.products( product_id numeric, product_name varchar(500), category varchar(100), price numeric ); ALTER TABLE test.products ADD PRIMARY KEY (product_id); Messages CREATE TABLE ALTER TABLE… Read More »
PostgreSQL column specified more than once Column specified more than once Duplicate column The cause of error: Column specified more than once. The solution is to remove duplicate columns. Wrong insert INSERT INTO test.students (id, first_name, first_name) VALUES (6, ‘Paul’, ‘Paul’); ERROR: column “first_name” specified more than once LINE 1: INSERT INTO test.students (id, first_name, first_name) VALUE… Query… Read More »
PostgreSQL column must appear in the GROUP BY clause or be used in an aggregate function Column must appear in the GROUP BY clause or be used in an aggregate function GROUP BY clause The cause of error: Column must appear in the GROUP BY clause or be used in an aggregate function The solution is to add… Read More »
PostgreSQL INSERT has more target columns than expressions INSERT has more target columns than expressions in PL/pgSQL INSERT has more target columns The cause of error: INSERT has more target columns than expressions in PL/pgSQL. The solution is to check the number of columns from the insert row. Create Students table CREATE TABLE test.students ( id numeric NOT… Read More »
PostgreSQL null value in column violates not-null constraint Null value in column violates not-null constraint in PL/pgSQL Not-null constraint The cause of error: Null value in column violates not-null constraint in PL/pgSQL. Create Students table CREATE TABLE test.students ( id numeric NOT NULL, first_name character varying(50) NOT NULL, last_name character varying(50) NOT NULL, entry_date timestamp without time zone… Read More »
PostgreSQL duplicate key value violates unique constraint Duplicate key value violates unique constraint in PL/pgSQL Duplicate key The cause of error: Duplicate key value violates unique constraint in PL/pgSQL. The solution is to add unique values when you make insert or update on the table. Create Students table CREATE TABLE test.students ( id numeric NOT NULL, first_name character… Read More »
LOOP Syntax and examples of conditional Loop – End Loop. CREATE OR REPLACE FUNCTION get_loop(in p_id numeric) RETURNS varchar AS $$ DECLARE j numeric; v_name varchar(50); BEGIN j:=0; FOR i in 1..p_id LOOP j:=j+1; v_name:=’The count is: ‘||j; END LOOP; return v_name; END; $$ LANGUAGE ‘plpgsql’; Control structures | IF | IF Else | IF Elsif Else CASE