Remove Modifiers on Table

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!

Don't forget to use setval to set the current value of the sequence to the highest number used in the data already, so that the next insertion uses a new, unused value.

Susan Cassidy

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

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, so that 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.