Literal Values, Data Types, and Type Casting in SQL

Every value in SQL has a type, and that type determines what SQL can do with it.

When you write 48000 * 0.075, SQL already knows what it's working with: both values are numeric, so the multiplication works. But sometimes a value arrives as the wrong type. A date stored as text. A price stored as a string. SQL won't treat those as numbers or dates on its own. You have to tell it what type a value really is. The :: operator handles this — it's called a cast.

Think of :: as saying "treat this as." You put it after the value, then write the target type. A text string like '2025-06-30' is just characters until you add ::date:

Now SQL treats it as a real date. The ::date cast is what makes that switch.

You can cast between many types. Text to number: '199.99'::numeric. Number to text: 42::text. These come up regularly when data is imported from spreadsheets or external systems where everything lands as a string.

SQL also has a second casting syntax that does the same thing:

:: is shorter and more common in PostgreSQL. CAST() is standard SQL and appears in other databases. Both produce the same result.

SQL also has type-prefixed literals for some types. An interval — a duration of time — is written as INTERVAL '30 days'. The type name comes before the quoted value instead of after it.

The one thing that trips people up: integer division drops the decimal.

Divide an integer by an integer and SQL returns an integer, cutting off anything after the decimal point. '10000'::integer / 4 returns 2500, not 2500.0. Cast to ::numeric first and you get the full decimal result. The same fix works whenever you need the fractional part of a division: make at least one side a decimal type.