Re: Schemas and serials

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:
>
> On Sat, Jul 29, 2017 at 3:38 PM, tel medola <tel(dot)medola(at)gmail(dot)com
> <mailto:tel(dot)medola(at)gmail(dot)com>> wrote:
>
> Depends.
> When you create your tables in new schema, the script was the same
> from "qa"?
> Sequences, tables, etc.. belong to the schema where was created.
>
> Roberto.
>
> Em sáb, 29 de jul de 2017 às 16:17, marcelo
> <marcelo(dot)nicolet(at)gmail(dot)com <mailto:marcelo(dot)nicolet(at)gmail(dot)com>>
> escreveu:
>
> Some days ago I asked regarding tables located in different
> schemas.
> Now, my question is
> Suppose I have two schemas (other than public): "qa" and
> "production".
> Initially I create all my tables in "qa". All of them have a
> primary key
> of type serial.
> Later, I will copy the tables definitions to production.
> It will automatically create the sequences in the new schema,
> starting
> at zero?
> TIA
> Marcelo
>
>
> --
> Sent via pgsql-general mailing list
> (pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> <http://www.postgresql.org/mailpref/pgsql-general>
>
>
> *Marcelo,
> >Initially I create all my tables in "qa". All of them have a primary
> key of type serial.
> >Later, I will copy the tables definitions to production.
>
> *
> *A word of caution, creating tables in a qa "schema" and then
> transferring to production is not the normal/correct (or safe) way to
> do development.
> *
> *The standard procedure is to create a seperate "qa" database (and/or
> server) with the exact same schema(s) as production. Then, after testing
> *
> *is completed, the schemas/tables are copied to production.
> *
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.