Re: Schemas and serials

marcelo <marcelo.nicolet@xxxxxxxxx> · Sun, 30 Jul 2017 13:36:11 -0300



    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.medola@xxxxxxxxx>
            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@xxxxxxxxx>
                        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@xxxxxxxxxxxxxx)

                        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.