changing sequence in serial field
This is a discussion on changing sequence in serial field within the pgsql Admins forums, part of the PostgreSQL category; --> Hello I noticed, that some serial field is using wrong sequence, so I altered the default value pointing to ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
07-04-2008, 07:50 AM
| ||||
| ||||
 changing sequence in serial field Hello I noticed, that some serial field is using wrong sequence, so I altered the default value pointing to the sequence I want, but now I cant delete currently no longer used sequence. Did I do someting wrong changing it? PgSQL 8.1 on Gentoo Linux. What I try to do DROP SEQUENCE b_prekes_br_kodas_pbk_id_seq1; Result ERROR: cannot drop sequence b_prekes_br_kodas_pbk_id_seq1 because table b_prekes_br_kodas column pbk_id requires it HINT: You may drop table b_prekes_br_kodas column pbk_id instead. ********** Error ********** ERROR: cannot drop sequence b_prekes_br_kodas_pbk_id_seq1 because table b_prekes_br_kodas column pbk_id requires it SQL state: 2BP01 Hint: You may drop table b_prekes_br_kodas column pbk_id instead. -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin      |
|
07-04-2008, 07:50 AM
| |||
| |||
| Re: changing sequence in serial field Στις Thursday 03 July 2008 17:19:48 ο/η Julius Tuskenis *γραψε: > Hello > > I noticed, that some serial field is using wrong sequence, so I altered > the default value pointing to the sequence I want, but now I cant delete > currently no longer used sequence. Did I do someting wrong changing it? > > PgSQL 8.1 on Gentoo Linux. > > > What I try to do > DROP SEQUENCE b_prekes_br_kodas_pbk_id_seq1; try ALTER SEQUENCE b_prekes_br_kodas_pbk_id_seq1 OWNED BY NONE; first, adn then drop. > > Result > ERROR: cannot drop sequence b_prekes_br_kodas_pbk_id_seq1 because table > b_prekes_br_kodas column pbk_id requires it > HINT: You may drop table b_prekes_br_kodas column pbk_id instead. > > ********** Error ********** > > ERROR: cannot drop sequence b_prekes_br_kodas_pbk_id_seq1 because table > b_prekes_br_kodas column pbk_id requires it > SQL state: 2BP01 > Hint: You may drop table b_prekes_br_kodas column pbk_id instead. > > -- > Julius Tuskenis > Programavimo skyriaus vadovas > UAB nSoft > mob. +37068233050 > > -- Achilleas Mantzios -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |
|
07-04-2008, 04:38 PM
| |||
| |||
| Re: changing sequence in serial field Hello Achilleas. I think you are wrong on this point - I can create some sequence (the owner is set) and then drop it easily. I think this is because b_prekes_br_kodas_pbk_id_seq1 was created automatically creating serial field and is in some way connected to it. Thats why I get error saying "...column pbk_id requires it." Any way I can't find a way to find and alter this dependency. Achilleas Mantzios rašė: > Στις Thursday 03 July 2008 18:19:27 ο/η Julius Tuskenis *γραψε: > >> Sorry - that doesnt work. >> > That was in 8.3.3 > Your mileage may vary. But the key is to get rid of the dependency. > >>> try >>> ALTER SEQUENCE b_prekes_br_kodas_pbk_id_seq1 OWNED BY NONE; >>> first, adn then drop. >>> >>> >>> >> ERROR: syntax error at or near "OWNED" >> LINE 1: ALTER SEQUENCE b_prekes_br_kodas_pbk_id_seq1 OWNED BY NONE; >> ^ >> >> ********** Error ********** >> >> ERROR: syntax error at or near "OWNED" >> SQL state: 42601 >> Character: 46 >> >> >> I also tried ALTER table b_prekes_br_kodas_pbk_id_seq1 OWNER TO NONE; >> but this gives: >> >> ERROR: role "none" does not exist >> >> ********** Error ********** >> >> ERROR: role "none" does not exist >> SQL state: 42704 >> >> >> > > > > Achilleas Mantzios rašė: > Στις Thursday 03 July 2008 17:19:48 ο/η Julius Tuskenis *γραψε: > >> Hello >> >> I noticed, that some serial field is using wrong sequence, so I altered >> the default value pointing to the sequence I want, but now I cant delete >> currently no longer used sequence. Did I do someting wrong changing it? >> >> PgSQL 8.1 on Gentoo Linux. >> >> >> What I try to do >> DROP SEQUENCE b_prekes_br_kodas_pbk_id_seq1; >> > > try > ALTER SEQUENCE b_prekes_br_kodas_pbk_id_seq1 OWNED BY NONE; > first, adn then drop. > > >> Result >> ERROR: cannot drop sequence b_prekes_br_kodas_pbk_id_seq1 because table >> b_prekes_br_kodas column pbk_id requires it >> HINT: You may drop table b_prekes_br_kodas column pbk_id instead. >> >> ********** Error ********** >> >> ERROR: cannot drop sequence b_prekes_br_kodas_pbk_id_seq1 because table >> b_prekes_br_kodas column pbk_id requires it >> SQL state: 2BP01 >> Hint: You may drop table b_prekes_br_kodas column pbk_id instead. >> >> -- >> Julius Tuskenis >> Programavimo skyriaus vadovas >> UAB nSoft >> mob. +37068233050 >> >> >> > > > > -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |
|
07-05-2008, 02:17 AM
| ||||
| ||||
| Re: changing sequence in serial field Julius Tuskenis <julius@nsoft.lt> writes: > b_prekes_br_kodas_pbk_id_seq1 was created automatically creating serial field and is in some way connected to it. Thats why I get error saying "...column pbk_id requires it." Any way I can't find a way to find and alter this dependency. It's a row in pg_depend. In 8.2 and up you can create/delete the row with ALTER SEQUENCE OWNED BY, but in earlier releases you'd have to do it manually. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |
Linear Mode
