This is a migrated thread and some comments may be shown as answers.

PostgreSQL serial column

3 Answers 78 Views
Data Access Free Edition
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Michael
Top achievements
Rank 2
Michael asked on 23 Nov 2010, 04:03 PM
Hi,

I port MSSQL "Music Store" database to PostgreSQL database for testing MVC2. Now tables have primary keys and is type of SERIAL. Now when I add new record, exception is raised for duplicate key value! In reality, it is added to a record earlier in AlbumId field, value of 0. Now adding a new record also trying to enter the 0! How can I get OA ORM to do a sequence of key columns?

Here is code for new album :
//
        // GET: /StoreManager/Create
  
        public ActionResult Create()
        {
            var viewModel = new StoreManagerViewModel
            {
                Album = new Album(),
                Genres = storeDB.Genres.OrderBy(b => b.Name).ToList(),
                Artists = storeDB.Artists.OrderBy(c => c.Name).ToList()
            };
              
            return View(viewModel);
        }

And here is for adding :
//
        // POST: /StoreManager/Create
  
        [HttpPost]
        public ActionResult Create(Album album)
        {
            if (ModelState.IsValid)
            {
                // Save Album
                storeDB.Add(album);
                storeDB.SaveChanges();
  
                return RedirectToAction("Index");
            }
            else
            {
                // Invalid - Redisplay with errors
                var viewModel = new StoreManagerViewModel
                {
                    Album = album,
                    Genres = storeDB.Genres.OrderBy(b => b.Name).ToList(),
                    Artists = storeDB.Artists.OrderBy(c => c.Name).ToList()
                };
  
                return View(viewModel);
            }
        }

Here is table definition :
CREATE TABLE "public"."Album" (
  "AlbumId" SERIAL, 
  "GenreId" INTEGER NOT NULL
  "ArtistId" INTEGER NOT NULL
  "Title" VARCHAR(160), 
  "Price" NUMERIC(10,2) NOT NULL
  "AlbumArtUrl" VARCHAR(1024) DEFAULT '/Content/Images/placeholder.gif'::character varying
  CONSTRAINT "PK__Album__97B4BE370AD2A005" PRIMARY KEY("AlbumId"), 
  CONSTRAINT "FK_Album_Genre" FOREIGN KEY ("GenreId")
    REFERENCES "public"."Genre"("GenreId")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE, 
  CONSTRAINT "FK__Album__ArtistId__276EDEB3" FOREIGN KEY ("ArtistId")
    REFERENCES "public"."Artist"("ArtistId")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITH OIDS;
  
CREATE INDEX "ArtistId_idx_Album" ON "public"."Album"
  USING btree ("ArtistId");
  
CREATE INDEX "GenreId_idx_Album" ON "public"."Album"
  USING btree ("GenreId");


Thanks, Michael.

3 Answers, 1 is accepted

Sort by
0
Damyan Bogoev
Telerik team
answered on 25 Nov 2010, 09:21 AM
Hello Michael,

I successfully reproduced this error on my side.
It seems that PostgreSql cannot find the correct sequence for the Album table because its name is not lowercase. You could see under the Sequences node that this sequence is named “Album_AlbumId_seq”. In order to avoid the error you should set the sequence’s name to lowercase("album_albumid_seq") and update your domain model using Update from database dialog.
I think that will help you.

Sincerely yours,
Damyan Bogoev
the Telerik team
Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
0
Michael
Top achievements
Rank 2
answered on 25 Nov 2010, 10:02 AM
Hi  Damyan,

Sequence name is automatic created by Database, if ORM can't work with mixed case I will change sequence name. Finaly i saw that exception about sequence name is not found ... But to get to that step, I had to manually in the generated model to explore and sets the IDENTITY column, because model mapping wizard is not have that made. It's a real problem. So I got the first entry in the column to 0 (zero) and when the new row is added again, exception is raised because ORM try to insert 0 (zero) again. He actualy did not understand that is sequence, than ordinary INTEGER field.

Michael.
0
Accepted
Damyan Bogoev
Telerik team
answered on 25 Nov 2010, 07:43 PM
Hello Michael,

We found the cause for this problem and have fixed it. Currently we are in a testing cycle of the service pack and I hope that we will be able to release it by the end of the week.
I am sorry for the inconvenience caused.

All the best,
Damyan Bogoev
the Telerik team
Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
Tags
Data Access Free Edition
Asked by
Michael
Top achievements
Rank 2
Answers by
Damyan Bogoev
Telerik team
Michael
Top achievements
Rank 2
Share this question
or