asked on

create column from selection as interger in postgres

This line
trunc(EXTRACT(hour from "public".pong8_maintable."timestamp8") / 7) as sectionid

make column type double precission but i want only integer how can i change it

$sql ='

CREATE TABLE pong8_StepOneSection as
SELECT
public.pong8_maintable.stockid,
public.pong8_maintable.filname,

date_trunc(\'hour\', public.pong8_maintable."timestamp8") as Date_hour,

trunc(EXTRACT(hour from "public".pong8_maintable."timestamp8") / 7)   as sectionid

Open in new window

Pawan Kumar

Try..

CAST(trunc(EXTRACT(hour from "public".pong8_maintable."timestamp8") / 7) AS Integer) as sectionid

MrTV

ASKER

Hi Pawan Kumar Khowal

pg_query(): Query failed: ERROR: syntax error at or near "EXTRACT" LINE 17: EXTRACT(DOW FROM "public".pong8_maintable."timestamp8") as D...

Pawan Kumar

Could you please post the entire query that you are executing on the database ?

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MrTV

ASKER

Hi Pawan Kumar Khowal

$dro ='DROP TABLE IF EXISTS  public.pong8_one_day_section';
sc_exec_sql($dro);

$sql ='
CREATE TABLE pong8_one_day_section as
SELECT
public.pong8_maintable.stockid,
public.pong8_maintable.filname,
date_trunc(\'hour\', public.pong8_maintable."timestamp8") as Date_hour,
// CAST(trunc(EXTRACT(hour from CAST("public".pong8_maintable."timestamp8" AS TIME) / 7))  AS Integer)  as sectionid
trunc(EXTRACT(hour from "public".pong8_maintable."timestamp8") / 7)   as sectionid  ,
EXTRACT(DOW FROM "public".pong8_maintable."timestamp8") as DayNo,
concat("public".pong8_maintable.filname,\'_\',date("public".pong8_maintable."timestamp8")::text,\'_\', trunc(EXTRACT(hour from "public".pong8_maintable."timestamp8") / 7))as datesection,
public.pong8_maintable."timestamp8",
public.pong8_maintable."Last",
public.pong8_maintable."TradePrice",
public.pong8_maintable."TradeVolume",
public.pong8_maintable."BestBid",
public.pong8_maintable."BidSize",
public.pong8_maintable."BestAsk",
public.pong8_maintable."AskSize",
public.pong8_maintable."Turnover"
FROM
Public.pong8_maintable ' ;
sc_exec_sql($sql);

Open in new window

Error
pg_query(): Query failed: ERROR: syntax error at or near "EXTRACT" LINE 17: EXTRACT(DOW FROM "public".pong8_maintable."timestamp8") as D... ^
Error

MrTV

ASKER

Hi Pawan Kumar Khowal
it works

MrTV

ASKER

Thank you