Sample Header Ad - 728x90

How to display name and type of table columns in creation order?

0 votes
2 answers
806 views
I have created this 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. enter image description here I've also tried to order the columns, but the only parameter I know is 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