Search Postgresql Archives

Problem with UPDATE and UNIQUE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi all

I have a problem, which I suspect stems from bad design.

If I explain what I am doing, perhaps someone can suggest a better approach.

I want to store data in a 'tree' form, with a fixed number of levels, so
that each level has a defined role.

I have the following (simplified) table -

CREATE TABLE treedata (
  rowid serial primary key,
  levelno int not null,
  parentid int references treedata,
  seq int not null,
  code varchar not null,
  description varchar not null
  );

The 'root' item has a parentid of null, all other items must have a valid
parent. Items with a levelno of 0 represent raw data, higher levelno's
represent grouping levels. The seq indicator is used to display data in a
defined order.

To describe each of the levels in the tree, I have the following table -

CREATE TABLE treelevels (
  levelno int primary key,
  code varchar unique not null,
  description varchar not null
  );

Typical values for this table could be -
  (0,'Prod','Product code')
  (1,'Cat','Product category')
  (2,'*','All products')

Now for the problem. I want to insert or delete levels dynamically. I can
insert or delete levels in 'treedata' without a problem. However, I also
want to insert or delete a level in 'treelevels'.

Say I want to insert a level between 'code' and 'category' called 'group' -

INSERT INTO treelevels VALUES (1,'Group','Product group');

Obviously this will fail with a duplicate levelno. Therefore before the
insert statement I want to do this -

UPDATE treelevels SET levelno = (levelno+1) WHERE levelno >= 1;

The problem is that if there are a number of levels, and they are in
indeterminate order, I can get duplicate level numbers while the command is
being executed.

My workaround at present is the following -

UPDATE treelevels SET levelno = (levelno+10001) WHERE levelno >= 1;
UPDATE treelevels SET levelno = (levelno-10000) WHERE levelno >= 1;

It works, but it feels very ugly.

Any suggestions will be much appreciated.

Thanks

Frank Millman


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux