Search Postgresql Archives

Re : Resetting SEQUENCEs

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

 



It is not as easy as that :

 1/ select table_name from information_schema.tables does not give you the tables list on the current schema but the list of all objects !
But the following will
SELECT c.relname
FROM pg_namespace AS nc, pg_class AS c
WHERE c.relnamespace = nc.oid
AND  c.relkind IN ('r' )

2/ Having the tables list is not enough (because obviously I know how to do this), then you need to get the list of sequences (actually you want to get first the sequences)  and then the list of colums using the sequence and (at last the tables where the columns are) .
At that point, it is possible to generate the SQL order to reset the SEQUENCE.

Unfortunately I do not know how to produce the list of sequences and columns using them and  columns tables.
 

Have fun,
L@u
The Computing Froggy


----- Message d'origine ----
De : Filip Rembiałkowski <plk.zuber@xxxxxxxxx>
À : Laurent ROCHE <laurent_roche@xxxxxxxxx>
Cc : pgsql-general@xxxxxxxxxxxxxx
Envoyé le : Jeudi, 18 Octobre 2007, 19h33mn 50s
Objet : Re: Resetting SEQUENCEs

2007/10/18, Laurent ROCHE <laurent_roche@xxxxxxxxx>:
>
> Hi,
>
> I am quite surprised I could not find a way to automatically reset the value
> of a sequence for all my tables.
>
> Of course, I can write:
> SELECT setval('serial', max(id)) FROM distributors
> But if I reload data into all my tables, it's a real pain to have to write
> something like this for every single table with a sequence.
>
> I would expect PostgreSQL to provide some command like:
> resynchAllSequences my_schema;


try something like

CREATE FUNCTION execute(in_sql TEXT) RETURNS void as $$
BEGIN
EXECUTE in_sql;
RETURN;
END;
$BODY$ language plpgsql;


select execute(
$$select setval( '$$
|| table_name ||
$$_id_seq', coalesce((select max(id) from $$
|| table_name ||
$$),1), false ) $$
) from information_schema.tables where you want;



--
Filip Rembiałkowski

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster



Stockage illimité de vos mails avec Yahoo! Mail. Changez aujourd'hui de mail !

[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