CODEWITHSUNDEEP
postgresqlpsql
amccormack
amccormack

Reputation: 13927

Postgres table alias for psql session

I can alias a table name in a Postgres statement like this:

SELECT a.id FROM very_long_table_name AS a;

Is there a mechanism to set up a similar alias that persists for a psql session?

For example:

$: psql -d sample
sample=# CREATE ALIAS a for very_long_table_name;
sample=# select id from a limit 1;
 id 
____
  1

Upvotes: 2

Views: 417

Answers (3)

Laurenz Albe
Laurenz Albe

Reputation: 247235

I think the best option is to create a temporary view.

This solution is not restricted to psql.

CREATE TABLE averylongname (id integer PRIMARY KEY);
INSERT INTO averylongname VALUES (1);

CREATE TEMPORARY VIEW x AS SELECT * FROM averylongname;

The view will automatically vanish when your database session ends, and it can be used with DML statements as well:

INSERT INTO x VALUES (2);
SELECT * FROM x;

 id 
----
  1
  2
(2 rows)

Upvotes: 1

user330315
user330315

Reputation:

As shown in the manual this can be done using psql variables:

sample=# \set a 'very_long_table_name'
sample=# select id from :a limit 1;
 id 
----
   1
(1 row)

If you don't want to run \set every time manually, you can include your common short names in ~/.psqlrc which is read when you start psql

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522050

I don't know of a way to create such an alias, but you may create a view on top of your table, and give it a short name, e.g.

CREATE VIEW short_name AS
SELECT *
FROM very_long_table_name;

Then, use the view name as you would the alias. Since views generally perform as well as the underlying tables, with regard to indices, you should not lose much in terms of performance.

Upvotes: 1

Related Questions