Questions tagged [cast]

Converts an expression of one data type to another.

Filter by
Sorted by
Tagged with
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; ...
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 ...
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 ...
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 ...
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 ...
2
votes
1answer
97 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:...
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 ...
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
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 ...
2
votes
1answer
937 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, ...
1
vote
1answer
448 views

Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

I experienced very strange problem today with TSQL function. The function has several parameters (int and bit). One of the (int) is set with value NULL. When calling a function with NULL inside the ...
0
votes
1answer
21 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 ...
-1
votes
1answer
2k views

postgresql function CAST json ARRAY to A TYPE

I try to cast a JSON to a TYPE VOID_EVENT_CREATE, but I didn't work because of the array. I'm going to use the query with NodeJS that why I would like to cast the JSON to a special type. It's easier ...