PostgreSQL primary key

joseluis

I need to obtain the names of the fields that are the primary key of PostreSQL tables, but the only thing i find that is likely is pg_meta_data.

Sxooter

The easiest way to discover such things is to crank up psql with the -E switch, and run the correct \ command and watch the queries fly by. For instance, this query gives the indexes:

SELECT c2.relname, i.indisprimary, i.indisunique, pg_catalog.pg_get_indexdef(i.indexrelid)
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
WHERE c.oid = '304357324' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname

csn

Pretty interesting - are some of the internal tables "hidden" or something? When I do:

db> select * from pg_t[tab][tab]

'pg_toast.' shows up in the auto-completion, but this:

db> select * from pg_to[tab][tab]

returns nothing. Auto-completion works for 'pg_catalog.'.

update - same story with 'pg_temp_1.'.

Sxooter

pg_temp_1 should be a schema, not a table, but they both sorta kinda show up in tab completion. You may have just found some of the tab completion bugs that were more common in older versions. I'm sure there are still some things no one's thought to add to the parser's tab completion capability.

What version of pgsql are you running, by the way? I'm using 7.4.7, 7.4.8 and 8.0.3. They seem fixed in 7.4.7 and above.

csn

I'm using version 8.0.2.

Sxooter

Hiow odd. Sure there wasn't something else similarly named and you just didn't get the second tab in or something?

hmmmmmm.

csn

Quite sure 😉. Check this out:

=> select * from pg_toast.pg_toast_22460;
ERROR:  permission denied for schema pg_toast

Looks like it involves permissions too. I think my user has all the admin privileges. I connected as postgres and got:

select * from pg_toast.pg_toast_22460;
ERROR:  relation "pg_toast.pg_toast_22460" does not exist

Auto-completion still returns the same things while connected as user 'postgres'.

Sxooter

Actually, that's normal operation. No reason to include things in autocomplete you can't access, so they don't show up in the tab listing.

The second error makes it seem like you might have connected to a different db instance on the same server...

csn

Originally posted by Sxooter
The second error makes it seem like you might have connected to a different db instance on the same server...

Different db instance? Is that more than just transactions, set variables, etc.? I just did =>\c db postgres.

Here's about all I could dig up on pg_toast and pg_temp*:

Christopher Kings-Lynne <chriskl ( at ) familyhealth ( dot ) com ( dot ) au> writes:

What objects can possibly appear in the pg_temp* schemas? Is it just
tables, views and sequences?

Indexes ... row types for tables ...

As above, but the pg_toast schema. Tables only here?

Ditto.

Am I guaranteed that a temp schema is 'pg_temp_*' and a toast one is
'pg_toast' and vice versa?

Yes.

What about information_schema? Is it always just going to be tables,
views, functions and domains?

Wouldn't count on it.

Can people create weird non-standard stuff in those schemas?

Certainly, wherefore you probably really shouldn't assume anything.
		regards, tom lane[/QUOTE] 
http://archives.postgresql.org/pgsql-hackers/2003-12/msg00796.php

Anyhow, I was just curious - I don't expect to ever have to directly access these tables 😉. Isn't toast deprecated?

Sxooter

But were you in the postgres database for both of those queries you posted (the one that first didn't have permission, then couldn't find the object)???

pg_toast is not deprecated, as far as I know.

It's how postgres keeps track of things over a certain size (i.e. blocks of data that spill out of an 8k block.)

csn

Originally posted by Sxooter
But were you in the postgres database for both of those queries you posted (the one that first didn't have permission, then couldn't find the object)???

Yes - I did 'psql -U csn db', messed around with pg_t*, then did '\c db postgres' and tried again.