On 10/22/19 10:48 AM, stan wrote:
Please reply to list also:
Ccing list.
Sorry if my description was not clear.
No, we do not mix test, and production data. Let me try to clarify the
question. Looking at a pg_dump, I see the following:
CREATE SEQUENCE public.customer_key_serial
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
Yet, in the same pg_dump file I have:
COPY public.customer (customer_key, cust_no, name, c_type, location,
bill_address_1, bill_address_2, bill_city, bill_state, bill_zip,
bill_country, bill_attention, bill_addressee, ship_address_1,
ship_address_2, ship_addressee, ship_attention, ship_city, ship_state,
ship_zip, office_phone_area_code, office_phone_exchange,
office_phone_number, office_phone_extension, cell_phone_area_code,
cell_phone_exchange, cell_phone_number, ship_phone_area_code,
ship_phone_exchange, ship_phone_number, ship_phone_extension,
fax_phone_area_code, fax_phone_exchange, fax_phone_number, status, modtime)
FROM stdin;
1 12 Made Up Inc. INDUSTRIAL_CONVEYOR \N 101 Nowhere
Ave. \N LaGrange GA 00000 \N \N \N
\N \N \N \N \N \N 00000 \N \N \N
\N \N \N \N \N \N \N \N \N
\N \N ACTIVE 2019-09-30 23:55:04.594203+00
2 5 Jimmys Favorite Customer. PLASTICS \N
56 Somewhere St. \N No Such City SC 00000 \N
\N \N \N \N \N \N \N \N 00000
\N \N \N \N \N \N \N \N \N
\N \N \N \N \N ACTIVE 2019-09-30
23:55:04.636827+00
So it appears to me the customer table is going to get (correctly) populated
with the originally generated keys, yet the sequence will want to return a 1
the next time it is called, when a new customer gets inserted.
Am I missing something here?
Yes something like this, in dump file, for non-serial sequence:
CREATE SEQUENCE public.plant1_p_item_no_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
SELECT pg_catalog.setval('public.plant1_p_item_no_seq', 5509, true);
or for serial sequence:
CREATE SEQUENCE public.avail_headers_line_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE ONLY public.avail_headers ALTER COLUMN line_id SET DEFAULT
nextval('public.avail_headers_line_id_seq'::regclass);
SELECT pg_catalog.setval('public.avail_headers_line_id_seq', 53, true);
If you want to see how things are run on a higher level do something like:
pg_dump -Fc -d some_db -f db.out
pg_restore -l db_out > db_toc.txt
-l on pg_restore creates a TOC(table of contents) showing the ordering
of the schema recreation.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
