how to check for user defined tables in postgres?

Question

apart from checking if table already exists, how can we verify if it is current user-defined table in postgres

CREATE TABLE IF NOT EXISTS tbl1()

how can we verify it was created by current user if already exists?

Sathish · Accepted Answer · 2014-03-25 07:24:01Z

2

Try Like This

 SELECT count(*) as cnt FROM pg_tables t where tableowner=current_user 
and tablename='Tablename'  and schemaname='schemaneme'

if cnt = 0 then (CREATE Table TableName)

answered Mar 25, 2014 at 7:24

Sathish

4,4854 gold badges36 silver badges60 bronze badges

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

4 Comments

RAFIQ Over a year ago

thanks for reply, it works with pg_tales and current_user. Can we use it for multiple tables, if we have hundred tables in one script by having table list or we have to wrap every table with it?

Sathish Over a year ago

give the table name in where clause Use In get the tables name and below check the condition using for loop

RAFIQ Over a year ago

sorry i could not get you, could you explain again?

Sathish Over a year ago

tablename='Tablename' change to tablename in (tablenames). count(*)` Change to tablename into rec. Rec as record. now use for loop for create table

Collectives™ on Stack Overflow

how to check for user defined tables in postgres?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related