Обсуждение: Setting sequence ids after importing data

Hi:

I am importing data from sqlite db into postgres. The tables have an id column that is obtained from a sequence.

To preserve the foreign key relationships, I need to preserve the ids while importing into postgres.

The problem is that after import, the sequences don't get incremented which leads to unique constraint violation errors further down.
How do I fix this ?

Thanks,
Hari

Hari Patel wrote:
> Hi:
>
> I am importing data from sqlite db into postgres. The tables have an id
> column that is obtained from a sequence.
>
> To preserve the foreign key relationships, I need to preserve the ids
> while importing into postgres.
>
> The problem is that after import, the sequences don't get incremented
> which leads to unique constraint violation errors further down.
> How do I fix this ?

See here in the documentation:

http://www.postgresql.org/docs/8.1/static/functions-sequence.html

SEan

On May 9, 2006, at 10:59 , Hari Patel wrote:

> CREATE function set_sequence_ids() RETURNS INTEGER AS '
> DECLARE
>   arg INTEGER;
> BEGIN
>   select into arg max(id) from foo;
>   select setval('foo_id_seq', 10);
>   return arg;
> END;
> ' LANGUAGE 'plpgsql';
>
> I get the following error in psql.
>
> psql:func.sql:9: ERROR:  syntax error at or near "foo_id_seq" at
> charact
> er 152
> psql:func.sql:9: LINE 6:   select setval('foo_id_seq', 10);
> psql:func.sql:9:                          ^

Try just
setval('foo_id_seq',10)
or
PEFORM setval('foo_id_seq', 10)

It's just a function call, and since you're not saving the return
value, you don't use SELECT. SELECT has different syntax in PL/pgSQL
than in SQL.

Hope this helps.

Michael Glaesemann
grzm seespotcode net

"Hari Patel" <hariwise@gmail.com> writes:
> CREATE function set_sequence_ids() RETURNS INTEGER AS '
> DECLARE
>   arg INTEGER;
> BEGIN
>   select into arg max(id) from foo;
>   select setval('foo_id_seq', 10);
>   return arg;
> END;
> ' LANGUAGE 'plpgsql';

> I get the following error in psql.

> psql:func.sql:9: ERROR:  syntax error at or near "foo_id_seq" at charact
> er 152
> psql:func.sql:9: LINE 6:   select setval('foo_id_seq', 10);
> psql:func.sql:9:                          ^

You've got a quoting problem: either double the quotes embedded in the
function body, or use dollar quotes ($$) to surround the function body
instead of plain quotes (').

BTW, seems like the above is wrong anyway: don't you want to set the
sequence to arg+1, rather than constant 10?

            regards, tom lane