Thread: Subselects not allowed?

Subselects not allowed?

From

Leif Biberg Kristensen

Date:

11 June 2011, 15:02:05

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

Re: Subselects not allowed?

From

Guillaume Lelarge

Date:

11 June 2011, 15:08:21

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

Re: Subselects not allowed?

From

Tom Lane

Date:

11 June 2011, 15:14:33

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

Re: Subselects not allowed?

From

Andreas Kretschmer

Date:

11 June 2011, 15:23:51

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°

Re: Subselects not allowed?

From

Leif Biberg Kristensen

Date:

11 June 2011, 15:24:02

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

Re: Subselects not allowed?

From

Leif Biberg Kristensen

Date:

11 June 2011, 15:39:16

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

Re: Subselects not allowed?

From

Leif Biberg Kristensen

Date:

11 June 2011, 20:09:21

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/

Re: Subselects not allowed?

From

Leif Biberg Kristensen

Date:

12 June 2011, 08:22:39

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/

Re: Subselects not allowed?

From

Andreas Kretschmer

Date:

12 June 2011, 08:37:31

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°

Re: Subselects not allowed?

From

Florian Weimer

Date:

14 June 2011, 14:06:29

* 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