pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSubselects not allowed?
10+ messages / 5 participants
[nested] [flat]
* Subselects not allowed?
@ 2011-06-11 15:01 Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-11 15:08 ` Re: Subselects not allowed? Guillaume Lelarge <guillaume@lelarge.info>
2011-06-11 15:23 ` Re: Subselects not allowed? Andreas Kretschmer <akretschmer@spamfence.net>
2011-06-11 20:09 ` Re: Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
0 siblings, 3 replies; 10+ messages in thread
From: Leif Biberg Kristensen @ 2011-06-11 15:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselects not allowed?
2011-06-11 15:01 Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
@ 2011-06-11 15:08 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-06-11 15:14 ` Re: Subselects not allowed? Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 10+ messages in thread
From: Guillaume Lelarge @ 2011-06-11 15:08 UTC (permalink / raw)
To: Leif Biberg Kristensen <leif@solumslekt.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselects not allowed?
2011-06-11 15:01 Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-11 15:08 ` Re: Subselects not allowed? Guillaume Lelarge <guillaume@lelarge.info>
@ 2011-06-11 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-06-11 15:23 ` Re: Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2011-06-11 15:14 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Leif Biberg Kristensen <leif@solumslekt.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselects not allowed?
2011-06-11 15:01 Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-11 15:08 ` Re: Subselects not allowed? Guillaume Lelarge <guillaume@lelarge.info>
2011-06-11 15:14 ` Re: Subselects not allowed? Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-06-11 15:23 ` Leif Biberg Kristensen <leif@solumslekt.org>
0 siblings, 0 replies; 10+ messages in thread
From: Leif Biberg Kristensen @ 2011-06-11 15:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselects not allowed?
2011-06-11 15:01 Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
@ 2011-06-11 15:23 ` Andreas Kretschmer <akretschmer@spamfence.net>
2011-06-11 15:39 ` Re: Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
2 siblings, 1 reply; 10+ messages in thread
From: Andreas Kretschmer @ 2011-06-11 15:23 UTC (permalink / raw)
To: pgsql-sql
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
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°
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselects not allowed?
2011-06-11 15:01 Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-11 15:23 ` Re: Subselects not allowed? Andreas Kretschmer <akretschmer@spamfence.net>
@ 2011-06-11 15:39 ` Leif Biberg Kristensen <leif@solumslekt.org>
0 siblings, 0 replies; 10+ messages in thread
From: Leif Biberg Kristensen @ 2011-06-11 15:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselects not allowed?
2011-06-11 15:01 Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
@ 2011-06-11 20:09 ` Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-12 08:22 ` Re: Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
2 siblings, 1 reply; 10+ messages in thread
From: Leif Biberg Kristensen @ 2011-06-11 20:09 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselects not allowed?
2011-06-11 15:01 Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-11 20:09 ` Re: Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
@ 2011-06-12 08:22 ` Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-12 08:37 ` Re: Subselects not allowed? Andreas Kretschmer <akretschmer@spamfence.net>
2011-06-14 14:00 ` Re: Subselects not allowed? Florian Weimer <fweimer@bfk.de>
0 siblings, 2 replies; 10+ messages in thread
From: Leif Biberg Kristensen @ 2011-06-12 08:22 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselects not allowed?
2011-06-11 15:01 Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-11 20:09 ` Re: Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-12 08:22 ` Re: Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
@ 2011-06-12 08:37 ` Andreas Kretschmer <akretschmer@spamfence.net>
1 sibling, 0 replies; 10+ messages in thread
From: Andreas Kretschmer @ 2011-06-12 08:37 UTC (permalink / raw)
To: pgsql-sql
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°
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Subselects not allowed?
2011-06-11 15:01 Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-11 20:09 ` Re: Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-12 08:22 ` Re: Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
@ 2011-06-14 14:00 ` Florian Weimer <fweimer@bfk.de>
1 sibling, 0 replies; 10+ messages in thread
From: Florian Weimer @ 2011-06-14 14:00 UTC (permalink / raw)
To: Leif Biberg Kristensen <leif@solumslekt.org>; +Cc: pgsql-sql
* 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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2011-06-14 14:00 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-11 15:01 Subselects not allowed? Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-11 15:08 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-06-11 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-06-11 15:23 ` Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-11 15:23 ` Andreas Kretschmer <akretschmer@spamfence.net>
2011-06-11 15:39 ` Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-11 20:09 ` Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-12 08:22 ` Leif Biberg Kristensen <leif@solumslekt.org>
2011-06-12 08:37 ` Andreas Kretschmer <akretschmer@spamfence.net>
2011-06-14 14:00 ` Florian Weimer <fweimer@bfk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox