PostgreSQL 9.3: ERROR: argument of WHERE must be type boolean, not type text

Question

I have created following table with five columns.

CREATE TEMP TABLE t_Test (
  colname   text,
  coldt     timestamp,
  coltm     timestamp,
  colaz     text,
  colx      integer  
);

Insertion:

INSERT INTO t_Test VALUES 
  ('X','2010-01-01','1900-01-01 01:01:25', 'Green', 1), ('B','2010-01-02','1900-01-01 11:32:15', 'Red', 2)
, ('Z','2010-01-03','1900-01-01 02:01:25', 'Green', 4), ('E','2010-01-04','1900-01-01 11:01:55', 'Red', 5)
, ('G','2010-01-05','1900-01-01 03:05:25', 'Red', 7);

Note: Now I want to show the result in the pivot format of the above data for which I am using the cross tab query as shown below.

I want to show only those records which matches the date and time condition.

CrossTab Query:

SELECT * FROM   crosstab(
      'SELECT colname, colaz, colx
       FROM   t_test
       WHERE to_char(coldt,''YYYY-MM-DD'') || '' '' || to_char(coltm,''HH12:MI:SS'')
      >= to_char(to_date(''01-01-2000'',''dd-mm-yyyy''), ''yyyy-mm-dd'') ||  ''11:50:01'''
)  
AS ct ("ColName" text, "Green" int, "Red" int);

While executing getting an error:

Error Details:

ERROR:  argument of WHERE must be type boolean, not type text
LINE 3:        WHERE to_char(coldt,'YYYY-MM-DD') || ' ' || to_char(c...

compyutech · Accepted Answer · 2015-12-18 10:21:11Z

2

Place both side of equation in bracket like below

SELECT * FROM   crosstab(
      'SELECT colname, colaz, colx
       FROM   t_test
       WHERE (to_char(coldt,''YYYY-MM-DD'') || '' '' || to_char(coltm,''HH12:MI:SS''))
      >= (to_char(to_date(''01-01-2000'',''dd-mm-yyyy''), ''yyyy-mm-dd'') ||  ''11:50:01''')
)  
AS ct ("ColName" text, "Green" int, "Red" int);

answered Dec 18, 2015 at 10:21

compyutech

5813 gold badges8 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL 9.3: ERROR: argument of WHERE must be type boolean, not type text

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related