PostgreSQL: 42883 Operator does not exist: timestamp without time zone = text

c# postgresql npgsql petapoco
AndreFeijo picture AndreFeijo · Feb 3, 2017 · Viewed 20.4k times · Source

I am using Npgsql 3.0.3.0 and PetaPoco latest version.

When I run this command:

var dateCreated = DateTime.Now; // just an example
var sql = new Sql("WHERE date_created = @0", dateCreated.ToString("yyyy-MM-dd HH:00:00"));
var category = db.SingleOrDefault<Category>(sql);

I get the following error:

Npgsql.NpgsqlException 42883: operator does not exist: timestamp without time zone = text

I understand the error message is saying I'm trying to compare a timestamp (date) with a text, however for me it's perfectly valid to compare them as I am expecting the following SQL statement to be built:

SELECT * FROM category WHERE date_created = '2017-02-03 15:00:00'

I don't really want to typecast my database column to text for performance reasons.

Answer

Roman Tkachuk picture Roman Tkachuk · Feb 3, 2017

You need to cast value to timestsamp:

var sql = new Sql("WHERE date_created = @0::timestamp", dateCreated.ToString("yyyy-MM-dd HH:00:00"));

Related questions

INSERT data from Textbox to Postgres SQL

I just learn how to connect C# and PostgresSQL. I want to INSERT data from tb1(Textbox) and tb2 to database. But I don't know how to code My previous code is SELECT from database. this is my code private …

Read More
C# Query using Npgsql for Postgresql shows duplicate results and missing table data

I am checking out PostgreSQL as potential substitute to SQLServer, I created a test table in a test database in PostgreSQL public schema and added two rows of data to the test table. Now the problem is when running simple …

Read More
Postgresql - Create Database & Table dynamically

This code is not working. Can anyone direct me where I can find examples of creating a Postgresql database and table on the fly with C#? const string connStr = "Server=localhost;Port=5432; User Id=postgres;Password=enter;Database=postgres"; var …

Read More