I want to know command like:

ALTER TABLE table_name ALTER COLUMN column_name ADD ALIAS new_name

and after then I execute query like

SELECT column_name FROM table_name 

or

SELECT * FROM table_name

I want to get column name like new_name

I didn't find any resolve solution without SELECT column_name AS new_name, so I want it

I want to know command like:

ALTER TABLE table_name ALTER COLUMN column_name ADD ALIAS new_name

and after then I execute query like

SELECT column_name FROM table_name 

or

SELECT * FROM table_name

I want to get column name like new_name

I didn't find any resolve solution without SELECT column_name AS new_name, so I want it

• sql
• postgresql

• 1 There isn't such a feature in (Postgre)SQL. – ruohola Commented Feb 4 at 7:44
• 2 Agreed. This is not possble in SQL. And it would be very confusing did such feature exist, where you'd work with one column name in the query but get a different column name in the result. – Thorsten Kettner Commented Feb 4 at 7:52
• It may be not confusing if it's have command like 'SELECT * FROM table_name WITH LABEL NAMES' if I want new_name and 'SELECT * FROM table_name' if I want column_name – ixoroturg Commented Feb 14 at 12:25

3 Answers 3

You can add a generated column to your table. https://www.postgresql./docs/current/ddl-generated-columns.html

ALTER TABLE table_name ADD new_name int GENERATED ALWAYS AS (column_name) STORED

Specify the same data type for new_name as the original column_name's.

I'd assume this can slow down performance a bit when doing table updates.

Demo at: https://dbfiddle.uk/D_Qq79T9 (Based on @d r's fiddle, thanks!)

That is not possible the way you asked for. Not sure what would be the reason for such an action. Maybe you could consider creating a view with altered (aliased) column names and then do the select from the view instead from the table.
You will still have to use col_name as alias syntax when creating the view, though.

Create Table tbl ( col_1 Int, col_2 Varchar(12) );

Insert Into tbl
Values( 1, 'A' ),
      ( 2, 'B' ),
      ( 3, 'C' );

Select * From tbl;

Create View tbl_v As 
  Select col_1 as c1_alias, col_2 as c2_alias
  From tbl;

Select * From tbl_v;

fiddle

You can RENAME it:

ALTER TABLE table_name RENAME COLUMN column_name TO new_name;

That's assuming this:

I want to get column name like new_name

means you want to see new_name instead of column_name from that point onwards.

If you want to see both at once and need the option to use either name to get the same value from that table, you need a generated column or a view as already shown by @jarlh and @d r.

You can also use a rule but I wouldn't recommend them here, especially since views are implemented using rules so it's safer to just use a view and let Postgres handle the rules under the hood.