தொகுதி 6 · பாடம் 4 இன் 4பாடத்திட்டத்தில் 24/57~10 min

தொகுதி பாடங்கள் (4/4)

CAST மற்றும் வகை மாற்றங்கள்

SQL is strongly typed: every column has a type (INTEGER, TEXT, NUMERIC, DATE, TIMESTAMP…), and many operations require the types to match. When they don't — or when you want to format a value differently — you use a cast.

Two equivalent syntaxes

SQL

-- SQL standard:
CAST(123 AS TEXT)

-- PostgreSQL shorthand (più comune nella pratica):
123::text

The two forms are identical. The shorthand ::type is more compact and chains nicely with other expressions:

SQL

SELECT ordered_at::date AS day,
       (oi.quantity * oi.unit_price)::numeric(10,2) AS total,
       id::text || ' - ' || email AS label
FROM   …;

Useful casts on the dataset

SQL

TIMESTAMP → DATE          ordered_at::date
NUMERIC   → INTEGER       price::int           -- tronca!
INTEGER   → NUMERIC       quantity::numeric    -- per evitare div. intera
INTEGER   → TEXT          id::text             -- per concatenare con ||
TEXT      → DATE          '2024-03-05'::date
NUMERIC   → TEXT          price::text          -- per LIKE su numeri

When a cast can fail

SQL

SELECT 'abc'::int;   -- ERRORE: invalid input syntax for type integer
SELECT '12.5'::int;  -- ERRORE: invalid input syntax for type integer
SELECT 12.5::int;    -- 12   (cast numerico-numerico: arrotonda)

Casts from string → number or string → date are the riskiest: they raise an error if the string does not exactly match the expected format. In production, on external data, it is best to validate first with TRIM / LOWER / regex.

Cast and ORDER BY

A case that generates subtle bugs: sorting numbers stored as text.

SQL

SELECT '2', '10', '1' ORDER BY 1;  -- '1', '10', '2'   (alfabetico!)

Solution: cast inside ORDER BY:

SQL

SELECT code FROM articles ORDER BY code::int;

Try it

உடற்பயிற்சி#sql.m6.l4.e1

முயற்சிகள்: 0ஏற்றுகிறது…

For each order, show id and day (only the date part of ordered_at, of type date). Two columns. Order by id.

எடிட்டரை ஏற்றுகிறது…

குறிப்பைக் காட்டு

The TIMESTAMP → DATE cast drops the time: ordered_at::date.

3 முயற்சிகளுக்குப் பிறகு தீர்வு கிடைக்கும்

Review exercise

உடற்பயிற்சி#sql.m6.l4.e2