0

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;
1

2 Answers 2

3

In Postgres, catalog tables are the true source of information. For this case in particular: pg_attribute. The column attnum represents the physical order (= logical order) of columns:

SELECT attname AS column_name
--   , atttypid::regtype AS data_type                       -- simple
     , format_type(atttypid, atttypmod) AS exact_data_type  -- exact
FROM   pg_attribute
WHERE  attrelid = 'public.tbl_cities'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

The manual:

attnum int2

The number of the column. Ordinary columns are numbered from 1 up. System columns, such as ctid, have (arbitrary) negative numbers.

The information schema is a standardized view on the same. But some details like exact data type are much easier to extract from pg_attribute. And faster.

I use the "system catalog information function" format_type() to get the exact data type, which includes modifiers and also works for array types and domains. You'll want that. Think of varchar(25)[] ...

Producing the same from information_schema.columns is not as simple (or fast). The manual about the column information_schema.columns.data_type:

Data type of the column, if it is a built-in type, or ARRAY if it is some array (in that case, see the view element_types), else USER-DEFINED (in that case, the type is identified in udt_name and associated columns). If the column is based on a domain, this column refers to the type underlying the domain (and the domain is identified in domain_name and associated columns).

See:

2
  • Where can I read more about what is pg_catalogue and pg_attribute and differences between them?? Commented May 1, 2023 at 10:55
  • @michalroesler Start with the links to the manual and more elaborate answers I provided. pg_catalogue is the schema where system tables like pg_attribute live. Commented May 1, 2023 at 11:40
1

I'd prefer to use the information_schema for a question like this:

SELECT ordinal_position, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'tbl_cities'
ORDER BY ordinal_position;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.