How to check for column attributes in postgreSQL?

Question

I am new to SQL. Let say that when someone created a table like this in database named test1 of PostgreSQL:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIME,
    contents VARCHAR(240) NOT NULL);

I make a query to the database and do not know how data in columns are stored. Is there a query to return the stored definition of the columns in the users table or a method to check this ?

For example: The result should return something like:

id column: SERIAL PRIMARY KEY
created_at: TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIME
...

Note that in modern Postgres versions, identity columns are recommended to be used over serial — user330315
– user330315, Commented Oct 7, 2021 at 11:58

Kurt Kline · Accepted Answer · 2021-10-07 11:56:16Z

1

You can use information_schema for this

select * 
from information_schema.columns 
where table_name = 'users'

answered Oct 7, 2021 at 11:56

Kurt Kline

2,1291 gold badge15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ken Masters Over a year ago

Thanks, mate. Your answer is absolutely what I need.

Collectives™ on Stack Overflow

How to check for column attributes in postgreSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related