Обсуждение: Remove Modifiers on Table
I created a modifier for auto incrementing my primary key as follows: records=# \d users Table "public.users" Column | Type | Modifiers --------+-----------------------+---------------------------------------------------- id | integer | not null default nextval('users_seq_id'::regclass) fname | character varying(40) | not null lname | character varying(40) | not null email | character varying(40) | not null office | character varying(5) | not null dob | date | not null title | character varying(40) | not null Indexes: "users_pkey" PRIMARY KEY, btree (id) "users_email_key" UNIQUE, btree (email) I recently tried to remove the modifier and it failed because it was associated with the 'id' column so my question is how do I remove / delete the modifier so I can delete the sequence I created to auto increment my 'id' value? I don't want to drop the id column / loss my column data, I just want to remove the associated modifier so I can drop the sequence. Thanks for any assistance.
Carlos Mennens wrote: > I created a modifier for auto incrementing my primary key as follows: > > records=# \d users > Table "public.users" > Column | Type | Modifiers > --------+-----------------------+---------------------------------------------------- > id | integer | not null default > nextval('users_seq_id'::regclass) > fname | character varying(40) | not null > lname | character varying(40) | not null > email | character varying(40) | not null > office | character varying(5) | not null > dob | date | not null > title | character varying(40) | not null > Indexes: > "users_pkey" PRIMARY KEY, btree (id) > "users_email_key" UNIQUE, btree (email) > > I recently tried to remove the modifier and it failed because it was > associated with the 'id' column so my question is how do I remove / > delete the modifier so I can delete the sequence I created to auto > increment my 'id' value? I don't want to drop the id column / loss my > column data, I just want to remove the associated modifier so I can > drop the sequence. If you are truly intent on removing the sequence you'll need to do the following: alter sequence users_seq_id owned by NONE alter table users alter column id drop default drop sequence users_seq_id HTH Bosco.
On Mon, May 16, 2011 at 4:58 PM, Bosco Rama <postgres@boscorama.com> wrote: > If you are truly intent on removing the sequence you'll need to do the > following: > > alter sequence users_seq_id owned by NONE > alter table users alter column id drop default > drop sequence users_seq_id Yes that worked perfect! I'm just curious if I have 20 tables and then want all the 'id' columns to be auto incrementing , that means I have to have 20 listed sequences for all 20 unique tables? Seems very cluttered and messy for PostgreSQL. Can one sequence be attributed to multiple columns in multiple tables? I'm used to MySQL where this was as easy as running: CREATE TABLE test ( id INT PRIMARY KEY AUTO INCREMENT); I guess this is not the case in PostgreSQL, right? Thank you!
On Tue, May 17, 2011 at 10:14 AM, Carlos Mennens <carlos.mennens@gmail.com> wrote: > > Yes that worked perfect! I'm just curious if I have 20 tables and then > want all the 'id' columns to be auto incrementing , that means I have > to have 20 listed sequences for all 20 unique tables? yes > Seems very > cluttered and messy for PostgreSQL. Can one sequence be attributed to > multiple columns in multiple tables? you can use only one sequence for all yes... but then you will have id=1 in one table, id=2 in another, etc... i mean, it will generate one single list of values for all tables > I'm used to MySQL where this was > as easy as running: > > CREATE TABLE test ( > id INT PRIMARY KEY AUTO INCREMENT); > in postgres is as easy as CREATE TABLE test( id SERIAL PRIMARY KEY); hey! it's even less keystrokes! -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL
On Tue, May 17, 2011 at 11:22 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > in postgres is as easy as > > CREATE TABLE test( > id SERIAL PRIMARY KEY); > > hey! it's even less keystrokes! I don't understand how this command above is associated with being able to auto increment the 'id' column. Sorry I'm still learning a lot...
On 17/05/2011 16:26, Carlos Mennens wrote: > On Tue, May 17, 2011 at 11:22 AM, Jaime Casanova<jaime@2ndquadrant.com> wrote: >> in postgres is as easy as >> >> CREATE TABLE test( >> id SERIAL PRIMARY KEY); >> >> hey! it's even less keystrokes! > > I don't understand how this command above is associated with being > able to auto increment the 'id' column. Sorry I'm still learning a > lot... Well, the SERIAL pseudo-type creates the sequence, associates it with the column, and sets a DEFAULT on the column which executes the nextval() function on the sequence - all in one fell swoop. Read all about it here: http://www.postgresql.org/docs/9.0/static/datatype-numeric.html#DATATYPE-SERIAL Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Tue, May 17, 2011 at 12:12 PM, Raymond O'Donnell <rod@iol.ie> wrote: > Well, the SERIAL pseudo-type creates the sequence, associates it with the > column, and sets a DEFAULT on the column which executes the nextval() > function on the sequence - all in one fell swoop. Read all about it here: > > http://www.postgresql.org/docs/9.0/static/datatype-numeric.html#DATATYPE-SERIAL Wow I had no idea. So I do NOT need to manually create a sequence with: CREATE SEQUENCE blah_id_seq; And instead I can just use the SERIAL data type, unless I understood that wrong. I'm going to read up on the URL you provided. Thank you so much!
On 17/05/2011 17:35, Carlos Mennens wrote: > On Tue, May 17, 2011 at 12:12 PM, Raymond O'Donnell<rod@iol.ie> wrote: >> Well, the SERIAL pseudo-type creates the sequence, associates it with the >> column, and sets a DEFAULT on the column which executes the nextval() >> function on the sequence - all in one fell swoop. Read all about it here: >> >> http://www.postgresql.org/docs/9.0/static/datatype-numeric.html#DATATYPE-SERIAL > > Wow I had no idea. So I do NOT need to manually create a sequence with: > > CREATE SEQUENCE blah_id_seq; > > And instead I can just use the SERIAL data type, unless I understood > that wrong. I'm going to read up on the URL you provided. Yes, that's exactly right - SERIAL does it all for you. The mistake some people make, on the other hand, is thinking that SERIAL is a type in its own right - it's not, it just does all those steps automatically. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell <rod@iol.ie> wrote: > Yes, that's exactly right - SERIAL does it all for you. The mistake some > people make, on the other hand, is thinking that SERIAL is a type in its own > right - it's not, it just does all those steps automatically. This information you have shed upon me makes my PG life so much easier! It's amazing what you can do with information once you know it exist :p
On Tue, May 17, 2011 at 12:57 PM, Carlos Mennens <carlos.mennens@gmail.com> wrote: > On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell <rod@iol.ie> wrote: >> Yes, that's exactly right - SERIAL does it all for you. The mistake some >> people make, on the other hand, is thinking that SERIAL is a type in its own >> right - it's not, it just does all those steps automatically. So if I have an existing column in my table with a INT data type, I can't seem to understand how to convert this on my 8.4 production server: ALTER TABLE users ALTER COLUMN id TYPE SERIAL; ERROR: type "serial" does not exist I verified from the docs that 8.4 does support SERIAL but how I convert this data type, I can't seem to figure out. Below is my table definition: orlando=# \d users Table "public.users" Column | Type | Modifiers --------+-----------------------+----------- id | integer | not null fname | character varying(40) | not null lname | character varying(40) | not null email | character varying(40) | not null office | character varying(5) | not null dob | date | not null title | character varying(40) | not null Indexes: "users_pkey" PRIMARY KEY, btree (id) "users_email_key" UNIQUE, btree (email)
On 17/05/2011 19:07, Carlos Mennens wrote: > On Tue, May 17, 2011 at 12:57 PM, Carlos Mennens > <carlos.mennens@gmail.com> wrote: >> On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell<rod@iol.ie> wrote: >>> Yes, that's exactly right - SERIAL does it all for you. The mistake some >>> people make, on the other hand, is thinking that SERIAL is a type in its own >>> right - it's not, it just does all those steps automatically. > > So if I have an existing column in my table with a INT data type, I > can't seem to understand how to convert this on my 8.4 production > server: > > ALTER TABLE users ALTER COLUMN id TYPE SERIAL; > ERROR: type "serial" does not exist That's because of what I just mentioned above. :-) It's not a type: it's just a shortcut. What you need to do instead is something like this: -- Create the sequence. create sequence users_id_seq; -- Tell the column to pull default values from the sequence. alter table users alter column id set default nextval('users_id_seq'); -- Establish a dependency between the column and the sequence. alter sequence users_id_seq owned by users.id; HTH Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell <rod@iol.ie> wrote: > That's because of what I just mentioned above. :-) It's not a type: it's > just a shortcut. What you need to do instead is something like this: > > -- Create the sequence. > create sequence users_id_seq; > > -- Tell the column to pull default values from the sequence. > alter table users alter column id set default nextval('users_id_seq'); > > -- Establish a dependency between the column and the sequence. > alter sequence users_id_seq owned by users.id; Yup - that explains that the shortcut doesn't work for existing tables but only during CREATE TABLE. Otherwise I will need to manually CREATE SEQUENCE...blah blah blah. Thank you!
On 05/17/2011 11:29 AM, Carlos Mennens wrote: > On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell<rod@iol.ie> wrote: >> That's because of what I just mentioned above. :-) It's not a type: it's >> just a shortcut. What you need to do instead is something like this: >> >> -- Create the sequence. >> create sequence users_id_seq; >> >> -- Tell the column to pull default values from the sequence. >> alter table users alter column id set default nextval('users_id_seq'); >> >> -- Establish a dependency between the column and the sequence. >> alter sequence users_id_seq owned by users.id; > > Yup - that explains that the shortcut doesn't work for existing tables > but only during CREATE TABLE. Otherwise I will need to manually CREATE > SEQUENCE...blah blah blah. It will work for an existing table if you are adding a column with 'type' SERIAL. You just cannot change an existing column to 'type' SERIAL. > > Thank you! > -- Adrian Klaver adrian.klaver@gmail.com
Don't forget to use setval to set the current value of the sequence to the highest number used in the data already, so thatthe next insertion uses a new, unused value. Susan Cassidy
On Tue, May 17, 2011 at 2:32 PM, Susan Cassidy <scassidy@edgewave.com> wrote: > Don't forget to use setval to set the current value of the sequence to the highest number used in the data already, sothat the next insertion uses a new, unused value. Doesn't the SERIAL shortcut automatically do this on the fly? How would I set this? ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('foo_seq_id'); ? On Tue, May 17, 2011 at 2:33 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > It will work for an existing table if you are adding a column with 'type' > SERIAL. You just cannot change an existing column to 'type' SERIAL. Yup, That's what I meant to say in a more clear and function statement ;)
> Doesn't the SERIAL shortcut automatically do this on the fly? How > would I set this? > > ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('foo_seq_id'); If you have existing data, say with values 1, 2, 3, etc. and you set the column to start using a sequence nextval as default,unless the sequence has been told what value to start with, it will start at 1. Per the documentation: SELECT setval('users_id_seq', 42); -- Next nextval (insert) will return 43 Susan Cassidy
Carlos Mennens wrote: > On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell <rod@iol.ie> wrote: >> That's because of what I just mentioned above. :-) It's not a type: it's >> just a shortcut. What you need to do instead is something like this: >> >> -- Create the sequence. >> create sequence users_id_seq; >> >> -- Tell the column to pull default values from the sequence. >> alter table users alter column id set default nextval('users_id_seq'); >> >> -- Establish a dependency between the column and the sequence. >> alter sequence users_id_seq owned by users.id; > > Yup - that explains that the shortcut doesn't work for existing tables > but only during CREATE TABLE. Otherwise I will need to manually CREATE > SEQUENCE...blah blah blah. Yeah. We went through this one too many times and finally came up with this function to handle it all for us. It's crude but it works for us. create or replace function make_serial(text, text) returns void as $$ declare tbl text; col text; seq text; seq_l text; begin seq := quote_ident($1||'_'||$2||'_seq'); seq_l := quote_literal($1||'_'||$2||'_seq'); tbl := quote_ident($1); col := quote_ident($2); raise notice 'seq = %, tbl = %, col = %', seq, tbl, col; execute 'create sequence '||seq; execute 'alter table '||tbl||' alter column '||col||' set default nextval('||seq_l||')'; execute 'alter sequence '||seq||' owned by '||tbl||'.'||col; execute 'select setval('||seq_l||', (select max('||col||') from '||tbl||'))'; end; $$ language plpgsql; Then you call it thusly: select make_serial('users', 'id'); HTH Bosco.