Re: [SQL] Data length and data precision
Just what I needed, thx! -Oorspronkelijk bericht- Van: [email protected] namens A. Kretschmer Verzonden: vr 6-2-2009 11:04 Aan: [email protected] Onderwerp: Re: [SQL] Data length and data precision In response to Bart van Houdt : > Hi all, > > I'm trying to write some code to make a 'fingerprint' of a database. This to > compare a customer database with a reference database of our own. > Therefore I'm trying to retrieve information like this: > -Table name > pg_class.relname where relkind = 'r' > -Column name > pg_attribute where attrelid = pg_class.oid > -Data type > pg_type where oid = pg_attribute.atttypid > -Data length > if pg_attribute.atttypmod = -1 then pg_type.typlen > else pg_attribute.atttypmod > -Data precision > No clue > -Nullable > pg_attribute.attnotnull > > As you can see I'm missing the data precision (e.g. numeric (19,2)) and I'm > not > entirely sure about the data length. > > Can anyone tell me where to find the data precision of a column? > And can anyone tell me if I get the data length from the correct places? information_schema.columns, numeric_precision. http://www.postgresql.org/docs/current/static/infoschema-columns.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Data length and data precision
Hi all, I'm trying to write some code to make a 'fingerprint' of a database. This to compare a customer database with a reference database of our own. Therefore I'm trying to retrieve information like this: -Table name pg_class.relname where relkind = 'r' -Column name pg_attribute where attrelid = pg_class.oid -Data type pg_type where oid = pg_attribute.atttypid -Data length if pg_attribute.atttypmod = -1 then pg_type.typlen else pg_attribute.atttypmod -Data precision No clue -Nullable pg_attribute.attnotnull As you can see I'm missing the data precision (e.g. numeric (19,2)) and I'm not entirely sure about the data length. Can anyone tell me where to find the data precision of a column? And can anyone tell me if I get the data length from the correct places? Thanks in advance, Bart
Re: [SQL] Data length and data precision
In response to Bart van Houdt : > Hi all, > > I'm trying to write some code to make a 'fingerprint' of a database. This to > compare a customer database with a reference database of our own. > Therefore I'm trying to retrieve information like this: > -Table name > pg_class.relname where relkind = 'r' > -Column name > pg_attribute where attrelid = pg_class.oid > -Data type > pg_type where oid = pg_attribute.atttypid > -Data length > if pg_attribute.atttypmod = -1 then pg_type.typlen > else pg_attribute.atttypmod > -Data precision > No clue > -Nullable > pg_attribute.attnotnull > > As you can see I'm missing the data precision (e.g. numeric (19,2)) and I'm > not > entirely sure about the data length. > > Can anyone tell me where to find the data precision of a column? > And can anyone tell me if I get the data length from the correct places? information_schema.columns, numeric_precision. http://www.postgresql.org/docs/current/static/infoschema-columns.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] test
This is a testplease disgregard.
[SQL] count(distinct)
Hi, this is probably an old issue but I'm not all that experienced. I wonder if an index can be accessed rather directly, as to speed up a query like "select count(distinct())", by simply calculating the number of branches (leaves) an index has. Or at least to skip sorting. Best regards, Zdravko -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] test
This is a test, please disregard.
Re: [SQL] count(distinct)
Zdravko Balorda wrote: > this is probably an old issue but I'm not all that experienced. > > I wonder if an index can be accessed rather directly, as to speed up > a query like "select count(distinct())", by simply calculating the > number of branches (leaves) an index has. Or at least to skip sorting. Afraid not. Have a search of the archives for discussion on "mvcc visibility indexes" for lots on this. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Implementing "access lists" (was: Must I use DISTINCT?)
On Fri, Feb 6, 2009 at 1:11 AM, Stephan Szabo wrote: > > On Thu, 5 Feb 2009, Michael B Allen wrote: > >> Please consider the following SQL >> >> SELECT e.eid, e.name >> FROM entry e, access a >> WHERE e.eid = 120 >> AND (e.ownid = 66 OR e.aid = a.aid) >> >> The intent is to match one entry with the eid of 120. However I would >> like to impose an additional constraint that either e.ownid must be 66 >> or e.aid must match the aid of an entry in the access table (there's >> actually a lot more to the query but I think this should be sufficient >> to illustrate my problem). >> >> The problem is that the e.ownid is 66 and therefore the same entry is >> returned for each access entry. >> >> Of course I can simply SELECT DISTINCT but that seems like an improper >> usage of DISTINCT here. >> >> Is there an alternative way to write this query? I only want to select >> from the access table for the purpose of constraining by aid. > > Would something like: > SELECT e.eid, e.name FROM entry e WHERE e.eid = 120 AND > (e.ownid = 66 OR e.aid in (select a.aid from access a)) > do what you wnat? Indeed it does. Using sub-selects eliminates the duplicate entries. Thanks Stephan. However, now I have a deeper SQL challenge. As I stated before, I'm ultimately trying to implement "access lists" where I simply join on (or subselect across) a table of links that link account and access list entries. If I only use one level of indirection it's easy. However, I'd really like to see if I can efficiently implement *two* levels of indirection. Meaning instead of having an access list linked with account entries, I want to have an access list linked with access lists that are linked with account entries. This is much more useful because you can grant permissions to lists of accounts. Otherwise, you have to constantly add and remove individual accounts from access lists which would be very tedious. This is the full "access list" query with two levels of indirection (that uses subselects instead of joins): SELECT e.eid, e.type, e.name, e.ownid, e.aclid FROM foo_entry e WHERE e.eid = 70 AND (e.ownid = 66 OR e.aclid = 66 OR e.aclid IN (SELECT a FROM foo_link WHERE data >= 10 AND (b = 66 OR b IN (SELECT a FROM foo_link WHERE b = 66) OR b IN (SELECT b FROM foo_link WHERE a = 66) )) OR e.aclid IN (SELECT b FROM foo_link WHERE data >= 10 AND (a = 66 OR a IN (SELECT a FROM foo_link WHERE b = 66) OR a IN (SELECT b FROM foo_link WHERE a = 66) )) ) So the above should return the foo_entry record with eid 70 but only if the user's own account (the entry with an eid of 66) matches either the ownid OR aclid OR the eid of an entry linked with an entry with an eid of 66 OR linked with an entry that is linked with an entry with an eid of 66. The 'data' field is the access level - the higher the level, the more permission you have. So my question is simply - can this query be reduced or optimized futher? Another thing I'm wondering is if this query can scale. At some point I'll just load a lot more records and find out. But I suspect there are people that would already know if this is a hopeless exercise? Here's the sample data again. There are only two tables: > select eid, type, name, ownid, aclid from foo_entry; +-+--+---+---+---+ | eid | type | name | ownid | aclid | +-+--+---+---+---+ | 64 |5 | system|64 | 0 | | 66 |5 | abaker|66 | 0 | | 67 |5 | bcarter |67 | 0 | | 68 |5 | cdavis|68 | 0 | | 69 |7 | [email protected]|66 | 114 | | 70 |7 | [email protected] |67 | 120 | | 71 |7 | [email protected]|68 | 0 | | 113 |6 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |66 | 0 | | 92 | 10 | IOPLEX Software |64 | 0 | | 114 |8 | Small Cap Consultants |66 | 0 | | 120 |8 | Equity Report Readers |66 | 0 | > select * from foo_link; +-+-+--+--+--+ | a | b | weight_a | weight_b | data | +-+-+--+--+--+ | 114 | 120 |1.000 |1.000 | 10 | | 66 | 69 |1.000 |1.000 | NULL | | 67 | 70 |1.000 |1.000 | NULL | | 68 | 71 |1.000 |1.000 | NULL | | 66 | 72 |1.000 |1.000 | NULL | | 69 | 72 |1.000 |1.000 | NULL | | 70 | 73 |1.000 |1.000 | NULL | | 71 | 74 |1.000 |1.000 | NULL | | 67 | 89 |0.900 |1.000 | NULL | | 71 | 92 |1.000 |1.000 | NULL | | 66 | 113 |1.000 |1.000 | NULL | | 66 | 114 |1.000 |1.00
