[GENERAL] Getting list of supported types in Postgres
Hi guys,
I am looking for a way to get list of supported types in Postgres
together with information whether type can have optional size (like
varchar for example), whether it can have precision (like decimal for
example), and whether it can come as value of sequence (like all integer
types for example), but I have trouble getting that info from pg_type
table. This is SQL I was using:
select
pg_catalog.format_type(oid, null),
*
from
pg_type
where
typnamespace = (select oid from pg_namespace where
nspname='pg_catalog') and
typisdefined and
typname not like '$_%' escape '$' and
typname not like 'pg%' and
typtype = 'b'
order by
typname
apparently pg_catalog.format_type for some types return quoted name
("char" for example), also I can't find decimal in results (there is
numeric, but I would like to have complete list of supported types, so
decimal should be included too). In documentation it is said that typlen
of -1 or -2 means that type is variable length, but I don't know how to
find out if type can have additional precision?
Regards,
Ivan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting list of supported types in Postgres
On 08/15/13 05:23, Michael Paquier napisa:
On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian wrote:
Try psql -E, and run the \dT command to see the query it uses.
You have also the following commands:
- ¥dT+, all types with addition information like its size ('var' is
for example variable length)
- ¥dTS+, this includes also the catalog types
Regards,
Hi Michael and Bruce,
Thank you for taking your time to reply.
If I run \dTS+ that returns something that resembles information I need,
but it is still missing info if type can have precision, and apparently
aliases are missing (no decimal for numeric, no character for char and
so on). Additionally it would be great if I could somehow also get
information if type can be indexed.
This doesn't have to come through SQL (I simply assumed that info can be
obtained either from information schema or Postgress specific views and
tables) - if you can point me to some source file that would be
acceptable too :-)
Regards,
Ivan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting list of supported types in Postgres
On 08/15/13 16:30, Adrian Klaver napisa:
On 08/15/2013 02:33 AM, Ivan Radovanovic wrote:
On 08/15/13 05:23, Michael Paquier napisa:
On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian wrote:
Try psql -E, and run the \dT command to see the query it uses.
You have also the following commands:
- ¥dT+, all types with addition information like its size ('var' is
for example variable length)
- ¥dTS+, this includes also the catalog types
Regards,
Hi Michael and Bruce,
Thank you for taking your time to reply.
If I run \dTS+ that returns something that resembles information I need,
but it is still missing info if type can have precision, and apparently
aliases are missing (no decimal for numeric, no character for char and
so on). Additionally it would be great if I could somehow also get
information if type can be indexed.
This doesn't have to come through SQL (I simply assumed that info can be
obtained either from information schema or Postgress specific views and
tables) - if you can point me to some source file that would be
acceptable too :-)
The docs would seem to be the place to go:
http://www.postgresql.org/docs/9.2/interactive/datatype.html
Thanks Adrian, sometimes we overlook most obvious solutions :-)
Now I just need to find out which types can be indexed (and which types
can be part of PK)
Regards,
Ivan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting list of supported types in Postgres
On 08/15/13 16:49, Adrian Klaver napisa:
On 08/15/2013 07:37 AM, Ivan Radovanovic wrote:
On 08/15/13 16:30, Adrian Klaver napisa:
On 08/15/2013 02:33 AM, Ivan Radovanovic wrote:
On 08/15/13 05:23, Michael Paquier napisa:
On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian
wrote:
Try psql -E, and run the \dT command to see the query it uses.
You have also the following commands:
- ¥dT+, all types with addition information like its size ('var' is
for example variable length)
- ¥dTS+, this includes also the catalog types
Regards,
Hi Michael and Bruce,
Thank you for taking your time to reply.
If I run \dTS+ that returns something that resembles information I
need,
but it is still missing info if type can have precision, and apparently
aliases are missing (no decimal for numeric, no character for char and
so on). Additionally it would be great if I could somehow also get
information if type can be indexed.
This doesn't have to come through SQL (I simply assumed that info
can be
obtained either from information schema or Postgress specific views and
tables) - if you can point me to some source file that would be
acceptable too :-)
The docs would seem to be the place to go:
http://www.postgresql.org/docs/9.2/interactive/datatype.html
Thanks Adrian, sometimes we overlook most obvious solutions :-)
Now I just need to find out which types can be indexed (and which types
can be part of PK)
http://www.postgresql.org/docs/9.2/interactive/indexes.html
doesn't list which types can be indexed and which can't?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting list of supported types in Postgres
On 08/15/13 16:59, Adrian Klaver napisa: On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: Now I just need to find out which types can be indexed (and which types can be part of PK) http://www.postgresql.org/docs/9.2/interactive/indexes.html doesn't list which types can be indexed and which can't? Postgres can handle a variety of indexes including indexing on expressions, which is why I pointed you to that link. To cut to the chase, in the above link at: http://www.postgresql.org/docs/9.2/interactive/indexes-opclass.html there is this: SELECT am.amname AS index_method, opf.opfname AS opfamily_name, amop.amopopr::regoperator AS opfamily_operator FROM pg_am am, pg_opfamily opf, pg_amop amop WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid ORDER BY index_method, opfamily_name, opfamily_operator; Thanks Adrian, but question was how to decide which types are indexable - query which you sent returns list of operators defined for some types - for example it returns operators for bytea too, and you can't index by bytea, so I don't see how you could decide if type can be indexed based on this? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting list of supported types in Postgres
On 08/15/13 17:15, Tom Lane napisa: Ivan Radovanovic writes: Thanks Adrian, but question was how to decide which types are indexable A little bit of research in the system-catalogs documentation will show you how to find the types that can be accepted by some index opclass (hint: "pg_opclass.opcintype::regtype"). As far as the other question goes, you could look for types that have a pg_type.typmodin function -- though I'm not sure whether you want to consider every possible usage of typmods as being a "precision". regards, tom lane Thanks Tom, I will take type definitions from documentation (as Adrian suggested), and it looks like your pg_opclass suggestion will solve indexability question. Case closed I guess :-) Regards, Ivan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting list of supported types in Postgres
On 08/15/13 17:27, Adrian Klaver napisa: On 08/15/2013 08:07 AM, Ivan Radovanovic wrote: On 08/15/13 16:59, Adrian Klaver napisa: On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: Now I just need to find out which types can be indexed (and which types can be part of PK) http://www.postgresql.org/docs/9.2/interactive/indexes.html doesn't list which types can be indexed and which can't? Postgres can handle a variety of indexes including indexing on expressions, which is why I pointed you to that link. To cut to the chase, in the above link at: http://www.postgresql.org/docs/9.2/interactive/indexes-opclass.html there is this: SELECT am.amname AS index_method, opf.opfname AS opfamily_name, amop.amopopr::regoperator AS opfamily_operator FROM pg_am am, pg_opfamily opf, pg_amop amop WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid ORDER BY index_method, opfamily_name, opfamily_operator; Thanks Adrian, but question was how to decide which types are indexable - query which you sent returns list of operators defined for some types - for example it returns operators for bytea too, and you can't index by bytea, Actually you can: CREATE TABLE bytea_test(id int, fld_1 bytea); test=# \d bytea_test Table "public.bytea_test" Column | Type | Modifiers +-+--- id | integer | fld_1 | bytea | test=# CREATE INDEX i ON bytea_test (fld_1); test=# \d bytea_test Table "public.bytea_test" Column | Type | Modifiers +-+--- id | integer | fld_1 | bytea | Indexes: "i" btree (fld_1) Didn't know that - I just tried on one existing table and it failed on account of index row too short ERROR: index row requires 14616 bytes, maximum size is 8191 SQL state: 54000 Although it looked suspicious like it could be solved by defining custom tablespace (never did that on Postgres so I am not sure if it would work), I assumed that it is because bytea can't be indexed. Obviously I learned one more new thing today :-) Thanks, Ivan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Bug in psql (\dd query)
Hello,
I was checking for way to get object comments, and it seems that \dd has
bug when it comes to extracting descriptions for constraints. Relevant
part of query psql is executing is:
SELECT DISTINCT tt.nspname AS "Schema", tt.name AS "Name", tt.object AS
"Object", d.description AS "Description"
FROM (
SELECT pgc.oid as oid, pgc.tableoid AS tableoid,
n.nspname as nspname,
CAST(pgc.conname AS pg_catalog.text) as name, CAST('constraint' AS
pg_catalog.text) as object
FROM pg_catalog.pg_constraint pgc
JOIN pg_catalog.pg_class c ON c.oid = pgc.conrelid
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_table_is_visible(c.oid)
/* more unions here */
) AS tt
JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND
tt.tableoid = d.classoid AND d.objsubid = 0)
ORDER BY 1, 2, 3;
obviously it is trying to get description for (table_oid,
constraint_oid, 0), while in fact it should read description for (oid of
pg_catalog.pg_constaint, constraint_oid, 0).
At least last tuple is what comment statement is inserting into
pg_description table
Regards,
Ivan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug in psql (\dd query)
On 08/21/13 16:03, Tom Lane napisa: Ivan Radovanovic writes: I was checking for way to get object comments, and it seems that \dd has bug when it comes to extracting descriptions for constraints. That code looks right to me, and it works according to a simple test: d1=# create table foo (f1 int primary key); CREATE TABLE d1=# comment on constraint foo_pkey on foo is 'here is a comment'; COMMENT d1=# \dd Object descriptions Schema | Name | Object |Description +--++--- public | foo_pkey | constraint | here is a comment (1 row) What exactly do you think the problem is? regards, tom lane Problem is if you create table in schema other than public (I am not sure if \dd should show comments only for objects in public schema, I assumed not?) db=# create schema test; CREATE SCHEMA db=# create table test.foo (f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE db=# comment on constraint foo_pkey on test.foo is 'here is a comment'; COMMENT db=# \dd Object descriptions Schema | Name | Object | Description +--++- (0 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug in psql (\dd query)
On 08/21/13 16:34, Ivan Radovanovic napisa: On 08/21/13 16:03, Tom Lane napisa: Ivan Radovanovic writes: I was checking for way to get object comments, and it seems that \dd has bug when it comes to extracting descriptions for constraints. That code looks right to me, and it works according to a simple test: d1=# create table foo (f1 int primary key); CREATE TABLE d1=# comment on constraint foo_pkey on foo is 'here is a comment'; COMMENT d1=# \dd Object descriptions Schema | Name | Object | Description +--++--- public | foo_pkey | constraint | here is a comment (1 row) What exactly do you think the problem is? regards, tom lane Problem is if you create table in schema other than public (I am not sure if \dd should show comments only for objects in public schema, I assumed not?) db=# create schema test; CREATE SCHEMA db=# create table test.foo (f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE db=# comment on constraint foo_pkey on test.foo is 'here is a comment'; COMMENT db=# \dd Object descriptions Schema | Name | Object | Description +--++- (0 rows) Obviously there is optional pattern argument for \dd which would show comments in different schema, so I it was my mistake after all. Sorry for false alarm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unique constraint and unique index
Just to verify: - when unique constraint is created using appropriate syntax rows are added to tables pg_constraint and pg_index (pg_constraint with type 'u' and referring to index with indisunique set to true) - when unique index is created row is added only to pg_index table but not to pg_constraint table (although in fact that index is behaving like constraint on table) Is that correct? Regards, Ivan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Querying information_schema [bug?]
Hello, I couldn't find anything mentioned about this in documentation and googling didn't help either: - if I connect to database as user who doesn't have permission to access all schemas then querying information_schema.schemata returns no rows (querying information_schema.tables returns only tables from accessible schemas, and executing \dn from psql returns list of all schemas) Desk=> select schema_name from information_schema.schemata; schema_name - (0 rows) Desk=> select distinct table_schema from information_schema.tables; table_schema cards information_schema pg_catalog (3 rows) Desk=> \dn List of schemas Name | Owner +--- cards | pgsql help | pgsql public | pgsql storage| pgsql (4 rows) Desk=> select version(); version - PostgreSQL 9.2.1 on amd64-portbld-freebsd8.3, compiled by cc (GCC) 4.2.1 20070831 patched [FreeBSD], 64-bit (1 row) Regards, Ivan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Logging access to data in database table
Hello, I need to log access to certain data in database in some log (I prefer to have that both in syslog and table in database), and I find it easy to write to syslog, but I can't solve the problem of writing this to database table. If this protected data is read only using postgres function , and if in the same function I add something like "insert into log_table (blah blah blah)", somebody could simply do begin; select * from access_function(); /* assuming access_function is function for accessing sensitive data */ rollback; and no info about access would be written in log_table. Is there some way to enforce insert within function to be always performed (I checked and commit can't be called within functions), or is there maybe some completely different clever way to solve this problem? Thanks in advance, Ivan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Logging access to data in database table
On 01/25/12 18:38, Greg Sabino Mullane napisa:
You would need to break out of the transaction somehow within that
function and make a new call to the database, for example using dblink
or plperlu. I've done the latter before and it wasn't too painful.
The general idea is:
- ---
$dbh = DBI->connect(...)
$sth = $dbh->prepare('INSERT into log_table...');
$sth->execute(@values);
$dbh->commit();
Fetch the data as normal, and return to the user.
- ---
Of course, you would want to cache the $dbh and $sth bits.
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201201251237
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Thanks for taking your time to reply, we will still consider whether to
use contrib/dblink or plperl, but this idea definitely wasn't something
any of us had in mind :-)
Thanks again,
Ivan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Logging access to data in database table
On 01/25/12 20:02, Misa Simic napisa: Thanks Bill, Make sense... db_link is probably then solution... Everything depends on concrete problem... But I still think security should be reconsidered (I would use db_link just in case there is no other options - if we must let users to have direct access to DB)... I mean, in that case when we need log each request for some sensitive data - we would not allow some user direct access to DB where he would be able to do such thing BEGIN TRAN, execute function what returns sensitive data, ROLLBACK Tran; (or many other things...) at least there would be an application layer above DB... (concretly in our case - Users do not have access to DB at all... everything is through Web App, actually DB - Web Service - User Apps (Web, Windows, Mobile etc...)) Thanks, Misa 2012/1/25 Bill Moran mailto:wmo...@potentialtech.com>> In response to Misa Simic mailto:misa.si...@gmail.com>>: > > But maybe it would be better to reorganise security on the way that users > who do not need to have access to some data - simply do not have it > (instead of to give them data and latter check log to confirm they have > taken it...) In many cases that's not enough. For example with HIPAA in the US, a user may be allowed to access data, but there still _has_ to be a log record for each access. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ Thanks for reply, Bill was right, this is security requirement that is independent of all other security mechanisms we have implemented in this system :-) I will check contrib/dblink - it seems to be one of the ways to solve this problem Best regards, Ivan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
