Error invalid input syntax for type double precision

I’ve created functions and one of them is:

CREATE OR REPLACE FUNCTION core.cal_status(
  er_v                 DOUBLE PRECISION,
  cell_v               DOUBLE PRECISION
) RETURNS DOUBLE PRECISION
  LANGUAGE plpgsql
AS $$
DECLARE out DOUBLE PRECISION;
  BEGIN
    IF er_v < 15                                THEN out := 'LOW_LOAD';
    ELSEIF cell_v < 60                          THEN out := 'LOW_LOAD';
    ELSEIF er_v > 40                            THEN out := 'CONGESTED';
    ELSEIF cell_v > 80                          THEN out := 'CONGESTED';
    ELSEIF cell_v >60 and cell_v < 80           THEN out := 'HIGH_LOAD';
  END IF;
  RETURN out;
  END;
$$;

When I call functions with this:

LEFT JOIN LATERAL core.cal_status(
            er_v,
            cell_v) status ON true

I got next error:

ERROR: invalid input syntax for type double precision: "LOW_LOAD" Where: PL/pgSQL function core.cal_status(double precision,double precision) line 4 at assignment

What is this about, I guess it’s something with output type, but not sure.

So I’m asked to do this query for a college project:

SELECT l.city AS name, AVG((pr.ap_price::double precision
                            * 7 - pr.weekly::double precision)
                           /((pr.ap_price::double precision))*100) AS savings_percentage 
FROM locations AS l, price AS pr, apartments AS ap, hosts AS h
WHERE pr.id_apt = ap.id_apartment AND l.id_apt = ap.id_apartment AND h.host_id = ap.id_host AND h.host_identity_verified = 't'
GROUP BY l.city
ORDER BY savings_percentage DESC LIMIT 3;

Where pr.ap_price and pr.ap_weekly are both saved as strings (VARCHAR(255)), and I’ve tried to cast them as other types wether using CAST(pr.ap_price AS double precision) or using the option found in the code above.
It always ends up showing the same error wether I cast it to numeric or double precision:
ERROR: la sintaxis de entrada no es válida para tipo double precision: «1,100.00» SQL state: 22P02
(In Spanish but basically says ERROR: the entrance syntaxis isn’t valid for type double precision)

How can I cast it correctly? My objective is to turn it from string to numbers to be able to compare them.

I have also tried using:
ALTER TABLE apartments_importacio ALTER COLUMN price TYPE double precision USING (price::double precision); but the same error shows. (Where apartments_importacio is the table where I copy the .csv file to later insert into the other tables)

Any clues? Thanks in advance!

Answer

I’m going to say it is because of this:

select ''::double precision;
ERROR:  invalid input syntax for type double precision: ""
LINE 1: select ''::double precision;

select '10'::double precision;
 float8 
--------
     10
(1 row)

The reason being an empty string is not a valid number.

One solution is to do:

select nullif(trim('  '), '')::double precision;
 nullif 
--------
   NULL
(1 row)

--- So for your case:

nullif(trim(pr.ap_price), '')::double precision

Otherwise you will need to go through an clean up the empty strings in those columns and change them to 0 or NULL. Long term I would say making those field float8 or better yet numeric is the better solution. Then you deal with the issue on creating the record.

UPDATE. Dealing with number formatting:

select '1,100.00'::double precision;
ERROR:  invalid input syntax for type double precision: "1,100.00"
LINE 1: select '1,100.00'::double precision;
               ^
select '1100.00'::double precision;
 float8 
--------
   1100
(1 row)

The solution to above is:

select to_number('1,100.00', '9999.99')::double precision;
 to_number 
-----------
      1100
(1 row)

For more information see

https://www.postgresql.org/docs/current/functions-formatting.html

to_number(text, text)   numeric     convert string to numeric   to_number('12,454.8-', '99G999D9S')```

See:

Table 9.27 shows the template patterns available for formatting numeric values.

For more information on your options for formatting.

Пытаюсь загрузить данные csv в postgres. Часть таблицы создания в порядке. Но когда я пытаюсь загрузить данные из csv, возникает ошибка. Мой код и ошибка прилагаются ниже. % S ошибается?

import psycopg2
import csv 

conn = psycopg2.connect(host = "127.0.0.1", port = "5432", database = "postgres", user = "postgres", password = "*******")
print "Opened database successfully"

cur = conn.cursor()     
cur.execute('''create table calls_aapl("Ask" float,"Bid" float,"Change" float,"ContractSymbol" varchar(50),"ImpliedVolatility" float,"LastPrice" float,
                  "LastTradeDate" date,"OpenInterest" int,"PercentChange" float,"Strike" float,"Volume" int);''')
print "Table created successfully"

reader = csv.reader(open('D:/python/Anaconda/AAPL_Data/Calls.csv', 'r'))

for i, row in enumerate(reader):
    print(i, row)
    if i == 0: continue


    cur.execute('''
        INSERT INTO "calls_aapl"(
            "Ask", "Bid", "Change", "ContractSymbol", "ImpliedVolatility", "LastPrice", "LastTradeDate", "OpenInterest", "PercentChange", "Strike", "Volume"
        ) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''', row
    )

conn.commit()
cur.close()

Ошибка:

(0, ['Ask', 'Bid', 'Change', 'ContractSymbol', 'LastPrice', 'LastTradeDate', 'OpenInterest', 'PercentChange', 'PercentImpliedVolatility', 'Strike', 'Volume'])
(1, ['41.7', '39.75', '1.15', 'AAPL180803C00150000', '41.05', '7/31/2018', '52', '2.88', '154.59', '150', '6'])
DataError: invalid input syntax for type double precision: "7/31/2018"
LINE 4: ...1.7','39.75','1.15','AAPL180803C00150000','41.05','7/31/2018...
                                                             ^

I’ve created functions and one of them is:

CREATE OR REPLACE FUNCTION core.cal_status(
  er_v                 DOUBLE PRECISION,
  cell_v               DOUBLE PRECISION
) RETURNS DOUBLE PRECISION
  LANGUAGE plpgsql
AS $$
DECLARE out DOUBLE PRECISION;
  BEGIN
    IF er_v < 15                                THEN out := 'LOW_LOAD';
    ELSEIF cell_v < 60                          THEN out := 'LOW_LOAD';
    ELSEIF er_v > 40                            THEN out := 'CONGESTED';
    ELSEIF cell_v > 80                          THEN out := 'CONGESTED';
    ELSEIF cell_v >60 and cell_v < 80           THEN out := 'HIGH_LOAD';
  END IF;
  RETURN out;
  END;
$$;

When I call functions with this:

LEFT JOIN LATERAL core.cal_status(
            er_v,
            cell_v) status ON true

I got next error:

ERROR: invalid input syntax for type double precision: "LOW_LOAD" Where: PL/pgSQL function core.cal_status(double precision,double precision) line 4 at assignment

What is this about, I guess it’s something with output type, but not sure.

DataError: (psycopg2.DataError) invalid input syntax for type double precision: "a"
 [SQL: 'INSERT INTO osmaxx.building_an  SELECT osm_id as osm_id,n    osm_timestamp as lastchange ,n    CASEn     WHEN osm_id<0 THEN 'R' -- R=Relationn     ELSE 'W'               -- W=Wayn     END AS geomtype,n    ST_Multi(way) AS geom,n    building as type,n    name as name,n    "name:en" as name_en,n    "name:fr" as name_fr,n    "name:es" as name_es,n    "name:de" as name_de,n    int_name as name_int,n    casen        when name is not null AND name = osml10n_translit(name) then namen        when "name:en" is not null then "name:en"n        when "name:fr" is not null then "name:fr"n        when "name:es" is not null then "name:es"n        when "name:de" is not null then "name:de"n        when name is not null then osml10n_translit(name)n        else NULLn    end as label,n    cast(tags as text) as tags,n    cast(nullif(height,'') as float) as heightn  FROM osm_polygonn  WHERE building is not null;n']
(16 additional frame(s) were not displayed)
...
  File "osmaxx/conversion/converters/converter_gis/bootstrap/bootstrap.py", line 30, in bootstrap
    self._filter_data()
  File "osmaxx/conversion/converters/converter_gis/bootstrap/bootstrap.py", line 79, in _filter_data
    self._execute_sql_scripts_in_folder(script_folder_path)
  File "osmaxx/conversion/converters/converter_gis/bootstrap/bootstrap.py", line 106, in _execute_sql_scripts_in_folder
    self._postgres.execute_sql_file(script_path)
  File "osmaxx/conversion/converters/converter_gis/helper/postgres_wrapper.py", line 27, in execute_sql_file
    return self.execute_sql_command(psql_command_file.read())
  File "osmaxx/conversion/converters/converter_gis/helper/postgres_wrapper.py", line 35, in execute_sql_command
    result = connection.execute(sqlalchemy.text(sql))