postgresql: relation does not exist

Question

I'm new using pl pgsql, with my pseudo code below i'm getting this error:

relation "Client.seniority_day" does not exist

How to resolve it please?

code

DROP FUNCTION IF EXISTS table_sen_seniority;
CREATE OR REPLACE FUNCTION Client.table_sen_seniority(granularity text, OUT max_date date)
RETURNS record
LANGUAGE plpgsql
AS $function$
declare
    table_sen text := 'Client.seniority'||granularity;

 BEGIN
     execute format('SELECT MIN(dat_sub) FROM %I', table_sen) INTO subscription_from;
     execute format('SELECT FROM %I WHERE date_ >= $1', table_sen) using subscription_from;
 END;
$function$
 ;

update

execute format('SELECT FROM %I WHERE date_ >= $1', table_sen) using subscription_from;

The error seems pretty clear. Why are you trying to select from a table that doesn't exist? — Gordon Linoff
– Gordon Linoff, Commented Feb 3, 2021 at 13:00
.@gordon Linoff but my table exist. :D I have table seniority in my schema Client — monkey2
– monkey2, Commented Feb 3, 2021 at 13:01
The need to do this, might indicate a questionable data model. I would expect a column named granularity in a table seniority (not different tables with different suffixes) — user330315
– user330315, Commented Feb 3, 2021 at 13:08

user330315 · Accepted Answer · 2021-02-03 13:06:06Z

2

You need to pass the schema and the table:

table_sen text := 'seniority'||granularity;

execute format('SELECT MIN(dat_sub) FROM %I.%I', 'Client', table_sen) 
  INTO subscription_from;

Note that "Client" and client would be different schema names.

answered Feb 3, 2021 at 13:06

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

monkey2 Over a year ago

Now i have this error. relation "seniority" does not exist.

Laurenz Albe · Accepted Answer · 2021-02-03 13:09:53Z

1

Your code results in

SELECT MIN(dat_sub) FROM "Client.seniority_day"

which treats the whole string as a table name, not a table called seniority_day in a schema called client.

You should do it like this:

DECLARE
   table_sen text := 'seniority' || granularity;
BEGIN
   EXECUTE format('SELECT MIN(dat_sub) FROM %I.%I', 'client', table_sen)
      INTO subscription_from;

That will result in

SELECT MIN(dat_sub) FROM client.seniority_day

answered Feb 3, 2021 at 13:09

Laurenz Albe

256k22 gold badges310 silver badges386 bronze badges

9 Comments

monkey2 Over a year ago

Thank you for your help :) but got error saying that relation seniority does not exit too. that probably came from my second query .

monkey2 Over a year ago

i have edited my original post please have a look if you have time .

Laurenz Albe Over a year ago

Do not change your original question. Now your code doesn't match your text and my answer. Rather, add additional information.

monkey2 Over a year ago

Sorry im not used with . Shall i rechage as before?

Laurenz Albe Over a year ago

Of course, you will have to modify your second query in a similar fashion. Did you understand the point of my answer? Your code treats the string Client.seniority_day as the name of that table, but in reality the name is seniority_day.

|

Collectives™ on Stack Overflow

postgresql: relation does not exist

2 Answers 2

1 Comment

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

9 Comments

Related