No operator matches the given name and argument types. You might need to add explicit type casts ---CodePudding

I'm a beginner in SQL and I use PostgreSQL. My database is a list of real estate transactions, 1 row = 1 sale. Here is a description of my tables :

CREATE TABLE adresse (
id SERIAL,
num_voie SMALLINT,
bis_ter VARCHAR(10),
type_voie VARCHAR(30),
voie VARCHAR(50),
cp VARCHAR(10),
ville VARCHAR(100));

CREATE TABLE bien(
id SERIAL,
type_bien VARCHAR(30),
surf_bien SMALLINT,
nbr_piece SMALLINT);

CREATE TABLE vente (
id SERIAL,
date_vente DATE,
prix_vente INTEGER,
id_adresse INTEGER,
id_bien INTEGER);

Also, 'id_adresse' is a FK referencing adresse (id) and 'id_bien' is a FK referencing bien (id).

Here is my problem : when I try to calculate the rate of evolution of sales between the first and the second quarter with this query :

WITH vente_T1 AS (
SELECT count(*) FROM vente WHERE date_vente BETWEEN '2020-01-01' AND '2020-03-31'),
vente_T2 AS (
SELECT count(*) FROM vente WHERE date_vente BETWEEN '2020-04-01' AND '2020-06-30')
SELECT vente_T2-vente_T1 FROM vente_T1, vente_T2;

I have this error :

ERROR:  operator does not exist: record - record 
LINE 5: SELECT vente_T2-vente_T1 FROM vente_T1, vente_T2; 
HINT: No operator matches the given name and argument types. 
You might need to add explicit type casts. 
SQL state: 42883 Character: 216

I have seen in another topic "This was due to the WHERE clause contains String value instead of integer value."

But I don't know how I can calculate the rate of evolution of sales without specify dates...

If you have any idea there are welcome !

CodePudding user response：

Your immediate problem is the expression vente_T2-vente_T1 - you can't subtract a complete record from another record. You can only do that for columns. However in order to access the column within the CTE, you should give them a proper alias:

WITH vente_T1 AS (
  SELECT count(*) as num_vente
  FROM vente 
  WHERE date_vente BETWEEN '2020-01-01' AND '2020-03-31'
), vente_T2 AS (
  SELECT count(*) as num_vente
  FROM vente 
  WHERE date_vente BETWEEN '2020-04-01' AND '2020-06-30'
)
SELECT vente_T2.num_vente - vente_T1.num_vente 
--              ^ column name        ^ column name
FROM vente_T1
  cross join vente_T2;