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
Java.Lang.Noclassdeffounderror: Com/Fasterxml/Jackson/Databind/Exc/Invaliddefinitionexception
Org.Hibernate.Tool.Schema.Spi.Commandacceptanceexception: Error Executing Ddl Via Jdbc Statement
How to Store Original Date/Time Without Timezone Calculation to Mongodb
Simplifying the If-Else Condition Using Java Functional Style Programming
How to Get Access to Job Parameters from Itemreader, in Spring Batch
After Spring Boot 2.0 Migration: Jdbcurl Is Required With Driverclassname
How to Read All Classes from a Java Package in the Classpath
Jackson Polymorphism: How to Map Multiple Subtypes to the Same Class
I Want to Execute Code Every X Seconds, But Handler.Postdelayed Not Working
Adb Cannot Connect to Daemon At Tcp:5037
Launching Spring Application Address Already in Use
Where Does Gradle Save Dependencies' Jars
How to Find Max Date in List<Object>
How to Set a Ttl for @Cacheable
Simpledateformat Producing Wrong Date Time When Parsing "Yyyy-Mm-Dd Hh:Mm"