Questions tagged [cast]

Converts an expression of one data type to another.

Filter by
Sorted by
Tagged with
17
votes
3answers
27k views

PostgreSQL using count() to determine percentages (cast issues)

I'm trying to run the following query to provide the % of rows in my patients table that have a value the refinst column. I keep getting a result of 0. select (count (refinst) / (select count(*) from ...
14
votes
1answer
10k views

How do I decompose ctid into page and row numbers?

Each row in a table has a system column ctid of type tid that represents the physical location of the row: create table t(id serial); insert into t default values; insert into t default values; ...
11
votes
1answer
26k views

Decode Base64 String Natively in SQL Server

I have a varchar column in a table in SQL Server which hold a base64-encoded text string which I would like to decode into it's plain text equivalent Does SQL Server have any native functionality to ...
11
votes
1answer
1k views

Surprising results for data types with type modifier

While discussing a recursive CTE solution for this question: Get the last 5 distinct values for each ID @ypercube stumbled across a surprising exception, which lead us to investigate the handling of ...
10
votes
1answer
12k views

Querying JSONB in PostgreSQL

I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support). ...
9
votes
1answer
654 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" =...
8
votes
2answers
219 views

Why does this seek on BIGINT col have extra constant scan, compute scalar, and nested loops operators?

When I look at the actual exection plan of some of my queries I notice that literal constants used in a WHERE clause show up as a nested chain of calculate scalar and constant scan. To reproduce this,...
8
votes
2answers
1k views

How exactly does the one-byte “char” type work in PostgreSQL?

I often see people talking about "char". I've never used it. It's defined in the docs as, The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is ...
7
votes
3answers
16k views

Inserting date from string: CAST vs CONVERT

Consider the following two ways to convert a datetime varchar string into a date field: SELECT convert(date, '2012-12-21 21:12:00', 20) -- Only date is needed SELECT cast('2012-12-21 21:12:00' as ...
7
votes
1answer
6k views

How to create an index on an integer json property in postgres

I can't figure out for the life of me how to create an index on a property of my json column which is an integer. I tried it this way (and also dozens of others) CREATE INDEX user_reputation_idx ON ...
6
votes
1answer
13k views

PostgreSQL custom operator UUID to varchar

I have a rather complicated Postgres database in which many UUID fields were incorrect stored as VARCHAR. I'd like to migrate them over in piecemeal, but unfortunately, doing so breaks all my views as ...
6
votes
3answers
13k views

changing float->numeric casts from assignment to implicit, dangerous?

In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding ...
6
votes
2answers
291 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 ...
6
votes
2answers
1k views

How to create INOUT assignment cast on Amazon RDS PostgreSQL? If not possible, then why?

I'm migrating existing application to Amazon RDS PostgreSQL. The application has cross-database support and for compatibility reasons it includes the following SQL: CREATE CAST (varchar AS numeric) ...
5
votes
2answers
1k views

How can I display a mysql bit(64) column as a string of ones and zeros?

We have a MySQL 5.5 database with a table that holds a number of 64-bit values which are used for calculating the hamming distance. The columns for these are defined as BIT(64) and are populated ...
5
votes
2answers
1k views

Is it possible to change the default style used by CAST/CONVERT when converting DATETIME? [closed]

When you attempt to CAST a DATETIME as VARCHAR in SQL Server, the default date formatting style is a fairly inaccurate date format that loses the precision of both seconds and milliseconds. For ...
5
votes
2answers
660 views

Can you use an inserted variable in a default constraint

Working in SQL Server, let's say I have a database with two tables: events and competitors. events has the following columns: [event_id] [event_name] [winner_id] [loser_id] competitors has the ...
5
votes
1answer
4k views

Why does implicit conversion from sql_variant (basetype decimal) not work well with float

Why is ResultFloat = 0 in the query below? Am I missing a point within the cast/convert, sql_variant docs? declare @v sql_variant = convert(decimal(28,8), 20.0); select sql_variant_property(@...
5
votes
4answers
1k views

PostgreSQL alternative to SQL Server’s `try_cast` function

Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error. This makes it possible to then use ...
5
votes
1answer
3k views

In Postgres, how do I cast character varying to an enum type?

I have a Postgres 8.4 table with columns that are of type "character varying", but I want to convert them to an enum type. When trying to alter the table ALTER TABLE logs ALTER COLUMN interface_type ...
4
votes
2answers
10k views

Is index performance on a TIMESTAMP(6) column impacted when converting it to a number

If I have an index on a TIMESTAMP(6) column, am I forced to use this column as a TIMESTAMP(6) to get the benefits of the index? Can I convert this column to a number with second precision (...
4
votes
1answer
2k views

Why is to_char left padding with spaces?

When ever I use 099 indicating 3 digits 0-padded, I'm getting spaces on the left side. SELECT '>' || to_char(1, '099') || '<'; ?column? ---------- > 001< (1 row) Why is to_char left ...
4
votes
1answer
175 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 ...
4
votes
1answer
3k views

what are the possible reasons for 'Arithmetic overflow error converting expression to data type int' in SQL server

When this command is run, there is SQL server error 'Arithmetic overflow error converting expression to data type int'. SELECT sum(size) FROM [dbname]..sysfiles sf, [dbname]..sysfilegroups sfg ...
4
votes
1answer
3k views

How to enable implicit casts in PostgreSQL >=9.2?

Coming from this topic on SO a user suggest me to enable implicit casts in my PostgreSQL and I don't know how to do that. I also don't know if this is a good practice or if it's the solution to the ...
3
votes
2answers
10k views

Error converting NVARCHAR to BIGINT when in SUBQUERY with WHERE clause after invalid records filtered [duplicate]

I have a table that has an NVARCHAR column that contains data that cannot be converted to BIGINT. I'm well aware of that and have filtered it out using ISNUMERIC(BB.NVARCHARCOL) = 1. Despite this, I ...
3
votes
3answers
996 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) ...
3
votes
3answers
3k views

float to varchar with no trailing zeros

I am having a heck of a time trying to convert a FLOAT(53) field to a VARCHAR where there are no trailing zeros or scientific notation. The field could have a number that is really big or really ...
3
votes
1answer
934 views

Define calculated column of BOOLEAN/TINYINT(1) type in a View? [duplicate]

I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view: CREATE VIEW `ItemView` AS SELECT Item.ID ,...
3
votes
1answer
7k views

Handling sql column with mixed varchar and float data types

We have some Excel spreadsheets with parcel numbers for properties that should be in a standard varchar format similar to: [Parcel # 1] [Building Address] 038-60-171-16-000 1750 W 4th St ...
3
votes
1answer
11k views

How to convert time without time zone to timestamp without time zone in PostgreSQL?

I received this error when trying to alter a column of type time to type timestamp: PG::CannotCoerce: ERROR: cannot cast type time without time zone to timestamp without time zone It's not ...
3
votes
2answers
4k 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 ...
3
votes
1answer
466 views

PostgreSQL CAST() giving unexpected results

I can't seem to figure out how this is rounding overflow digits. It is most definitely not any of: Round up if last digit is 5 or higher. Round to even if last digit is 5. Randomly round up or down ...
2
votes
3answers
639 views

How do I cast varchar to macaddr without changing client code

I have a dedicated DB Server with Debian Jessie and Postgresql 9.4 Client: A is a Tomcat Java app which use torque with a JDBC driver connecting to the DB Server. Issue: on Updating a row with a new ...
2
votes
2answers
30k views

How to cast to int array in Postgresql?

I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code. Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (...
2
votes
2answers
579 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 ...
2
votes
4answers
8k views

Format value to configured number of decimal places

I want to format a number to display decimal places according to a configuration value specified in another table. If the configured value is NULL, the number of decimal places should default to 2 (...
2
votes
3answers
8k views

SQL Server: CONVERT From ISO8601 To DateTime Within Query

I have a Microsoft SQL Server 2008 R2 database where the DateTime column is not a datetime data type, but varchar(50). Our website logs that data and I don't really have control over it (I'm sure a ...
2
votes
1answer
46 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 ...
2
votes
1answer
771 views

What does SELECT ~0; do?

I've recently stumbled into an interesting Mysql query: SELECT ~0; results: 18446744073709551615 Which is the max value of a bigint unsigned. What does the tilde operator do in this case?
2
votes
1answer
504 views

PostgreSQL Full Text Search (FTS), what is the difference between ::tsvector and to_tsvector()?

i have a table with tsvector type column (col). I fill some rows via script and use cast data::tsvector, but (!) some rows was filled with to_tsvector(data), so i have rows with different values like ...
2
votes
2answers
798 views

In Postgres, is it possible to get all rows from a table where a column can be cast to a different type?

I have a table in a Postgres DB that has a TEXT column, named data, that I use to store both IP addresses and domain names. I'm wondering if it's possible to select all the rows in the table whose ...
2
votes
2answers
3k views

Remove scientific notation from an XML column

I have a XML column as shown below: <Root> <Row> <Rowid>1</Rowid> <date>2013-05-06</date> <Balance>1.0002E7</Balance> </Row> </...
2
votes
2answers
4k views

Getting “Conversion failed when converting the nvarchar value to data type int.”

I have a SQL Server table pairing zipcodes and lat/long geometry, such as below ID ZIP5 geom 1 356HH 0xE610000001044E00....FFFFFFFF0000000003ID 2 35677 0xE6100000010404000000068....000003 ...
2
votes
1answer
944 views

PostgreSQL parses string literal as record before calling cast

In my PostgreSQL 9.6 database, I have a composite type, let's call that type widget. I have a PL/pgSQL* function called text_to_widget that parses textual representations of widgets to widget values, ...
2
votes
1answer
37 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 ...
2
votes
1answer
98 views

Ranges over domains do not trigger implicit casting

As far as I was able to find out, it is currently not possible in PostgreSQL v9.5 (and above?) to obtain implicit casting with custom range types. To illustrate, when I define the following demo setup:...
2
votes
1answer
244 views

How does cast work with Set Returning Functions (SRF) like generate_series?

I got to wondering about how we would best use generate_series if we only want a series of dates. As a note, generate_series returns setof timestamp or setof timestamp with time zone (same as argument ...
2
votes
1answer
430 views

Error updating zoned field from SQL Server stored procedure linked server to iSeries

I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned. In my stored ...
2
votes
2answers
53 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 ...