On Tue, Dec 10, 2002 at 09:04:07PM +0100, Peter Eisentraut wrote:
> Alvaro Herrera writes:
> 
> > Would it work to make \d tab-completable in a way that showed both the
> > commands that are available and the objects they describe? e.g.
> >
> > \d<tab> would show something like
> > \dt [tables]	\ds [sequences]	\dv [views] ...
> 
> That won't work.  The actual completion and the view of the alternatives
> if the completion is ambiguous is driven by the same data.
> 

Speaking of psql and tab completion, I ran into a bogosity in ALTER
yesterday, in ver 7.3. I was looking to change the ownership of a table,
so I did:

ALTER TABLE foo <tab>
 ADD     ALTER   RENAME 

Hmm, so maybe it's ALTER again? (I don't rember that syntax, but ...)

ALTER TABLE doo ALTER <tab>
 GROUP   SCHEMA  TABLE   USER

huh? ALTER TABLE foo ALTER TABLE? 

<checks \h ALTER  and \h ALTER TABLE>

Seems ALTER TABLE foo <tab> should yield:

ADD DROP ALTER RENAME OWNER TO

And ALTER <tab> (which currently gives GROUP   SCHEMA  TABLE   USER)
should have:

DATABASE GROUP TABLE TRIGGER USER

And ALTER TABLE foo ALTER <tab>
should list column names of foo.

Digging into the source, I see this case was there, but you couldn't
reach it because of the match on ALTER.

I've sent a patch to PATCHES to implement my proposed completions, with
the caveat that they're based on the \h ALTER output, and manual testing,
not on reading the parser in detail. 

Ross


Wed, 11 Dec 2002 11:59:40 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
psql's tab completions for ALTER command
Table:

      Column       |         Type          |                              Modifiers
-------------------+-----------------------+---------------------------------------------------------------------
 imported_date     | integer               | not null default 0


PG v7.2.1 (nice and clean):

select imported_date, "timestamptz"(imported_date) from server_accounts limit 1;
 imported_date |      timestamptz
---------------+------------------------
    1037498593 | 2002-11-16 18:03:13-08
(1 row)


PG v7.3 (nasty and dirty):

select imported_date, "timestamptz"(imported_date) from server_accounts limit 1;
ERROR:  Bad timestamp external representation '1027966107'

select imported_date, "timestamptz"( cast(imported_date as timestamp) )  from server_accounts limit 1;
ERROR:  Cannot cast type integer to timestamp without time zone

select imported_date, "timestamptz"( cast(imported_date as timestamptz) )  from server_accounts limit 1;
ERROR:  Cannot cast type integer to timestamp with time zone

select imported_date, ('Jan 1 1970'::DATE + (imported_date || ' seconds')::INTERVAL)::timestamp from server_accounts limit 1
 imported_date |      timestamp
---------------+---------------------
    1027966107 | 2002-07-29 18:08:27

The last query works, but you must admit it is pretty nasty. Yes, I could create a from_epoch() function that takes care of this, but should this really be nessecary?

Is there a better way to go about this in v7.3 without changing the column type?




11 Dec 2002 10:33:44 -0800
Mike Benoit <mikeb@netnation.com>
Frustration with date/times/epoch in v7.3.