Solved

create column from selection as interger in postgres

Posted on 2016-10-28
7
36 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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Try..

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

Author Comment

by:teera
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Could you please post the entire query that you are executing on the database ?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
Comment Utility
Try...

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

Author Comment

by:teera
Comment Utility
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
Comment Utility
Hi Pawan Kumar Khowal  
it works
0
 

Author Closing Comment

by:teera
Comment Utility
Thank you
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
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…
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.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now