[SQL] About "Alter table... alter column.. TYPE ... "

[Ying Lu]

Hello,
To alter table column from varchar(32) to date. "Alter table" command 
does not seem to work:

alter table test  alter column col type date ;
ERROR:  column "col1" cannot be cast to type "date"
Tks,
Emi
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] SQL command Error: "create table ... Like parentTable including defaults"

[Ying Lu]

Greetings,

I have a simple question about SQL command :

create table tableName1 LIKE parentTable   INCLUDING defaults ;


I was trying to create table "tableName1" with the same structure as 
"parentTable" without any data. I got a syntax error: 
'syntax error at or near "like" ... '


I guess there must be something wrong with my sql command, could 
somebody help?


Thanks a lot,
Emi

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

[SQL] Index creation question for expression (col1 || '-' || col2)

[Ying Lu]

Greetings,

A question about creating index for the following expression.

CREATE INDEX idx_t1 ON test (col1 || '-' || col2);

May I know is it possible and how I may create index for the expression 
such as "col1 || '-' || col2" for a table please?


Thanks a lot,
Emi



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Index creation question for expression (col1 || '-' ||

[Ying Lu]

On Fri, Jul 08, 2005 at 12:08:41PM -0400, Ying Lu wrote:
 


CREATE INDEX idx_t1 ON test (col1 || '-' || col2);

May I know is it possible and how I may create index for the expression 
such as "col1 || '-' || col2" for a table please?
   




"The syntax of the CREATE INDEX command normally requires writing
parentheses around index expressions, as shown in the second
example"

That second example is exactly what you're trying to do:

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
 



This is exactly what I want. I missed a pair of "()" :( . That is why I 
got an error.


Thank you very much.

- Emi


---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] About retrieving objects' priviledge info such as grantee & its privileges for a specific object (view, table, function, etc.)

[Ying Lu]

Hello,

May I know the commands to retrieve objects' privileges info please? 
Something like:



Object Type, Object name,creator,grantee,
privilege, is_creatable

=
table  T1 user1user2
select, update, delete   no
table  T1 user1user3 
selectno
view   V1user2 user4
selectno


P.S. (postgreSQL 8.0.1)

Thanks a lot,
Emi

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings