Why does SQL Server say it can’t convert varchar to numeric?

I have the following schema/data in MSSQL Server 2019 (SQLFiddle):

CREATE TABLE products(     idn NUMERIC(9) PRIMARY KEY );  CREATE TABLE sales(     idn NUMERIC(9) PRIMARY KEY,     pid VARCHAR(50) NOT NULL,     type VARCHAR(10) NOT NULL );  INSERT INTO products (idn) VALUES (1); INSERT INTO sales (idn, pid, type) VALUES (1, 1, 'number'); INSERT INTO sales (idn, pid, type) VALUES (2, 'Colgate', 'word'); 

sales has mixed data i.e VARCHAR and NUMERIC. The transaction filter takes care of JOINing correctly.

Why does the following SELECT fail?

SELECT      *  FROM      products      INNER JOIN sales ON products.idn = sales.pid      AND sales.type = N'number'  WHERE      products.idn in (1);  

I don’t see why casting NVARCHAR to NUMERIC is an issue:


If I modify the query slightly it works:

SELECT      *  FROM      products      INNER JOIN sales ON products.idn = sales.pid      AND sales.type = N 'number'  WHERE     -- Selecting the same data from `sales`.     sales.pid in (1);  SELECT      *  FROM      products      INNER JOIN sales ON products.idn = sales.pid     -- Dropping the `N` prefix.     AND sales.type = 'number'  WHERE      products.idn in (1);  

How to implement a unique key for a long VARCHAR column in MySQL

I need to let MySQL to check the uniqueness of a VARCHAR column which is 5000 max length and is not the primary key. I can use an index of length 500 on the column to speed up query, but how to create an unique key that works?

An unique key of length 500 fails when trying to insert two records whose that column is the same for the first 500 characters and differs on somewhere after that.

Redshift Hash Join – Casting Varchar as Text to FK when both same type?

I have a SQL statement that is JOINing some outer table on some inner table ON “somekey”. Both the outer table’s and inner table’s “somekey” are exactly the same datatype – varchar(36). However, for some reason when analyzing the query plan, Redshift wants to cast each as text first. I imagine this might reduce performance. 1) Why is it casting? 2) Can I make it not cast? 3) Does it matter (does it actually hurt performance)?

The partial plan:

  • XN Hash Join DS_DIST_ALL_NONE (cost=64.17..2067242.11 rows=28074 width=160)
    • Hash Cond: ((“outer”.somekey)::text = (“inner”.somekey)::text)

mysql: может ли у поля с типом varchar быть primary key?

Нужно создать таблицу, в которой буду хранить хеши объектов (товары, заказы и т.д.)

насколькоя понимаю, то в таблице могут быть только 2 поля:

entity_id - primary hash - varchar, not null, len 255 

Вопрос в том, что entity_id может быть не числовым, поэтому скорее всего ему нужно задать тип varchar. Можно ли так делать?

Или как лучше сделать, чтобы потом поиск по этой таблице работал быстро? Размер таблицы будет примерно 500 тыс записей, нужно часто искать в ней, и изменять хеши (итогда нужно будет и удалять записи, но это редко).

Помогите пожалуйста правильно спроектировать таблицу.

Как конвертировать переменную в varchar перед передачей в функцию postgres?

Так вызываю процедуру:

try:     c.callproc('add_image', (str(uploadet_link), str(request.user.password)))                    finally:     c.close() 

и получаю ошибку:

LINE 1: SELECT * FROM add_image(‘https://i.imgur.com/CEDzlS4.jpg’,’!… ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Сделал вот такой запрос, чтобы определить тип переменных которые я передаю:

SELECT pg_typeof('https://i.imgur.com/DPalXFO.jpg'); --unknown SELECT pg_typeof('https://i.imgur.com/DPalXFO.jpg'::varchar); --character varying 

получается нужно конвертировать данные в самой postgres функции? Но ведь этого нельзя сделать на этапе передачи параметров? Еще так пробовал:

c.execute('select * from add_imgae(%s::varchar, %s::varchar)', (uploadet_link, request.user.password)) 

но не помогло.

Функция выглядит так:

create or replace function add_imgae(image_url character varying, user_password character varying)  returns pg_catalog.void as  $  $    declare  user_id int;  begin  user_id := (select id from auth_user where auth_user.password =   user_password);  insert into public.images(image_url, autor_id, views, rating) values   (image_url, user_id, 0, 0);  end;  $  $    language plpgsql 

Как еще можно конвертировать переменную в varchar? Или только с помощью str(var_name)? Тогда может в другом проблема?

Fix OGR2OGR “failed when converting the varchar value to data type int” issue in the MSSQL table –

I am a newbie to MSSQL and am trying to convert an MSSQL table to Mapinfo Tab file using OGR2OGR but it keeps failing with the above message on a few tables.

I have tried to get a reply in the GIS SE about switches to apply in OGR2OGR but have not got any options. https://gis.stackexchange.com/questions/314669/ogr2ogr-conversion-failed-when-converting-the-varchar-value-to-data-type-int

How can I change this in the table?enter image description here

What does NCPR mean -this text doesn’t exist in the table -it it code for something?

Is it possible to reduce the default varchar size in catalog_product_flat? (M2)

I have a seemingly common problem. With too many custom attributes that are necessary and no way to change them in a way that would make the use of the default flat catalog possible I am searching for another solution.

Magento by default sets the size of attributes stored as varchar in flat tables to 255, which in my case is unnecessarily long. I could totally live with a size of 65, which would also bypass the MySQL limitation problem.

I have found hints on how to adjust Magento 1 in that way but not a single clue on how to do that with Magento 2. What I would like to know is:

  • Wether or not this is possible at all, e.g. where to find the sizing in the code,
  • And if it is, what kind of problems could emerge doing it.

UPDATE: I have found a way to do it, which seems really convenient. I added

if ($  fieldName != "description" && $  columnLength > 200){    $  columnLength = 65; } 

right before

$  table->addColumn($  fieldName, $  fieldProp['type'], $  columnLength, $  columnDefinition, $  columnComment); 

in Magento/Catalog/Model/Indexer/Product/Flat/FlatTableBuilder

Creating the flat table now works like a charm, but my second question remains: What kind of implications could this have?

Why Char works when varchar dont?

foo VARCHAR, bar VARCHAR(30) DEFAULT 'chilli pepper',

I dont know why this part of query doesnt work but it does accept it when i use char. I used https://www.eversql.com/sql-syntax-check-validator/ to determine error, since this query also have 2 FK that are referenced to same column (id), but im not sure that would work.

Simmilar thread but didnt answer my question