Re: [SQL] Subselects not allowed?
* Leif Biberg Kristensen:
And even better, in the first comment to the blog post, I was advised about
the
SETVAL() function which does exactly what I wanted in the first place.
CREATE SEQUENCE persons_person_id_seq;
SELECT SETVAL('persons_person_id_seq', MAX(person_id)) FROM persons;
ALTER TABLE persons ALTER COLUMN person_id SET DEFAULT
NEXTVAL('persons_person_id_seq');
ALTER SEQUENCE persons_person_id_seq OWNED BY persons.person_id;
I think you should acquire an exclusive lock on the table, too.
--
Florian Weimerfwei...@bfk.de
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subselects not allowed?
On Saturday 11. June 2011 22.09.09 Leif Biberg Kristensen wrote:
I've written a blog post which I hope may be helpful to others in a similar
situation:
http://solumslekt.org/blog/?p=321
Thanks to Guillaume Lelarge, Tom Lane, and Andreas Kretschmer. You guys are
great.
And even better, in the first comment to the blog post, I was advised about the
SETVAL() function which does exactly what I wanted in the first place.
CREATE SEQUENCE persons_person_id_seq;
SELECT SETVAL('persons_person_id_seq', MAX(person_id)) FROM persons;
ALTER TABLE persons ALTER COLUMN person_id SET DEFAULT
NEXTVAL('persons_person_id_seq');
ALTER SEQUENCE persons_person_id_seq OWNED BY persons.person_id;
regards, Leif
http://code.google.com/p/yggdrasil-genealogy/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subselects not allowed?
Leif Biberg Kristensen l...@solumslekt.org wrote: On Saturday 11. June 2011 22.09.09 Leif Biberg Kristensen wrote: I've written a blog post which I hope may be helpful to others in a similar situation: http://solumslekt.org/blog/?p=321 Thanks to Guillaume Lelarge, Tom Lane, and Andreas Kretschmer. You guys are great. And even better, in the first comment to the blog post, I was advised about the SETVAL() function which does exactly what I wanted in the first place. Yeah, right! Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subselects not allowed?
On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote: Can anybody tell me why this doesn't work? Because it's not supported. The START clause expects a value, not a subquery. pgslekt= CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX(source_id) FROM sources); ERROR: syntax error at or near ( LINE 1: CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX... ^ pgslekt= regards, Leif -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subselects not allowed?
Guillaume Lelarge guilla...@lelarge.info writes: On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote: Can anybody tell me why this doesn't work? pgslekt= CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX(source_id) FROM sources); ERROR: syntax error at or near ( Because it's not supported. The START clause expects a value, not a subquery. More generally, there are no utility statements in PG that accept non-constant expressions in their parameters. (A utility statement is anything other than SELECT, INSERT, UPDATE, DELETE.) There's been occasional speculation about changing that, but it would take a significant amount of work I think. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subselects not allowed?
Leif Biberg Kristensen l...@solumslekt.org wrote: Can anybody tell me why this doesn't work? pgslekt= CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX(source_id) FROM sources); ERROR: syntax error at or near ( LINE 1: CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX... ^ pgslekt= regards, Leif You can't do that, but you can do this: test=# select * from foo; i 98 99 (2 rows) Time: 0,146 ms test=*# \ds seq_foo; No matching relations found. test=*# do $$ declare m int; begin select into m max(i) from foo; execute 'create sequence seq_foo start with ' || m; end; $$; DO Time: 1,115 ms test=*# \ds seq_foo; List of relations Schema | Name | Type | Owner +-+--+ public | seq_foo | sequence | kretschmer (1 row) test=*# select * from seq_foo; sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---++-+--+-+---+-+-+---+--- seq_foo | 99 | 99 |1 | 9223372036854775807 | 1 | 1 | 1 | f | f (1 row) I'm using 9.1Beta, but it works since 9.0, see: http://www.depesz.com/index.php/2009/11/01/waiting-for-8-5-do/#more-1535 Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subselects not allowed?
On Saturday 11. June 2011 17.14.21 Tom Lane wrote: Guillaume Lelarge guilla...@lelarge.info writes: On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote: Can anybody tell me why this doesn't work? pgslekt= CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX(source_id) FROM sources); ERROR: syntax error at or near ( Because it's not supported. The START clause expects a value, not a subquery. More generally, there are no utility statements in PG that accept non-constant expressions in their parameters. (A utility statement is anything other than SELECT, INSERT, UPDATE, DELETE.) There's been occasional speculation about changing that, but it would take a significant amount of work I think. Thanks for the explanation, Tom. Presumably this means that I can't automatically patch a live database to use a sequence unless I build a special function for the update. With 9.x I could use the new DO syntax, but that isn't yet deployed everywhere. Unless there's a way around it? regards, Leif -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subselects not allowed?
On Saturday 11. June 2011 17.23.40 Andreas Kretschmer wrote: Leif Biberg Kristensen l...@solumslekt.org wrote: Can anybody tell me why this doesn't work? pgslekt= CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX(source_id) FROM sources); ERROR: syntax error at or near ( LINE 1: CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX... ^ pgslekt= regards, Leif You can't do that, but you can do this: test=# select * from foo; i 98 99 (2 rows) Time: 0,146 ms test=*# \ds seq_foo; No matching relations found. test=*# do $$ declare m int; begin select into m max(i) from foo; execute 'create sequence seq_foo start with ' || m; end; $$; DO Time: 1,115 ms test=*# \ds seq_foo; List of relations Schema | Name | Type | Owner +-+--+ public | seq_foo | sequence | kretschmer (1 row) test=*# select * from seq_foo; sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---++-+--+ -+---+-+-+---+--- seq_foo | 99 | 99 |1 | 9223372036854775807 | 1 | 1 | 1 | f | f (1 row) I'm using 9.1Beta, but it works since 9.0, see: http://www.depesz.com/index.php/2009/11/01/waiting-for-8-5-do/#more-1535 Yes it works like a charm with 9.x, but it's not backwards compatible. That looks like a problem waiting to happen. But of course I can create a one- shot function and drop it afterwards. regards, Leif -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subselects not allowed?
I've written a blog post which I hope may be helpful to others in a similar situation: http://solumslekt.org/blog/?p=321 Thanks to Guillaume Lelarge, Tom Lane, and Andreas Kretschmer. You guys are great. regards, Leif http://code.google.com/p/yggdrasil-genealogy/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
