Improve company productivity with a Business Account.Sign Up

x
?
Solved

create column from selection as interger in postgres

Posted on 2016-10-28
7
Medium Priority
?
195 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

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.
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…

606 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