Re: Using sequences in SQL text files - Mailing list pgsql-general

Thank you all for your help.
Let me summer what I'm trying to do:
I have an empty database that I want to populate it with data.
I created SQL text files, categories.sql, books.sql ....
As I'm a Java guy, I use Apache Ant SQL taks to run those SQL text files.
So, I run categories.sql file first, then books.sql
It seems to me that Brian's solution supposed that I have all SQL insert
statements are in one file, this isn't my case (actually, I have many SQL
files that
I want to run, merging them in one SQL isn't practical).
So, is it possible to do so:
----
categories.sql
don't insert an id here
INSERT INTO categories ('..', '..' ...) VALUES ('..', '..', ...);
INSERT INTO categories ('..', '..' ...) VALUES ('..', '..', ...);
----
books.sql
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), '..', ...);
----
Thank you all for your time.



brian ally wrote:
>
> 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;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
>

--
View this message in context: http://www.nabble.com/Using-sequences-in-SQL-text-files-tp15561422p15584090.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.