Getting ENUM values

dummy57

If I have a table field of type: enum('first', 'second'), how do I construct a query that will give me back all possible values (here the words 'first' and 'second') for this field?

Sxooter

you should be able to use:

show fields from tablename;

then iterate across the set to find the field name.

I'm not a big MySQL user, but it sounds like it might work.

csn

Yeah, I remember trying to do this in mysql. I couldn't find any way to get the enum values back as rows (or a row). Like Sxooter said, I think it's SHOW FIELDS FROM table, then parse/regexp the results for the enum values.

csn

Sxooter-

Instead of enums in PG you'd set a constraint to check a field's value was in a certain set - correct? Is there an easy way in PG to get those values?

Sxooter

For now, yes, a check constraint with an in() condition is the answer. Note, however, that enumerated types are specified in the SQL3 standard (or were at one time, they may have gotten pulled by now, I haven't read the every changing SQL 3 spec in a while) so eventually Postgresql may well have an enumerated type.

Assuming you created a table like this:

create table test (color text check (color in('red','green','blue')),id serial);

then you'd likely need a query like this:

SELECT 
  consrc, 
  conname 
FROM 
  pg_catalog.pg_constraint r 
JOIN 
  pg_catalog.pg_class c 
ON (r.conrelid=c.oid) 
WHERE 
  c.relname ~ '^test$';

Simple huh? 🙂 To figure out how to do this kind of crazy stuff, you can run psql with the -E switch, which will make psql show you all the funky SQL it uses in the backend to talk to the pg_* tables. In this case, the queries are for 7.4. beta 1, so they may be a little different for older versions.

Sometime around 7.5 the full SQL99 information schema should be online, making a lot of this jumping through hoops unnecessary.

csn

🙂 - the underlying voodoo of PG that are the pg_ tables. I've been meaning to learn more about them (and probably end up REALLY fubar'ing things 😉 ).

I thought I remember some posts in hackers/general about the information schema. What exactly is that? Is it primarily about getting meta information about tables, databases, columns, etc.?

Sxooter

Ahhh. Schemas. Schemas are basically namespaces that live between the database and table level. Imagine a department in a company that handles financials having a database called just that, finance. Now, if we have accounts receiveable, accts payable, billables, etc, we can put them each in a different schema, so we have

psql template1

create database finance;
\c finance
create schema customers;
create schema accts_payable;
create schema accts_receiveable;
create schema billables;

create table customers.customer_accts (....;
create table accts_payable.sometable ....;
create table accts_receiveable.someothertable

Now we can put default different security models in each schema, so that accts_receiveable people can READ from the accts_payable, but not change it, and vice versa...

So, where MySQL lets you join databases like:

select * from db1.table1, db2.table2 join on ()...

In Postgresql the same syntax would be withing a single database, but across multiple schemas. This means large joins and what not can benefit from the ability to play in one area so to speak, while you get much the same security you would from multiple databases.

In 7.4, they are now implementing the information schema. Here's a short list of all the tables currently in the information schema:

applicable_roles
check_constraints
column_domain_usage
column_privileges
column_udt_usage
columns
constraint_column_usage
constraint_table_usage
data_type_privileges
domain_constraints
domain_udt_usage
domains
element_types
enabled_roles
information_schema_catalog_name
key_column_usage
parameters
referential_constraints
role_column_grants
role_routine_grants
role_table_grants
role_usage_grants
routine_privileges
routines
schemata
sql_features
sql_implementation_info
sql_languages
sql_packages
sql_sizing
sql_sizing_profiles
table_constraints
table_privileges
tables
triggered_update_columns
triggers
usage_privileges
view_column_usage
view_table_usage
views

select * from information_schema.oneoftheabove;

gives a pretty neat little display.

Using the information_schema you can get:

select * from check_constraints where constraint_name='constraint_name';

csn

Definitely some Good Stuff 🙂.

BTW, is 7.4 going to have a native Windows port? I didn't see it in the changelog

Sxooter

Nope, 7.4 will NOT have native windows, or PITR either.

It's mainly a consolidation / performance tweaking release. There's tons of stuff that's been done, but the big projects were running too late to wait for, so they'll have to try and make the 7.5 release in 6 to 9 months.

Note, however, that a lot of work HAS been done on both the windows release and PITR, that will translate straight over to 7.5, so it is quite likely that the 7.5 release will have them.

Initial testing from most users on 7.4 beta is showing a 10 to 30% increase in overall speed, and huge gains in things like select * from table where id in (select ids from othertable); and a few other problem performance areas of the past.