Questions tagged [cast]
Converts an expression of one data type to another.
121
questions
-1
votes
1
answer
20
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?
- 225
0
votes
2
answers
35
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;
...
- 59.4k
2
votes
1
answer
85
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 ...
- 209
1
vote
1
answer
113
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.
...
- 475
3
votes
1
answer
340
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....
- 251
0
votes
1
answer
156
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 ...
- 3
0
votes
2
answers
3k
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 ...
- 821
2
votes
1
answer
678
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 ...
- 23
1
vote
1
answer
309
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 ...
- 17
9
votes
1
answer
2k
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 ...
- 163k
1
vote
1
answer
387
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 (...
- 13
0
votes
1
answer
489
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
...
- 3,105
0
votes
2
answers
108
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)', '(?&...
- 622
0
votes
1
answer
3k
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 |
+-------...
- 101
0
votes
0
answers
409
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 ...
- 63
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 ...
- 115
6
votes
2
answers
3k
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 elem[...
- 183
6
votes
4
answers
7k
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:
...
- 23.2k
2
votes
1
answer
289
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]) ...
- 101
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 ('...
- 133
1
vote
1
answer
4k
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 ...
- 115
0
votes
1
answer
6k
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 ...
- 111
0
votes
1
answer
112
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
...
- 107
2
votes
2
answers
56
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 ...
- 615
-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 ...
- 1
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. ...
- 101
4
votes
2
answers
4k
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. ...
0
votes
1
answer
618
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 ...
- 983
0
votes
1
answer
72
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....
- 1,061
2
votes
1
answer
609
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 ...
- 221
0
votes
1
answer
444
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, ...
0
votes
1
answer
1k
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 ...
- 2,276
1
vote
1
answer
991
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' ...
- 189
0
votes
2
answers
628
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 ...
- 1
0
votes
0
answers
410
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 ...
- 1
3
votes
2
answers
432
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 ...
- 131
0
votes
1
answer
7k
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 ...
user172334
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 ...
- 133
2
votes
2
answers
11k
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 ...
- 43
0
votes
1
answer
285
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)
...
- 103
15
votes
1
answer
12k
views
Why do I need to cast NULL to column type?
I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this:
(Both the active and core fields are of type boolean)
UPDATE fields as t set "active" =...
- 531
14
votes
3
answers
39k
views
Casting an array of texts to an array of UUIDs
How can I cast an array of texts into an array of UUIDs?
I need to do a join between two tables: users and projects.
The users table has an array field named project_ids containing the project IDs ...
- 321
1
vote
0
answers
159
views
Used a CAST function ,still IMPLICIT Conversion warning in Execution Plan
I know that IMPLICIT Conversion warnings occur when the SQL server has to convert a value to a another datatype to do some processing.
I am getting this warning in an execution plan and when i checked ...
- 1,295
1
vote
0
answers
146
views
Is it possible to define a cast between text and a domain representing a uuid to avoid manual casts?
I created a DOMAIN as following:
CREATE DOMAIN country_id AS uuid;
I create such domains for readability. It is much easier to read function definitions which accept a country_id instead of a uuid.
...
- 319
6
votes
2
answers
2k
views
Is there a way to partially initialize a TYPE?
I have created a custom type:
CREATE TYPE my_type as(name text, street text, location text);
Initializing such a type is easy:
SELECT ROW('a', 'b', 'c')::my_type;
I am wondering if there is an ...
- 319
1
vote
0
answers
1k
views
Update BIT field with non-BIT value
I was trying (by a mistake) to update bit typed field with non-bit value. It took me some time to realize the cause for not getting this field updated (non-bit value). I was supprised that no native ...
- 13
2
votes
1
answer
62
views
C++ stoi equivalent in SQL Server's T-SQL
I'm looking for something I can use in Microsot SQL Server that would act the same way the stoi function in C++ acts. Specifically, I'm looking to be able to grab numerics at the beginning of the ...
- 23
4
votes
1
answer
190
views
How do I list available casts in SQL Server?
In PostgreSQL, there is a command, \dC+ I can use in psql \dC+,
\dC[+] [PATTERN] list casts
To lists the possible CASTS. This is useful, because I can easily find out what I can use to cast to ...
- 59.4k
0
votes
0
answers
19
views
Conversion Failed From nvarchar to int even when all values in result set are int [duplicate]
Take the following SQL sample:
Declare @Table Table (Value int)
Insert Into @Table
Select intValueAsVarcharType
From OtherTable
Where Category = 'Type1'
The OtherTable.intValueAsVarcharType ...
- 55
4
votes
3
answers
6k
views
Can MariaDB or MySQL cast as bigint, getting an error: ... right syntax to use near 'bigint)'?
If I want to take a string and create a table (this fails in strict mode),
CREATE TABLE g
AS SELECT CAST('2147483699' AS int);
How do I tell it that I want a bigint type? I'll also get an int(10) ...
- 59.4k