Melvin:
My example was somewhat inexact. The full question is as follows:
I need to have two groups of tables: the "reference" ones (examples: city, country, customer) which will "reside" in the public schema, and the transaccional ones, which will reside in a schema representing one year/season. These table's definitions must be copied to a new schema at the start of new year/season.
One of these tables create script could be as follows
CREATE TABLE dailyprogram
(
id serial NOT NULL,
date timestamp without time zone NOT NULL,
packerid integer NOT NULL,
CONSTRAINT "PK_dailyprogram" PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE dailyprogram
OWNER TO postgres;
My reworded question is: if I run this sql in the new schema, the implicit '
CREATE SEQUENCE dailyprogram_id_seq;' statement will be executed in the new schema, so the sequence will be reset to zero?
TIA
PS: Of course, I considered the other option: to have a table representing the seasons, and every main transactional table with a foreign key to this season table, but it add a level of indirection to a database which is now very convoluted.
On 29/07/17 17:17, Melvin Davidson wrote: