Bug in my ( newbie ) mind?
Greetings,
Please, what am I doing wrong?
chris=# \d phone_prefix
Table "phone_prefix"
-[ RECORD 1 ]----------------------------------------------------------
Attribute | number
Type | integer
Modifier | not null default nextval('"phone_prefix_number_seq"'::text)
-[ RECORD 2 ]----------------------------------------------------------
Attribute | prefix
Type | text
Modifier |
Index: phone_prefix_number_key chris=# select phone_prefix.prefix order by random() limit 1;
prefix
--------
384
(1 row)
Wonderful, works exactly as expected.
chris=# select lpad((random()*10000)::int, 4, '0')::text as "Number";
Number
--------
2958
(1 row)
ditto
But attempting to concatenate the two is a disaster.
chris=# select phone_prefix.prefix order by random() limit 1 || '-' ||
lpad((random()*10000)::int, 4, '0')::text as "Phone Number";
ERROR: parser: parse error at or near "||"
chris=#
What am i doing wrong?
chris=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.1beta3 on i586-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
Thanks a 10^6
p.s. imho it would be a terrific help, especially for new-comers to SQL
like me, if the parser could be persuaded to utter just a tiny glimmer of
a hint as to what it thinks one's mistake is instead of the rather
enigmatic "ERROR: parser: parse error at or near".
Is it possible for mere mortals to help?
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN 45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me
<<--
On Wed, 21 Feb 2001, Christopher Sawtell wrote:
chris=# select phone_prefix.prefix order by random() limit 1 || '-' ||
lpad((random()*10000)::int, 4, '0')::text as "Phone Number";
All the things you are selecting need to come in the first part of the
query.
like,
SELECT prefix || '-' || lpad((random()*10000)::int, 4, '0')::text as
"Phone Number" from phone_prefix order by random() limit 1;
--
Tod McQuillin
Christopher Sawtell writes:
chris=# select phone_prefix.prefix order by random() limit 1 || '-' ||
lpad((random()*10000)::int, 4, '0')::text as "Phone Number";
ERROR: parser: parse error at or near "||"
This sure won't fix everything, but at the very least you need to
parenthesize that first select clause inside another select. For
instance, this works:
select (select '123'::text) || (select '456'::text);
So one might think that, with appropriate casting, something more
like:
select (select phone_prefix.prefix order by random() limit 1) || ...
would be more likely to work (modulo some casting and such).
Dan
Dan Lyke <danlyke@flutterby.com> writes:
So one might think that, with appropriate casting, something more
like:
select (select phone_prefix.prefix order by random() limit 1) || ...
would be more likely to work (modulo some casting and such).
Note this will not work in pre-7.1 releases --- 7.1 is the first that
allows ORDER BY and LIMIT clauses in a sub-select.
regards, tom lane
Tom Lane writes:
Note this will not work in pre-7.1 releases --- 7.1 is the first
that allows ORDER BY and LIMIT clauses in a sub-select.
Yah, the way I figure it is that if you're a PostgreSQL user, the very
least you can do for the community is be running the latest beta on
your development machines so that you can help find the bugs.
Open source software doesn't really cost any less than commercial
software, it just costs in different less tangible ways. And it's
those that make the quality higher.
Dan