Postgresql Hint: You Will Need to Rewrite or Cast the Expression. Column "State" Is of Type Status But Expression Is of Type Character Varying

PostgreSQL Hint: You will need to rewrite or cast the expression. column state is of type status but expression is of type character varying

You are using Prepared Statements - PostgreSQL get info from client side, so parameter is varchar because you are using setString method. You should to inform Postgres, so input datatype is different with explicit cast.

PreparedStatement stmt = conn.prepareStatement(
"INSERT INTO Event (EventNum, EventName, startHour, endHour, startMin, endMin, startDate, endDate, State, depName)
VALUES (?, ?, ?, ?, ?, ?, ?::date, ?::date, ?::status, ?)");

All data are passed in text form (it is default) - so there are not a problem with passed values. PostgreSQL uses strict type system - and without explicit casting don't allow cast from varchar to date, enum, int, ...

Execute Values You will need to rewrite or cast the expression

This should work:

from datetime import datetime
psycopg2.extras.execute_values(cursor, insert_stmt, [('AAPL',datetime.fromtimestamp(1528004700)), ('AAPL', datetime.fromtimestamp(1528005600))])

Edit:
In general, Postgres has no way of guessing that your 1528004700 is a timestamp, you need to state it explicitly in some way. Your solution with to_timestamp places "this is a timestamp" on the Postgres side, above code places it on the python side. From the information point of view they are equivalent, I did not check which one is more efficient.

Postgres query error

An INSERT INTO table1 SELECT * FROM table2 depends entirely on order of the columns, which is part of the table definition. It will line each column of table1 up with the column of table2 with the same order value, regardless of names.

The problem you have here is whatever column from cd with the same order value as c_d of the table "region" has an incompatible type, and an implicit typecast is not available to clear the confusion.

INSERT INTO SELECT * statements are stylistically bad form unless the two tables are defined, and will forever be defined, exactly the same way. All it takes is for a single extra column to get added to cd, and you'll start getting errors about extraneous extra columns.

If it is at all possible, what I would suggest is explicitly calling out the columns within the SELECT statement. You can call a function to change type within each of the column references (or you could define a new type cast to do this implicitly -- see CREATE CAST), and you can use AS to set the column label to match that of your target column.

If you can't do this for some reason, indicate that in your question.

PostgreSQL: column BOOLEAN_VALUE is of type numeric but expression is of type boolean Hint: You will need to rewrite or cast the expression

PostgreSQL do not auto-cast int to boolean automatically, eventually you can create your own cast function.

See: https://www.postgresql.org/docs/9.4/static/sql-createcast.html

PostgreSQL error: column qty is of type integer but expression is of type text

To fix this error, you need to cast the types of each column in the select statement:

INSERT INTO account_sale
(qty, price, product_id)

SELECT $1::integer, $2::float, t.id
FROM inventory_product AS t
WHERE t.ebay_sku = $3

UNION

SELECT $4::integer, $5::float, t.id
FROM inventory_product AS t
WHERE t.ebay_sku = $6
...

The closet thing I could find to an explanation to this is in @DenisdeBernardy 's comment in this question:

It's due to the way Postgres coerces types. With a single select, it'll infer the types based on the insert part of the statement, whereas with a union, it'll infer the type based on the first line of the union and fallback to text from lack of hints.

Postgres (ERROR: column date_deadline is of type date but expression is of type text)

You need to cast:

update res_user as ru set
date_of_birth = n.date_of_birth
from (values
('1991-07-30'::date,'User1'),
('1980-06-30'::date,'User2'),
('1975-02-12'::date,'User3'),
) as n(date_of_birth, name)
where n.name = ru.name;

Change type of varchar field to integer: cannot be cast automatically to type integer

There is no implicit (automatic) cast from text or varchar to integer (i.e. you cannot pass a varchar to a function expecting integer or assign a varchar field to an integer one), so you must specify an explicit cast using ALTER TABLE ... ALTER COLUMN ... TYPE ... USING:

ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer);

Note that you may have whitespace in your text fields; in that case, use:

ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (trim(col_name)::integer);

to strip white space before converting.

This shoud've been obvious from an error message if the command was run in psql, but it's possible PgAdmin-III isn't showing you the full error. Here's what happens if I test it in psql on PostgreSQL 9.2:

=> CREATE TABLE test( x varchar );
CREATE TABLE
=> insert into test(x) values ('14'), (' 42 ');
INSERT 0 2
=> ALTER TABLE test ALTER COLUMN x TYPE integer;
ERROR: column "x" cannot be cast automatically to type integer
HINT: Specify a USING expression to perform the conversion.
=> ALTER TABLE test ALTER COLUMN x TYPE integer USING (trim(x)::integer);
ALTER TABLE

Thanks @muistooshort for adding the USING link.

See also this related question; it's about Rails migrations, but the underlying cause is the same and the answer applies.

If the error still occurs, then it may be related not to column values, but indexes over this column or column default values might fail typecast. Indexes need to be dropped before ALTER COLUMN and recreated after. Default values should be changed appropriately.



Related Topics



Leave a reply



Submit