Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

create column from selection as interger in postgres

Posted on 2016-10-28
7
Medium Priority
?
181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 32

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 32

Expert Comment

by:Pawan Kumar
ID: 41865187
Could you please post the entire query that you are executing on the database ?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 32

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
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.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

597 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