PostgreSQL - How to cast dynamically?

Question

I have a column that has the type of the dataset in text.

So I want to do something like this:

SELECT CAST ('100' AS %INTEGER%);
SELECT CAST (100 AS %TEXT%);

SELECT CAST ('100' AS (SELECT type FROM dataset_types WHERE id = 2));

Is that possible with PostgreSQL?

I currently don't know if this is possible. I'm curious though how do you define WHERE id = 2 I mean, if you know which data type to look means you already know the data type right ? — Jorge Campos
– Jorge Campos, Commented Apr 10, 2019 at 18:23
And, this may help you out: stackoverflow.com/questions/3827828/… — Jorge Campos
– Jorge Campos, Commented Apr 10, 2019 at 18:25
This is not really possible -- unless you use dynamic SQL. The type of a column is fixed in the SELECT; it is not variable. — Gordon Linoff
– Gordon Linoff, Commented Apr 10, 2019 at 18:40
No, it is not possible. The syntax parser of PostgreSQL denies any dynamic element here. Even if it would be possible, you'd have to make sure that conversion between the source and target type is actually possible. Is there a concrete reason you think you'd need the database to do that instead of the application? — Ancoron
– Ancoron, Commented Apr 10, 2019 at 19:21

Erwin Brandstetter · Accepted Answer · 2019-04-10 19:26:47Z

3

SQL is strongly typed and static. Postgres demands to know the number of columns and their data type a the time of the call. So you need dynamic SQL in one of the procedural language extensions for this. And then you still face the obstacle that functions (necessarily) have a fixed return type. Related:

Or you go with a two-step flow. First concatenate the query string (with another SELECT query). Then execute the generated query string. Two round trips to the server.

SELECT '100::' || type FROM dataset_types WHERE id = 2; -- record resulting string
Execute the result. (And make sure you didn't open any vectors for SQL injection!)

About the short cast syntax:

Postgres data type cast

answered Apr 10, 2019 at 19:26

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - How to cast dynamically?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related