Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

create column from selection as interger in postgres

Posted on 2016-10-28
7
Medium Priority
?
188 Views
Last Modified: 2016-11-02
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

0
Comment
Question by:teera
  • 4
  • 3
7 Comments
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41865084
Try..

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

Author Comment

by:teera
ID: 41865185
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...
0
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41865187
Could you please post the entire query that you are executing on the database ?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 38

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41865199
Try...

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

Author Comment

by:teera
ID: 41870164
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
0
 

Author Comment

by:teera
ID: 41870173
Hi Pawan Kumar Khowal  
it works
0
 

Author Closing Comment

by:teera
ID: 41870174
Thank you
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question