Обсуждение: Subselects not allowed?
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
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
Guillaume Lelarge <guillaume@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
Leif Biberg Kristensen <leif@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
----9899
(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 relationsSchema | 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°
On Saturday 11. June 2011 17.14.21 Tom Lane wrote:
> Guillaume Lelarge <guillaume@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
On Saturday 11. June 2011 17.23.40 Andreas Kretschmer wrote:
> Leif Biberg Kristensen <leif@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
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/
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/
Leif Biberg Kristensen <leif@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°
* 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 Weimer <fweimer@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