PostgreSQL: Issue Declaring Variables in Query

Question

I'm trying to declare a variable for later use in a PostgreSQL query. I've done this often in TSQL, but I wasn't sure about the syntax.

The stuff I've seen online all points to something like this:

declare timestamp_utc timestamp := current_timestamp;
select start_date;

when I run the above query, I get an error message:

ERROR: syntax error at or near "timestamp"

I'm sure this is simple, but I just can't find the answer online. Any help you could provide would be greatly appreciated.

• mustaccio: postgresql.org/docs/current/sql-do.html

Answer 1

There are no variables in plain SQL.
You can use "customized options" as limited workaround. See:

• Declaring variable for a whole script

Server-side procedural languages like PL/pgSQL have variables. You can use DECLARE in a code block - in functions, procedures and in anonymous code blocks executed with the DO command. PL/pgSQL is the default PL for the latter. See:

• How to use variables in procedure (not function)?

Or you can set variables in the client, like the default command-line terminal psql. See:

• PSQL passing external variables
• How to pass variable to PL/pgSQL code from the command line?

Related:

• Set custom constant in Postgres script

• Lexen: This was very helpful. Thanks!

Answer 2

As Erwin pointed out, you can't declare variables in vanilla PostgreSQL. The way I got around this was by declaring a temp table and inserting the values I needed in there.

drop table if exists variables;
create temp table variables (
 timestamp_utc timestamp 
);
insert into variables select current_timestamp;
select timestamp_utc from variables;
drop table if exists variables;

It's a lot more code, but it handles the use case I'm working with.

• Vérace: Put all of that inside a transaction!