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/s...-sequence.html

SEan

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thanks Sean. I'm trying the following after having done the import.

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: ^


setval seems to work okay outside plpgsql but I need it to do the select
into.

Any help is greatly appreciated.

Thanks,
Hari

On 5/8/06, Sean Davis <sdavis2@mail.nih.gov> wrote:
>
> 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/s...-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




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend