How to display name and type of table columns in creation order?
0
votes
2
answers
806
views
I have created this
I've also tried to order the columns, but the only parameter I know is
tbl_cities
using the code below:
CREATE TABLE IF NOT EXISTS public.tbl_Cities
(City_ID SERIAL NOT NULL,
City_Name VARCHAR(100) NOT NULL,
City_State_Region VARCHAR(100),
ID_Country INT NOT NULL,
CONSTRAINT PK_City_ID PRIMARY KEY (City_ID));
Now I want to query **the exact same order of this columns and their datatype,** in order to make sure, what are the column names and what is the real column order (without scrolling back to the CREATE TABLE
statement). In context of the INSERT
statements I'm about to write and execute.
To learn this info I execute:
SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'tbl_cities';
But the output I get is in different order, than the columns exist in reality.

ORDER BY column_name
. In this very case it results in the correct order in which the columns exist in reality.
SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'tbl_cities'
ORDER BY column_name;
My **question** is: What is the correct syntax to achieve the **real order in which the columns are in the table,** in case ordering them alphabetically would return different result/order.
I have found this question , but it concerns SQL Server and I need to do this on PostgreSQL v14.7.
**I'm open to different answers** using not only information_schema.columns
but also any kind of this other thing:
SELECT * FROM pg_catalog.pg_tables;
Asked by michal roesler
(125 rep)
May 1, 2023, 12:37 AM
Last activity: May 1, 2023, 03:48 AM
Last activity: May 1, 2023, 03:48 AM