ERROR: 42P01: relation [x] does not exist

General Questions/Discussions
1

Hello,

I’m getting an exception in the ORM generated sample code when trying to Save operation in CreateDbTestData . I’m using PostgreSQL 8.2 with .NET 2.0 (solution needed conversion from .NET 1.1). My database name is “dbtest” with a single table “dummy”. The ListDbTestData sample works properly, so my connection to the database is good.

My .postgresql setup:

CREATE TABLE dummy (
	id	integer primary key,
	smth	varchar(30)
);

I receive this exception:

{"ERROR: 42P01: relation \"public.dummy_id_seq\" does not exist"}	System.Exception {Npgsql.NpgsqlException}

During the “Save” call in this code in ormsamples.CreateDbTestData:

				
MyPackage.Dummy myPackageDummy = MyPackage.DummyFactory.CreateDummy();
// Initialize the properties of the persistent object
myPackageDummy.Smth = "Something";
myPackageDummy.Save();

I’m an experienced programmer, but new to ORM, dB, and Visual Paradigm.

Thanks,
Eric

2

I’ve found a fix. I had to manually add a SEQUENCE to the database, named:

__seq.

It is possible to override the sequence generator. Go to the ERD and right click the primary key and select “Open Specification”. ID generation is control by the “ID Generator” field. Some selections require you to enter a custom key. For example, if you select “sequence” you can manually enter “my_seq” as the key. This should correspond to a CREATE SEQUENCE command in your SQL setup code.

Here is my new SQL setup code:

CREATE TABLE dummy (
	id	integer primary key,
	smth	varchar(30)
);

-- workaround for VP DB-VA error:
-- 	"ERROR 42P01: relation \"public.dummy_id_seq\" does not exist"
CREATE SEQUENCE dummy_id_seq
   INCREMENT 1
   START 1;

Is this the recommended way of setting up the dB primary key sequence generation? I’m assuming that the VP database generator does this automatically, and that I’m only seeing it because I’m handcrafting my dB.

Thanks,
Eric

3

Hi bdubs,

You are correct. Another method is “create table dummy (id serial not null, primary key (id));”, it will auto generate “dummy_id_seq” sequence.

Hope this helps.

Best regards,
Jick

4

Jick,

Thanks for the response. Yes, this is very helpful. Your method is easier because “DROP TABLE dummy” will drop both the TABLE and the implicitly created SEQUENCE. This makes my mainenance/setup .postgresql files simpler.

Thanks,
bdubs

5

Alternatively, you can create it as a serial, which will automatically generate the sequence.

CREATE TABLE dummy (  
    id  serial,  
    smth    varchar(30),
    CONSTRAINT "PK_Dummy_Id" PRIMARY KEY (id)
);