Specify datatype in CREATE TABLE AS statement

Question

I am creating a table dummy2 using the values in another table dummy. This is my syntax:

CREATE TABLE dummy2 AS SELECT
t1.zid AS orig_id,
t2.zid AS dest_id,
ABS(RANDOM()*(45-2)-45) AS dist
FROM dummy t1, dummy t2;

The attribute zid in dummy is stored as DOUBLE PRECISION. However, in the new table I would like to change its data-type to say, INTEGER. How can I achieve this? My initial thoughts were this (very similar to the way we declare the data-type when creating a table using the schema:

CREATE TABLE dummy2 AS SELECT
t1.zid AS orig_id INTEGER,
t2.zid AS dest_id INTEGER,
ABS(RANDOM()*(45-2)-45) AS dist
FROM dummy t1, dummy t2;

But it fails to execute. Can anyone help me out with this? Appreciate the assistance!

Did you try explicitly CASTing or CONVERTing the zid column to INTEGER, in the SELECT statement? — Vikdor
– Vikdor, Commented Oct 6, 2012 at 11:39

Clodoaldo Neto · Accepted Answer · 2012-10-06 11:40:32Z

3

CREATE TABLE dummy2 AS SELECT
cast(t1.zid as integer) AS orig_id,
cast(t2.zid as integer) AS dest_id,
ABS(RANDOM()*(45-2)-45) AS dist
FROM dummy t1, dummy t2;

answered Oct 6, 2012 at 11:40

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Akshay Hazari Over a year ago

can we do cast null as integer and expect it to be integer

Akshay Hazari Over a year ago

If all values are null what does the column data type turn out to be?

Rosdi Kasim Over a year ago

@AkshayHazari CAST(NULL AS INTEGER) will force the type to be integer.

Collectives™ on Stack Overflow

Specify datatype in CREATE TABLE AS statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related