PostgreSQL handles variables differently from SQL Server. Here are the main approaches...
---
title: 'SQL Server vs PostgreSQL: Working with Variables'
subtitle: PostgreSQL handles variables differently from SQL Server.
author: Jon LaBelle
date: March 24, 2025
source: https://jonlabelle.com/snippets/view/markdown/sql-server-vs-postgresql-working-with-variables
notoc: false
---
## Using Variables in PostgreSQL
PostgreSQL handles variables differently from SQL Server. Here are the main approaches:
### 1. PL/pgSQL Blocks with DECLARE
```sql
DO $$
DECLARE
customer_id INT := 123;
order_date DATE := CURRENT_DATE;
BEGIN
-- Use variables in queries
INSERT INTO orders (customer_id, order_date)
VALUES (customer_id, order_date);
-- Can also select into variables
SELECT COUNT(*) INTO order_count
FROM orders
WHERE customer_id = customer_id;
END $$;
```
### 2. Functions with Variables
```sql
CREATE OR REPLACE FUNCTION get_recent_orders(customer_id INT)
RETURNS TABLE (order_id INT, order_date DATE) AS $$
DECLARE
cutoff_date DATE := CURRENT_DATE - INTERVAL '30 days';
BEGIN
RETURN QUERY
SELECT o.id, o.order_date
FROM orders o
WHERE o.customer_id = customer_id
AND o.order_date >= cutoff_date;
END;
$$ LANGUAGE plpgsql;
-- Call with: SELECT * FROM get_recent_orders(123);
```
### 3. Common Table Expressions (WITH)
```sql
WITH params AS (
SELECT
123 AS customer_id,
CURRENT_DATE - INTERVAL '30 days' AS start_date
)
SELECT o.*
FROM orders o, params p
WHERE o.customer_id = p.customer_id
AND o.order_date >= p.start_date;
```
### 4. Prepared Statements
```sql
-- Create a prepared statement with placeholders
PREPARE order_query(INT, DATE) AS
SELECT * FROM orders WHERE customer_id = $1 AND order_date >= $2;
-- Execute with specific values
EXECUTE order_query(123, '2025-01-01');
-- When done
DEALLOCATE order_query;
```
Unlike SQL Server's `@variable` syntax, PostgreSQL uses named variables in procedural blocks and positional parameters (`$1`, `$2`) in prepared statements.
## Declaring Multiple Variables in PostgreSQL
Variable declarations always go between the `DECLARE` and `BEGIN` keywords, with each variable on its own line ending with a semicolon.
### In PL/pgSQL Blocks (DO blocks or functions)
You can declare any number of variables in the DECLARE section, with each variable on its own line ending with a semicolon:
```sql
DO $$
DECLARE
customer_id INT := 123;
order_date DATE := CURRENT_DATE;
total_amount DECIMAL(10, 2) := 0;
shipping_address TEXT := 'New York';
is_priority BOOLEAN := TRUE;
BEGIN
-- Use variables here
INSERT INTO orders (customer_id, order_date, amount, address, priority)
VALUES (customer_id, order_date, total_amount, shipping_address, is_priority);
END $$;
```
### In Functions
```sql
CREATE OR REPLACE FUNCTION process_order(p_order_id INT)
RETURNS VOID AS $$
DECLARE
v_customer_id INT;
v_order_date DATE;
v_total_amount DECIMAL(10, 2) := 0;
v_items_count INT;
v_is_completed BOOLEAN := FALSE;
BEGIN
-- Function body using the variables
SELECT customer_id, order_date INTO v_customer_id, v_order_date
FROM orders
WHERE order_id = p_order_id;
-- More processing...
END;
$$ LANGUAGE plpgsql;
```
## PostgreSQL Data Types for Variables
PostgreSQL is very flexible with types, supporting everything from simple primitives to complex structures like arrays, JSON, and geometric types.
Here are the most common ones:
### Numeric Types
```sql
DO $$
DECLARE
id INT := 1; -- Integer
small_num SMALLINT := 100; -- Small integer
big_num BIGINT := 9223372036854775807; -- Large integer
price DECIMAL(10,2) := 99.99; -- Exact decimal with precision
amount NUMERIC := 123.45; -- Exact numeric
distance REAL := 123.456; -- Floating point
scientific DOUBLE PRECISION := 1.23e10; -- Double precision floating point
BEGIN
-- Use variables here
END $$;
```
### String Types
```sql
DO $$
DECLARE
code CHAR(3) := 'ABC'; -- Fixed-length, padded with spaces
name VARCHAR(100) := 'John Smith'; -- Variable-length with limit
description TEXT := 'Long text...'; -- Variable unlimited length
BEGIN
-- Use variables here
END $$;
```
### Date/Time Types
```sql
DO $$
DECLARE
current_date DATE := CURRENT_DATE;
meeting_time TIME := '14:30:00';
created_at TIMESTAMP := '2025-03-24 12:30:45';
created_at_tz TIMESTAMPTZ := '2025-03-24 12:30:45-07';
duration INTERVAL := '2 hours 30 minutes';
BEGIN
-- Use variables here
END $$;
```
### Other Common Types
```sql
DO $$
DECLARE
is_active BOOLEAN := TRUE;
uuid_val UUID := 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
json_data JSONB := '{"name": "John", "age": 30}';
ip_address INET := '192.168.0.1';
numbers INT[] := ARRAY[1,2,3,4,5]; -- Array type
BEGIN
-- Use variables here
END $$;
```
### User-Defined Types
You can also use enum types, composite types, and domain types:
```sql
-- Enum example (create type first)
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
DO $$
DECLARE
current_mood mood := 'happy';
BEGIN
-- Use variables here
END $$;
```