Re: Using sequences in SQL text files

HHB wrote:
> Hi.
> I have sequence for each table in my database.
> In order to populate same data in the database, I created some SQL text
> files.
> ---
> insert into categories values (id value from sequence, '..', '...');
> insert into books values (id value from sequence, '..', '...', '..', fk to
> category id);
> ---
> Is it possible to do so?
> How to use a sequence in such text files?
> Thanks.

I think it depends. If this is a new database then you can leave off the
SERIAL id values and let the sequence do its thing. To insert the
foreign key into books you can use currval() like so:

-- don't insert an id here
INSERT INTO categories ('..', '..' ...) VALUES ('..', '..', ...);

--
INSERT INTO books (category_id, '..', ...) VALUES
(CAST(currval('categories_id_seq') AS INT), '..', ...);
INSERT INTO books (category_id, '..', ...) VALUES
(CAST(currval('categories_id_seq') AS INT), '..', ...);

INSERT INTO categories ('..', '..' ...) VALUES ('..', '..', ...);
INSERT INTO books (category_id, '..', ...) VALUES
(CAST(currval('categories_id_seq') AS INT), '..', ...);
...

If the data is from a dump (and so the sequence IDs--and foreign key
relations--already exist) you'll need to use setval() afterwards to
reset where the sequences should begin from afterwards.

After all of your inserts (this time with the existing IDs):

SELECT setval('books_id_seq', max(id)) FROM books;
SELECT setval('categories_id_seq', max(id)) FROM categories;