Re: [GENERAL] 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 Sat, Jul 29, 2017 at 3:38 PM, tel medola <tel.medola@gmail.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.nicolet@gmail.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@postgresql.org)
To make changes to your subscription:
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.