Questions tagged [cast]

Converts an expression of one data type to another.

Filter by
Sorted by
Tagged with
0 votes
1 answer
96 views

What is the idiomatic way to truncate a string down to 100 byte-pairs?

nvarchar(100) holds 100 byte-pairs, as documented here. LEFT([Your String], 100) truncates your string down to 100 characters as documented here. These are not the same. Foolishly, I have used LEFT([...
J. Mini's user avatar
  • 93
4 votes
1 answer
280 views

Postgres strange conversion of the string 'now' to timestamp

It looks like postgres treats the string 'now()' the same as a call to the now() function. Why does postgres allow this? select 'now'::timestamp; or this? select 'now()'::timestamp; or even this? ...
Christian Long's user avatar
0 votes
1 answer
207 views

PostgreSQL 15: ERROR: operator is not unique: unknown || double precision

we are using a PostgreSQL version 15 and have added 13 conversions from this blog entry: http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html However, only now we ...
Athris's user avatar
  • 11
1 vote
1 answer
21 views

How can I sort by "release" in Mysql, where e.g. 10.10 comes after 10.9, with non-numerical entries both before and after?

If I have a table as per https://www.db-fiddle.com/f/arPEdUty3U6AJEQfqYEyRc/1 with a list of releases: INSERT INTO `releases` (`ReleaseID`, `ReleaseNumber`) VALUES (1, '10.6'), (2, '10.8'), (3, '_TBD')...
Ben Holness's user avatar
2 votes
2 answers
1k views

XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels

This is the error message I get when running the query below: Msg 6335, Level 16, State 102, Line 43 XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels. ...
Marcello Miorelli's user avatar
0 votes
0 answers
18 views

Combine INT, TIME fields and compute Elapsed Percentage

Need calculation and conversion help... Columns: [Days] INT [hh:mm:ss] Time [AllowedRuntimeMinutes] INT Need to combine [Days]+[hh:mm:ss] as the duration the query has been running, and then calculate ...
CrushingIT's user avatar
3 votes
1 answer
681 views

In Snowflake, how do I CAST CURRENT_TIMESTAMP up to the minute only?

In Snowflake, how do I CAST CURRENT_TIMESTAMP up to the minute only. I don't want seconds, milliseconds, or timezone included in my result.
Jimbo's user avatar
  • 65
1 vote
0 answers
596 views

Convert Integer Array to JSON Array

I'm not sure how I should go about converting int[] to json[], here's what I've tried so far: db=# alter table product alter column images type json[] using images::json[]; ERROR: cannot cast type ...
Shayan's user avatar
  • 111
2 votes
1 answer
822 views

How can I utilize an index with a different type or collation in a LEFT JOIN in MySQL?

I am trying to debug a slow query, and when I EXPLAIN the SQL, I get a warning: Warning: #1739 Cannot use ref access on index '[index name]' due to type or collation conversion on field '[field name]' ...
Ben Holness's user avatar
0 votes
2 answers
2k views

Convert hex string representation to hex number in SQL Server T-SQL

I have been given a dataset where hexadecimal numbers have been stored as a quasi-hex string representation intended to be human-friendly, e.g. "16A1". Is there a conversion function in T-...
Tim's user avatar
  • 545
0 votes
1 answer
107 views

Why isn't it trivial to create a virtual integer timestamp column from a TIMESTAMP column in MySQL?

There's no way to extract an integer timestamp from a TIMESTAMP column in a virtual column context in MySQL because UNIX_TIMESTAMP is not allowed in virtual columns. Why isn't this a trivial task?
Dan's user avatar
  • 235
0 votes
2 answers
209 views

Can I create a cast from a composite (table or row) type to a domain?

Let's say I have a simple table foo, and domain bar CREATE TABLE foo(x) AS VALUES (1),(2),(3); CREATE DOMAIN bar AS text; Is it possible to make a cast such that I can do SELECT foo::bar FROM foo; ...
Evan Carroll's user avatar
  • 62.7k
2 votes
1 answer
324 views

How to copy data between postgres schemas if there are objects?

I removed shop from database, that should not be removed. Fortunately I had backup and reverted this backup to schema public_backup. Now I am trying to restore only this shop. I can't override my ...
Daniel's user avatar
  • 209
1 vote
1 answer
386 views

Best way to cast a VIEW row type to the underlying TABLE row type

I have a table with an index and a row-level security policy. Due to this problem (more details: 1, 2, 3, 4, 5), the index is not used when the policy applies, which makes my queries unbearably slow. ...
Bergi's user avatar
  • 504
3 votes
1 answer
608 views

I can't understand a syntax error issue in SQL / TimeScaleDB

This does NOT compile: SELECT add_compression_policy( hypertable => 'exchange.candles', compress_after => INTERVAL ((SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange....
Thomas's user avatar
  • 293
0 votes
1 answer
466 views

MySQL incorrect equality casting bigint to char

Why is the following statement true in MySQL? CAST(544553000004545482 AS CHAR) = 544553000004545446 --Background-- I am querying a database with user IDs stored as VARCHAR(255), but my program ...
Eric's user avatar
  • 3
0 votes
2 answers
7k views

Convert an optional JSONB field to a text array?

In my_table, the misc column is jsonb with default value {}. Some rows have misc.names as an array of text values; other rows do not have the misc.names key. I want to copy that data to a separate ...
Nathan Long's user avatar
2 votes
1 answer
1k views

Is there Postgres workaround for MSSQL's try_cast function as used in my CASE expression?

I am in need of some assistance/advice please. I have a query in MSSQL as an openquery to a Postgres Linked Server; a CASE statement actually, in a query that allowed me to check to see if a value in ...
ripvw32's user avatar
  • 23
1 vote
1 answer
975 views

Convert Varchar to Interval

I have a Postgresql column that is a varchar that is saving a string that looks like '2 years, 5 months' or '3 months' or '9 years, 0 months'. I can't convert the column to an interval because it's ...
thesofaking's user avatar
9 votes
1 answer
4k views

Why does the cast from double precision to numeric round to 15 significant digits?

The cast from double precision (float8) to numeric rounds to 15 significant decimal digits, thereby losing information. Clearly, more precision is possible. The cast to bigint (for values within its ...
Erwin Brandstetter's user avatar
1 vote
1 answer
848 views

Help understanding behavior of try_cast() with numeric strings

Erwin made a great, generic try_cast function for postgres (copied below). I'm interested in using it for an EAV schema where attributes are stored with a type field. I can use a similar function (...
Optimum's user avatar
  • 13
0 votes
2 answers
1k views

How to case text array elements automatically?

According to PostgreSQL documentation on the ARRAY constructor: By default, the array element type is the common type of the member expressions, determined using the same rules as for UNION or CASE ...
tinlyx's user avatar
  • 3,500
0 votes
2 answers
433 views

Why does Casting this string as a decimal fail?

Why does casting this result of REGEXP_SUBSTR() to a DECIMAL fail? SELECT REGEXP_SUBSTR('Cost (-$14.18)', '(?<=Cost [(]-[$])[0-9.]+') AS _extracted, CAST(REGEXP_SUBSTR('Cost (-$14.18)', '(?&...
But those new buttons though..'s user avatar
0 votes
1 answer
5k views

Converting DATE and TIME integers to timestamp

I am trying to get a timestamp by combining date and time columns in my PostgresQL database and am running into errors. Here is what I have: +-----------+-----------+ | StartDate | StartTime | +-------...
Sidhu177's user avatar
  • 101
0 votes
0 answers
698 views

How to convert bit(n) to bytea when n >= 256?

How can I convert a BIT(256) into 64-byte BYTEA? More generally, how can I convert from BIT(n) to BYTEA when n is large enough that I can't cast the bits through an INT8? Example: I've got a bunch of ...
Steven Kalt's user avatar
0 votes
1 answer
20 views

Verify day falls in month

I have a table in BigQuery with Xday, Xmonth, and Xyear columns but the data was user generated and apparently done without adequate input validation so some of it is nonsense. It's easy enough to do ...
Bobbie D's user avatar
  • 115
6 votes
2 answers
4k views

PostgreSQL - CAST vs :: operator on LATERAL table function

While I can SELECT elem[1], elem[2] FROM ( VALUES ('1,2'::TEXT) ) AS q(arr), LATERAL CAST(String_To_Array(q.arr, ',') AS INT[]) AS elem ; using an explicit call to CAST, I can't SELECT ...
geozelot's user avatar
  • 183
7 votes
4 answers
11k views

Error converting VARCHAR(MAX) to XML due to "UTF-8" attribute

I need to dig into a logs table with a schema similar to this: CREATE TABLE t ( id int PRIMARY KEY, data varchar(max) ); Column data stores a XML text received from a web service in this format: ...
McNets's user avatar
  • 23.7k
2 votes
1 answer
436 views

What is the best way to divide data for random groups?

I would like to have persisted column which will randomly group data for 32 groups according to one of varchar key column in table. My idea was: SELECT ABS(CAST(HASHBYTES('MD5',[keyColumnFromTable]) ...
axdna's user avatar
  • 111
3 votes
1 answer
2k views

PostgreSQL ignores implicit casts in some situations

The setup (tried only on PostgreSQL 9.6): CREATE TYPE MY_ENUM AS ENUM ('a', 'b'); CREATE CAST (CHARACTER VARYING AS MY_ENUM) WITH INOUT AS IMPLICIT; CREATE TABLE t (x MY_ENUM); INSERT INTO t VALUES ('...
Eugene Pakhomov's user avatar
1 vote
1 answer
6k views

How to prevent implicit casting from bytea to text during the current connection in postgres?

In Postgres bytea values are automatically converted to text when inserted to text/varchar columns, based on the bytea_output setting. I am working with some program code which automatically converts ...
user183966's user avatar
1 vote
1 answer
9k views

MySQL Cast to TINYINT [closed]

I'm trying to cast a string data to a TINYINT data but I'm getting below error. SELECT CAST('100' as TINYINT); > 1064 - You have an error in your SQL syntax; check the manual that corresponds to ...
Majid's user avatar
  • 121
0 votes
1 answer
337 views

Convert a numeric column with number eg.(-1222222) to two float column with two decimal places eg (-12222.22)

I know there are different variations of this question asked. But what I am trying to do is that, there is a table with a numeric column (12,0) that has data looking like Units ----- -1222222 0 -19 ...
masksoverflow's user avatar
2 votes
2 answers
67 views

Where can I read about the behavior of the postgress casting operators?

I want to do something like this: alter table foos alter column bar type bool using bar::boolean; bar is currently a text column. Some experimentation shows that postgres will do things like convert ...
John Bachir's user avatar
-4 votes
1 answer
35 views

can I be helped? my querey should = something like .000534 however I get 0

My query result should be 0.000534, but I'm getting 0. Please help me. psql -Upostgres -dcans -c \ 'select (select COUNT(*) FROM brd.gaf_info where ...
ciscoguy's user avatar
0 votes
1 answer
1k views

Performance issues with CAST function

Observing this performance issue that can't be explained with EXPLAIN in a PostgreSQL 9.6 database. There is currently a table public.users, which has a cast on it to output a record to JSONb. (e.g. ...
Guy Park's user avatar
  • 101
5 votes
2 answers
5k views

Why isn't it possible to cast to a timestamp in an index in PostgreSQL?

I am working with a JSON document which I receive from an external source. In this document, there is a timestamp property which corresponds to the textual format of a timestamp PostgreSQL data type. ...
basicsuperuser's user avatar
0 votes
1 answer
1k views

Postgres - how to find contents of one field within another field

How do I find where the contents of one field are within another field? I have a database table with a auto-generated sequence number in one field, and another character based field which should ...
ConanTheGerbil's user avatar
0 votes
1 answer
76 views

Two conversions of practically the same number giving two different results

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle... This Select convert(numeric(19,2),convert(float,9....
JohnG's user avatar
  • 1,081
2 votes
1 answer
819 views

Is TEXT to VARCHAR(MAX) an implicit conversion?

I am copying TEXT columns to another table where the destination column is VARCHAR(MAX). It appears that this is an implicit conversion and that I do not need to write an explicit CAST for it. Is ...
lit's user avatar
  • 231
0 votes
1 answer
585 views

Casting fraction number to decimal throws Arithmetic overflow error

I have a table called AssignmentMarks that stores Student's assignment marks for different subjects. This table has a column called Marks varchar(10) which stores the marks. The marks can be like 1, ...
Asrar Ahmad Ehsan's user avatar
0 votes
1 answer
2k views

PostgreSQL: What is the best way to cast from from text to point[]?

I'm looking for a way to cast from a text value: '(180,90),(-180,-90)' into a point[] value: {"(180,90)","(-180,-90)"} What is the best way to do this? One possibility that I can think of is to ...
Zach Smith's user avatar
  • 2,360
1 vote
1 answer
1k views

Convert Date and Filter by Year

I have a date field that is in a 'yyyymmdd' format, and I need to filter the query by the year, using that field. For example, in my table there is a "Posting Date" field that is in the '20180401' ...
Daylon Hunt's user avatar
0 votes
2 answers
858 views

SQL USING CAST in a query inside a stored Procedure

I was trying to use a query inside an Stored procedure which has a cast funtion. But when executing the SP I'm getting an error Conversion failed when converting date and/or time from character ...
swaroop's user avatar
0 votes
0 answers
472 views

Writing CSV directly to a BYTEA column from PostgreSQL tables

Assuming these two tables: CREATE TABLE data (day DATE, kpi INTEGER); CREATE TABLE reports (csv BYTEA); Is it possible to (pseudocode): INSERT INTO reports (csv) (SELECT to_csv(SELECT day, kpi FROM ...
crdb's user avatar
  • 1
3 votes
2 answers
471 views

UPDATE with CAST(meta->>"$.field") failing due to "null"

This issue is affecting MySQL 5.7.25. So, in a migration I'm moving legacy fields from the table into a JSON column. This part of the process works fine. But, the rollback is failing miserably! At ...
Robert K's user avatar
  • 131
0 votes
1 answer
9k views

Convert SQL Date MMDDYY varchar to Date?

In reading this, https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017 http://www.sqlusa.com/bestpractices/datetimeconversion/ I cannot locate ...
user avatar
2 votes
2 answers
3k views

How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause

How would I cast a column value (a "non-MySQL" DATE string retained as a VARCHAR) to a "MySQL date" so it can be used for sorting? I have dates in the format of 06-02-2019 (6 feb) but MySQL itself ...
Luc H's user avatar
  • 133
2 votes
2 answers
14k views

Cast as int in CASE expression in SQL

I have a query that is pulling data from 10 tables and a sub-query. One of the selects is for a location and I am using a case expression to clean up the data on output. The field it is pulling the ...
Rhonda's user avatar
  • 43
0 votes
1 answer
381 views

MS SQL -Date and Time Style conversion problem

I need to convert Date and Time Style from USA format to German and than cast that into int data type. So for variable in case below this works fine if I want to display it in Standard 112 (ISO) ...
Hrvoje's user avatar
  • 103