Thread: Using sequences in SQL text files

Using sequences in SQL text files

From

HHB

Date:

19 February 2008, 14:44:55

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.
--
View this message in context: http://www.nabble.com/Using-sequences-in-SQL-text-files-tp15561422p15561422.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Using sequences in SQL text files

From

Raymond O'Donnell

Date:

19 February 2008, 14:53:20

On 19/02/2008 15:43, HHB wrote:

> How to use a sequence in such text files?

You're looking for the nextval() function - look it up in the docs.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: Using sequences in SQL text files

From

Richard Huxton

Date:

19 February 2008, 14:55:36

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);

If they are of SERIAL type then they'll use their sequence by default:

INSERT INTO my_table (id, a, b) VALUES (DEFAULT, 'abc', 123);

Otherwise you can use the currval/nextval() functions:

INSERT INTO my_table (id, a, b) VALUES (nextval(<SEQUENCE-NAME>), 'abc',
123);

--
   Richard Huxton
   Archonet Ltd

Re: Using sequences in SQL text files

From

Lone Wolf

Date:

19 February 2008, 16:40:40

Can I have something like this in my SQL text file:
(items_seq.nextval(), '....', '...')
?

Raymond O'Donnell <rod@iol.ie> wrote:

On 19/02/2008 15:43, HHB wrote:

> How to use a sequence in such text files?

You're looking for the nextval() function - look it up in the docs.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Deep into that darkness peering, long I stood there, wondering, fearing, Doubting, dreaming dreams no mortal ever dreamed before.

E.A Poe

Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

Re: Using sequences in SQL text files

From

brian

Date:

19 February 2008, 17:03:24

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;

Re: Using sequences in SQL text files

From

HHB

Date:

20 February 2008, 07:22:09

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.

Re: Using sequences in SQL text files

From

Thomas

Date:

20 February 2008, 07:50:09



On 20 févr. 08, at 08:57, HHB wrote:
>
> 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.
>

With this, all books will be created in only 1 category : the last
created, and i think this is not what you want.

If you really want to separate books insertion from categories
insertion, your INSERT statements for books must be smarter and look
like :

INSERT INTO books (category_id, ....)
SELECT category_id, ....
FROM categories WHERE category_name = '';

ex:
INSERT INTO books (category_id, name)
SELECT category_id, 'Lord of the rings'
FROM categories WHERE category_name = 'Fantasy';

(assuming you have a 'Fantasy' category)

Hope this helps.

Tom

Re: Using sequences in SQL text files

From

intelforum@subtropolix.org

Date:

20 February 2008, 18:53:50

HHB wrote:
> 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.
>
>

No, because you'll be retrieving the same (last) value for
categories_id_seq every time. If your library is limited to a single
category, you might be good to go, but anyway ...

If you're going to be using INSERT, rather than COPY, you might as well
follow each "INSERT INTO category ..." with the books for that
category. You say you want to keep things separated a bit (and I can
relate to that--haha) but you could take that a step further, reading
the categories line by line and running the appropriate books file for
each. Or something like that.

Or you could use Ant to create a bunch of COPY blocks after each INSERT
statement. (I guess--I'm sure it can do that)

Or you can use the following mechanism to store the category IDs as
session variables.

CREATE FUNCTION get_id(name text) RETURNS integer
AS $_X$
     return $_SHARED{$_[0]};
$_X$
LANGUAGE plperl IMMUTABLE;

CREATE FUNCTION set_id(name text, val integer) RETURNS text
AS $_X$

if ($_SHARED{$_[0]} = $_[1])
{
     return 'ok';
}
else
{
     return "can't set shared variable $_[0] to $_[1]";
}

$_X$
LANGUAGE plperl;

Usage:

INSERT INTO categories ...
SELECT set_id('fiction', CAST(currval('categories_id_seq') AS INT));
INSERT INTO categories ...
SELECT set_id('science', CAST(currval('categories_id_seq') AS INT));
INSERT INTO categories ...
SELECT set_id('politics', CAST(currval('categories_id_seq') AS INT));

...
INSERT INTO books (category_id, ....) VALUES (CAST(get_id('fiction') AS
INT4), ...
INSERT INTO books (category_id, ....) VALUES (CAST(get_id('politics') AS
INT4), ...
INSERT INTO books (category_id, ....) VALUES (CAST(get_id('fiction') AS
INT4), ...

Personally, I'd work out some way to do each INSERT into categories
followed by a COPY block with all the books for that category, simply
because it will be quicker and is much neater.

b