Subject: Missing doc on expression format for ALTER TABLE

From: Josh Berkus <[email-masked]>
Date: Sat, 26 Mar 2005 16:55:25 -0800

Folks,

I've noticed that this statement is missing something:

(from sql-altertable.sgml):

ALTER COLUMN TYPE

 This form changes the type of a column of a table. Indexes and simple table 
constraints involving the column will be automatically converted to use the 
new column type by reparsing the originally supplied expression. The optional 
USING clause specifies how to compute the new column value from the old; if 
omitted, the default conversion is the same as an assignment cast from old 
data type to new. A USING clause must be provided if there is no implicit or 
assignment cast from old to new type. 

... nowhere in this file (or anywhere else that I can tell) is the syntax for 
the USING expression defined.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---

From: Tom Lane <[email-masked]>
Date: Sat, 26 Mar 2005 23:47:59 -0500

> the USING expression defined.

Hm?  It says

	ALTER [ COLUMN ] column TYPE type [ USING expression ]

Surely you're not expecting it to repeat the complete syntax for
<expression>.

			regards, tom lane

---

From: Josh Berkus <[email-masked]>
Date: Sun, 27 Mar 2005 17:11:43 -0800

Tom,

> Surely you're not expecting it to repeat the complete syntax for

Well, yes, actually.  Where in the documentation is a type conversion 
expression defined?   Do we use NEW and OLD?  Or the name of the column?  Or 
something else?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---

From: Alvaro Herrera <[email-masked]>
Date: Sun, 27 Mar 2005 22:00:50 -0400

> expression defined?   Do we use NEW and OLD?  Or the name of the column?  Or 

I think the ALTER TABLE page is in dire need of a "See also" section, at
least.  And a reference to where <expression> is defined would be nice
to have.

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"All rings of power are equal,
But some rings of power are more equal than others."
                                 (George Orwell's The Lord of the Rings)

---

From: Josh Berkus <[email-masked]>
Date: Sun, 27 Mar 2005 21:50:47 -0800

Guys,

> > expression defined?   Do we use NEW and OLD?  Or the name of the column? 
> > Or something else?
>

What I'm pointing out here is that the USING clause can't be just ANY 
expression, it needs to be a specific type of expression.   And the docs 
don't explain what kind of expression is usable here.   Even a single example 
would go a long way ...

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---

From: David Fetter <[email-masked]>
Date: Mon, 28 Mar 2005 08:22:44 -0800

On Sun, Mar 27, 2005 at 09:50:47PM -0800, Josh Berkus wrote:
> Guys,
> 
> > > Well, yes, actually.  Where in the documentation is a type
> > > conversion expression defined?   Do we use NEW and OLD?  Or the
> > > name of the column?  Or something else?
> >
> > I think the ALTER TABLE page is in dire need of a "See also"
> > section, at least.  And a reference to where <expression> is
> > defined would be nice to have.
> 
> What I'm pointing out here is that the USING clause can't be just
> ANY expression, it needs to be a specific type of expression.   And
> the docs don't explain what kind of expression is usable here.
> Even a single example would go a long way ...

I sent a patch in awhile ago that added this:

"To change an integer column containing UNIX timestamps to timestamp
with time zone via a USING clause:

ALTER TABLE foo
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
"

That is at least something, although I agree that more examples of the
exact kind of expression would be a very good thing.

Cheers,
D
-- 
David Fetter [email-masked] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!