Your SQL has two problems:
- You do not use the specific field type for dates
- In case of using a field of type text (
text , etc) you are not using the basic or extended format of date ISO-8601 .
The alphabetical order does not work with the dates as you are using them. As a result:
03-12-2018 is different from
- In particular
03-12-2018 is a date before
3-12-2018 because the first character of the first date (
0 ) is less than the first character of the second (
12-12-2019 is a date before
- This is because the first character of the first date (
1 ) is lower than the first character of the second (
By not using the field type
date you do not get an error when inserting dates that may be wrong in format or content and by not using the recommended ISO format the alphabetical (or alphanumeric) order does not match the order of dates.
As you have defined the schema, you could use text fields using the format
AAAA-MM-DD (4-digit year, 2-digit months, and 2-digit days, all with fill zeros to the left if necessary) :
INSERT INTO detalle_ventas (
fecha_venta, codigo_barra, descripcion, cantidad
) VALUES (
'2018-12-03', 0, 'descripcion', 1
SELECT would work correctly.
But I, personally, would recommend you to pass the field to the guy I have recommended:
ALTER TABLE detalle_ventas
ALTER COLUMN fecha_venta TYPE date USING fecha_venta::text::date;
Or the full definition:
CREATE TABLE detalle_ventas (
fecha_venta date not null,
codigo_barra int not null,
descripcion varchar(50) not null,
cantidad double precision not null
You can see a proof of concept online at this link .