Menu
Is there a way to easily get the column types of a query result? I read the psql documentation, but I don't think it supports that. Ideally, I'd be able to get something like:
columna : text | columnb : integer ----------------+------------------- oh hai | 42 Is there a way I can get this information without coding something up?
398
SELECT * FROM information_schema. columns WHERE table_schema = 'your_schema' AND table_name = 'your_table' ; Note: As per the example above, make sure the values are enclosed within quotes.
PostgreSQL describe table is defined as check the structure of table, we can describe the structure of table by using \d and table name command in PostgreSQL. In PostgreSQL describe table statement is not present like MySQL instead of describe we have using \d table name and \d+ table name.
First, specify the name of the table to which the column you want to change belongs in the ALTER TABLE clause. Second, give the name of column whose data type will be changed in the ALTER COLUMN clause. Third, provide the new data type for the column after the TYPE keyword.
It is possible to get any SELECT query result column type.
Example
Given the following query and result, let's answer the question *"What is the column type of all_ids?"*
SELECT array_agg(distinct "id") "all_ids" FROM "auth_user"; all_ids -------------------------------------------- {30,461577687337538580,471090357619135524} (1 row) We need a mechanism to unveil the type of "all_ids".
On the postgres mailing list archives I found reference to a native pg function called pg_typeof.
Example usage:
SELECT pg_typeof(array_agg(distinct "id")) "all_ids" FROM "auth_user"; Output:
all_ids ---------- bigint[] (1 row) Cheers!
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
