0

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.

Lexen
  • 13
  • 1
  • 2

2 Answers2

0

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

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:

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

Related:

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
0

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.

Lexen
  • 13
  • 1
  • 2