pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbug.. ?
10+ messages / 7 participants
[nested] [flat]
* bug.. ?
@ 2001-02-22 19:55 Jeff MacDonald <jeff@hub.org>
2001-02-24 05:10 ` Re: bug.. ? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Jeff MacDonald @ 2001-02-22 19:55 UTC (permalink / raw)
To: pgsql-sql
A person recent pointed this out to me..
seems a bit funny, because limit 1 pretty much
say's it't not gonna return multiple values.
jeff
> This doesn't work:
>
> CREATE FUNCTION vuln_port(int4) RETURNS int4 AS 'SELECT port FROM
> i_host_vuln WHERE vuln = $1 GROUP BY port ORDER BY count(port) DESC
> LIMIT 1' LANGUAGE 'sql';
>
> The result:
> ERROR: function declared to return int4 returns multiple values in final
> retrieve
>
>
> I'm running 7.0.2.
Jeff MacDonald,
-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Facsimile : 1 902 542 5386
IRC Nick : bignose
PGP Public Key : http://bignose.hub.org/public.txt
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: bug.. ?
2001-02-22 19:55 bug.. ? Jeff MacDonald <jeff@hub.org>
@ 2001-02-24 05:10 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2001-02-24 05:10 UTC (permalink / raw)
To: Jeff MacDonald <jeff@hub.org>; +Cc: pgsql-sql
Jeff MacDonald <jeff@hub.org> writes:
> A person recent pointed this out to me..
> seems a bit funny, because limit 1 pretty much
> say's it't not gonna return multiple values.
>> CREATE FUNCTION vuln_port(int4) RETURNS int4 AS 'SELECT port FROM
>> i_host_vuln WHERE vuln = $1 GROUP BY port ORDER BY count(port) DESC
>> LIMIT 1' LANGUAGE 'sql';
>>
>> The result:
>> ERROR: function declared to return int4 returns multiple values in final
>> retrieve
It's a combination of a bug and a poorly worded error message. It is
complaining not about rows, but about the extra column needed to do the
ORDER BY. The error check forgets to ignore this 'hidden' column.
Fixed some time ago for 7.1...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* bug?
@ 2003-01-02 04:36 Minghann Ho <mho@mcs.vuw.ac.nz>
2003-01-02 06:16 ` Re: bug? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Minghann Ho @ 2003-01-02 04:36 UTC (permalink / raw)
To: pgsql-sql
The following SELECT are errors?!
tpcr=# select extract (week from date '2000-01-01');
date_part
-----------
52
(1 row)
tpcr=# select extract (week from date '2000-01-02');
date_part
-----------
52
(1 row)
Please check ...
thanks
Hans
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: bug?
2003-01-02 04:36 bug? Minghann Ho <mho@mcs.vuw.ac.nz>
@ 2003-01-02 06:16 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2003-01-02 06:16 UTC (permalink / raw)
To: Minghann Ho <mho@mcs.vuw.ac.nz>; +Cc: pgsql-sql
Minghann Ho <mho@mcs.vuw.ac.nz> writes:
> The following SELECT are errors?!
No, they're not. PG follows the ISO definition of week numbers:
Monday is the first day of the week, and the first week of a year
is the first one containing a Thursday.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Bug?
@ 2006-10-18 13:23 Indira Muthuswamy <indumuthu@gmail.com>
2006-10-18 13:31 ` Re: Bug? Andrew Dunstan <andrew@dunslane.net>
2006-10-18 18:43 ` Re: [HACKERS] Bug? Jim C. Nasby <jim@nasby.net>
0 siblings, 2 replies; 10+ messages in thread
From: Indira Muthuswamy @ 2006-10-18 13:23 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
Hai,
I have encountered a problem with PostgreSQL.I have created a table
'tab1' with a column 'a' with serial type.I entered 20 records into the
table.So the query
select max(a) from tab1;
returned 20.When I tried the same query after the command
truncate table tab1;
I found that the output of the first query as
max
-----
(1 row)
When I tried to insert a new row into the table tab1 I found that the
value at column a incremented to the value 21.
But I heard from my friends that the value of the serial column gets
decremented whenever we issue the 'truncate table' command (in MS SQL
SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify me on
this?
Thanks in advance,
Regards,
m.Indira
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Bug?
2006-10-18 13:23 Bug? Indira Muthuswamy <indumuthu@gmail.com>
@ 2006-10-18 13:31 ` Andrew Dunstan <andrew@dunslane.net>
1 sibling, 0 replies; 10+ messages in thread
From: Andrew Dunstan @ 2006-10-18 13:31 UTC (permalink / raw)
To: Indira Muthuswamy <indumuthu@gmail.com>; +Cc: pgsql-hackers@postgresql.org
Indira Muthuswamy wrote:
> Hai,
>
> I have encountered a problem with PostgreSQL.I have created a table
> 'tab1' with a column 'a' with serial type.I entered 20 records into
> the table.So the query
> select max(a) from tab1;
> returned 20.When I tried the same query after the command
> truncate table tab1;
> I found that the output of the first query as
>
> max
> -----
>
> (1 row)
>
> When I tried to insert a new row into the table tab1 I found that the
> value at column a incremented to the value 21.
> But I heard from my friends that the value of the serial column gets
> decremented whenever we issue the 'truncate table' command (in MS SQL
> SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify
> me on this?
>
Sure. You heard wrong about postgres.
Please do not ask questions like this on the -hackers list - this should
have gone to pgsql-general. The hackers list is for postgresql
development discussions only.
cheers
andrew
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] Bug?
2006-10-18 13:23 Bug? Indira Muthuswamy <indumuthu@gmail.com>
@ 2006-10-18 18:43 ` Jim C. Nasby <jim@nasby.net>
2006-10-19 06:58 ` Re: [HACKERS] Bug? Indira Muthuswamy <indumuthu@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Jim C. Nasby @ 2006-10-18 18:43 UTC (permalink / raw)
To: Indira Muthuswamy <indumuthu@gmail.com>; +Cc: pgsql-sql
Moving to -sql.
On Wed, Oct 18, 2006 at 06:53:46PM +0530, Indira Muthuswamy wrote:
> Hai,
>
> I have encountered a problem with PostgreSQL.I have created a table
> 'tab1' with a column 'a' with serial type.I entered 20 records into the
> table.So the query
> select max(a) from tab1;
> returned 20.When I tried the same query after the command
> truncate table tab1;
> I found that the output of the first query as
>
> max
> -----
>
> (1 row)
> When I tried to insert a new row into the table tab1 I found that the
> value at column a incremented to the value 21.
> But I heard from my friends that the value of the serial column gets
> decremented whenever we issue the 'truncate table' command (in MS SQL
> SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify me on
> this?
Your friends are mistaken. TRUNCATE does nothing to affect sequences.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] Bug?
2006-10-18 13:23 Bug? Indira Muthuswamy <indumuthu@gmail.com>
2006-10-18 18:43 ` Re: [HACKERS] Bug? Jim C. Nasby <jim@nasby.net>
@ 2006-10-19 06:58 ` Indira Muthuswamy <indumuthu@gmail.com>
2006-10-19 16:50 ` Re: [HACKERS] Bug? Jim C. Nasby <jim@nasby.net>
2006-10-21 15:25 ` Re: [HACKERS] Bug? Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 2 replies; 10+ messages in thread
From: Indira Muthuswamy @ 2006-10-19 06:58 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-sql
Then how do we clear the values of a serial column(is it done only by
dropping the column?)?
Regards,
M.Indira
On 10/19/06, Jim C. Nasby <jim@nasby.net> wrote:
>
> Moving to -sql.
>
> On Wed, Oct 18, 2006 at 06:53:46PM +0530, Indira Muthuswamy wrote:
> > Hai,
> >
> > I have encountered a problem with PostgreSQL.I have created a table
> > 'tab1' with a column 'a' with serial type.I entered 20 records into the
> > table.So the query
> > select max(a) from tab1;
> > returned 20.When I tried the same query after the command
> > truncate table tab1;
> > I found that the output of the first query as
> >
> > max
> > -----
> >
> > (1 row)
> > When I tried to insert a new row into the table tab1 I found that the
> > value at column a incremented to the value 21.
> > But I heard from my friends that the value of the serial column gets
> > decremented whenever we issue the 'truncate table' command (in MS SQL
> > SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify me
> on
> > this?
>
> Your friends are mistaken. TRUNCATE does nothing to affect sequences.
> --
> Jim Nasby jim@nasby.net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] Bug?
2006-10-18 13:23 Bug? Indira Muthuswamy <indumuthu@gmail.com>
2006-10-18 18:43 ` Re: [HACKERS] Bug? Jim C. Nasby <jim@nasby.net>
2006-10-19 06:58 ` Re: [HACKERS] Bug? Indira Muthuswamy <indumuthu@gmail.com>
@ 2006-10-19 16:50 ` Jim C. Nasby <jim@nasby.net>
1 sibling, 0 replies; 10+ messages in thread
From: Jim C. Nasby @ 2006-10-19 16:50 UTC (permalink / raw)
To: Indira Muthuswamy <indumuthu@gmail.com>; +Cc: pgsql-sql
See section 9.12 of the docs.
On Thu, Oct 19, 2006 at 12:28:58PM +0530, Indira Muthuswamy wrote:
> Then how do we clear the values of a serial column(is it done only by
> dropping the column?)?
>
> Regards,
> M.Indira
>
>
>
> On 10/19/06, Jim C. Nasby <jim@nasby.net> wrote:
> >
> >Moving to -sql.
> >
> >On Wed, Oct 18, 2006 at 06:53:46PM +0530, Indira Muthuswamy wrote:
> >> Hai,
> >>
> >> I have encountered a problem with PostgreSQL.I have created a table
> >> 'tab1' with a column 'a' with serial type.I entered 20 records into the
> >> table.So the query
> >> select max(a) from tab1;
> >> returned 20.When I tried the same query after the command
> >> truncate table tab1;
> >> I found that the output of the first query as
> >>
> >> max
> >> -----
> >>
> >> (1 row)
> >> When I tried to insert a new row into the table tab1 I found that the
> >> value at column a incremented to the value 21.
> >> But I heard from my friends that the value of the serial column gets
> >> decremented whenever we issue the 'truncate table' command (in MS SQL
> >> SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify me
> >on
> >> this?
> >
> >Your friends are mistaken. TRUNCATE does nothing to affect sequences.
> >--
> >Jim Nasby jim@nasby.net
> >EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
> >
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] Bug?
2006-10-18 13:23 Bug? Indira Muthuswamy <indumuthu@gmail.com>
2006-10-18 18:43 ` Re: [HACKERS] Bug? Jim C. Nasby <jim@nasby.net>
2006-10-19 06:58 ` Re: [HACKERS] Bug? Indira Muthuswamy <indumuthu@gmail.com>
@ 2006-10-21 15:25 ` Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 0 replies; 10+ messages in thread
From: Richard Broersma Jr @ 2006-10-21 15:25 UTC (permalink / raw)
To: Indira Muthuswamy <indumuthu@gmail.com>; Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-sql
> Then how do we clear the values of a serial column(is it done only by
> dropping the column?)?
If you look at the following link, you will see that serial is not really a true data-type. It is
merely a short-cut to get the desired results:
http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
Also, if you are interested in resetting your columns "serial" value back to 1 or 0 or -10000, you
can do it using the setval() function for the following link:
http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-10-21 15:25 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-22 19:55 bug.. ? Jeff MacDonald <jeff@hub.org>
2001-02-24 05:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-02 04:36 bug? Minghann Ho <mho@mcs.vuw.ac.nz>
2003-01-02 06:16 ` Re: bug? Tom Lane <tgl@sss.pgh.pa.us>
2006-10-18 13:23 Bug? Indira Muthuswamy <indumuthu@gmail.com>
2006-10-18 13:31 ` Re: Bug? Andrew Dunstan <andrew@dunslane.net>
2006-10-18 18:43 ` Re: [HACKERS] Bug? Jim C. Nasby <jim@nasby.net>
2006-10-19 06:58 ` Re: [HACKERS] Bug? Indira Muthuswamy <indumuthu@gmail.com>
2006-10-19 16:50 ` Re: [HACKERS] Bug? Jim C. Nasby <jim@nasby.net>
2006-10-21 15:25 ` Re: [HACKERS] Bug? Richard Broersma Jr <rabroersma@yahoo.com>
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