Re: how to ignore a column from pg_dump

2009/7/20 Abhi_m <talk2abhinav@gmail.com>:
>
> Hi All,
>
> I am using a sequence in a table in my database. There are large nos of
> insertion and deletion operations are happening on the table.
> When the sequence overflows I need to recreate the database.
> While restoring the database I want this sequence column to start  from
> again from 0 and fill all holes created by deletion on db. But all other
> columns should remain unchanged.
> I am backing up my database using pg_dump and recreating it using
> pg_restore.
> Is there any way I can tell pg_dump not to backup values for this sequence
> column and create them afresh while restoring?

Another way to do this would be to create another table that is
identical to the first and then select the data out of the first one
into the second one.  Then drop the first one and rename the second to
the first.

e.g.:

x=> create table one (id serial primary key, name text);
NOTICE:  CREATE TABLE will create implicit sequence "one_id_seq" for
serial column "one.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"one_pkey" for table "one"
CREATE TABLE
x=> insert into one (name) values ('fred');
INSERT 0 1
x=> insert into one (name) values ('bob');
INSERT 0 1
x=> insert into one (name) values ('mary');
INSERT 0 1
x=> insert into one (name) values ('joe');
INSERT 0 1
x=> insert into one (name) values ('sue');
INSERT 0 1
x=> delete from one where id = 3 or id = 4;
DELETE 2
x=> insert into one (name) values ('gill');
INSERT 0 1
x=> select * from one;
 id | name
----+------
  1 | fred
  2 | bob
  5 | sue
  6 | gill
(4 rows)

x=> create table two (id serial primary key, name text);
NOTICE:  CREATE TABLE will create implicit sequence "two_id_seq" for
serial column "two.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"two_pkey" for table "two"
CREATE TABLE
x=> insert into two (name) select name from one;
INSERT 0 4
x=> select * from two;
 id | name
----+------
  1 | fred
  2 | bob
  3 | sue
  4 | gill
(4 rows)

x=> drop table one;
DROP TABLE
x=> alter table two rename to one;
ALTER TABLE
x=> \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 public | one  | table | x
(1 row)

Of course your indexes etc. will now not be named after your table,
but you can rename them too if want to:

x=> \d one
                         Table "public.one"
 Column |  Type   |                    Modifiers
--------+---------+--------------------------------------------------
 id     | integer | not null default nextval('two_id_seq'::regclass)
 name   | text    |
Indexes:
    "two_pkey" PRIMARY KEY, btree (id)

x=> \ds
            List of relations
 Schema |    Name    |   Type   |  Owner
--------+------------+----------+---------
 public | two_id_seq | sequence | x
(1 row)

x=> \di
              List of relations
 Schema |   Name   | Type  |  Owner  | Table
--------+----------+-------+---------+-------
 public | two_pkey | index | x | one
(1 row)

x=>

--
Michael Wood <esiotrot@gmail.com>