CODEWITHSUNDEEP
sqlpostgresql
user10624646
user10624646

Reputation:

How to set datatype of SQL column in Alias?

If I have a query such as: 

select name as first_name 
from my_table

How can I set the datatype of that column in Postgresql to be character varying or anything in future - is there a way such as: 

select name as first_name character varying
from my_table

I assume there is a way to set a datatype of a column after the aliasing - unsure how in postgresql

I am using postgresql and pgadmin4

Upvotes: 2

Views: 2424

Answers (4)

jarlh
jarlh

Reputation: 44786

Use cast to change the data type:

select cast(name as character varying(100)) as first_name
from my_table

But I'd consider altering the table column instead, to be varchar(100):

alter table my_table alter column name type character varying(100)

Upvotes: 2

kojo justine
kojo justine

Reputation: 123

To easily cast your result to any datatype use :: datatype as in below:

select name as first_name ::varchar from my_table

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

In Postgres, I would use the short-hand syntax for type conversion :::

select name::varchar as first_name

Of course, the right thing to do is to fix the type in the table, but that is already covered by other answers.

Upvotes: 0

Shubham Shewdikar
Shubham Shewdikar

Reputation: 91

Use new datatype for column after TYPE keyword in PostgreSQL

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_datatype;

Let's take an example -

  CREATE TABLE Test (
        emp_id serial PRIMARY KEY,
        emp_name TEXT NOT NULL,
    );

  INSERT INTO Test (emp_id, emp_name) VALUES ('abc00012','Shubham');

  ALTER TABLE Test ALTER COLUMN emp_name TYPE VARCHAR;

Upvotes: 0

Related Questions