Converts an expression of one data type to another.
Questions tagged [cast]
134 questions
36
votes
2 answers
Decode Base64 String Natively in SQL Server
I have a varchar column in a table in SQL Server that holds a Base64-encoded text string,
which I would like to decode into its plain text equivalent.
Does SQL Server have any native functionality to handle this type of thing?
Here is a sample…
GWR
- 2,847
- 9
- 35
- 42
29
votes
2 answers
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).
Now, supposed it contains two records:
1, { name:…
Golo Roden
- 422
- 1
- 5
- 9
28
votes
7 answers
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 a CASE expression or a coalesce to fall back on.…
Manngo
- 3,065
- 10
- 38
- 61
26
votes
1 answer
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;
select ctid
, id
from t;
ctid | id
:---- |…
Jack Douglas
- 40,517
- 16
- 106
- 178
23
votes
3 answers
Determine percentage from count() without 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 patients) * 100) as "Formula"
from…
user3779117
- 331
- 1
- 2
- 4
19
votes
4 answers
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 as text.
The projects table had a UUID field named…
Sig
- 455
- 1
- 5
- 14
17
votes
1 answer
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" = new_values."active","core" =…
ChrisJ
- 621
- 1
- 8
- 22
17
votes
2 answers
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 (Debian 4.9.2-10) 4.9.2, 64-bit:
CREATE TABLE…
Léo Léopold Hertz 준영
- 765
- 3
- 9
- 22
14
votes
1 answer
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 padding here? Why are there leading spaces?
Evan Carroll
- 65,432
- 50
- 254
- 507
11
votes
1 answer
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 type modifiers. We found surprising behavior.
1.…
Erwin Brandstetter
- 185,527
- 28
- 463
- 633
10
votes
1 answer
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 TYPE interface_types
USING…
Rob
- 311
- 1
- 2
- 8
10
votes
2 answers
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 Postgres doesn't have a built in operator for…
keithhackbarth
- 225
- 1
- 2
- 7
10
votes
2 answers
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 internally used in the system catalogs as a…
Evan Carroll
- 65,432
- 50
- 254
- 507
9
votes
2 answers
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 users(("user"->>'reputation')::int)
It works just…
Christoph
- 1,653
- 2
- 12
- 8
9
votes
1 answer
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 range) preserves more precision:
SELECT f8 …
Erwin Brandstetter
- 185,527
- 28
- 463
- 633