Re: [SQL] AGE function
--- Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Tue, Sep 06, 2005 at 10:05:06PM -0700, Louise
> Catherine wrote:
> > When I execute this statement :
> > select AGE(TO_DATE('20041101','mmdd'),
> > TO_DATE('19991201','mmdd'))
> >
> > at postgre 7.3.3, the result :
> > age
> > -
> > 4 years 11 mons 1 day
> >
> > at postgre 8.0.3, the result :
> > age
> > ---
> > 4 years 11 mons
> >
> > My question :
> > 1. How does postgre 7.3.3 calculate AGE function?
> > 2. Why the result produced by postgre 7.3.3
> > is different from postgre 8.0.3 ?
>
> I get the same answer ("4 years 11 mons") in 7.2.8,
> 7.3.10, 7.4.8,
> 8.0.3, and 8.1beta1. Have you verified that
> to_date() is returning
> the correct dates? What are the results of the
> following queries
> on each of your systems?
>
> SELECT TO_DATE('19991201','mmdd'),
> TO_TIMESTAMP('19991201','mmdd');
> SELECT TO_DATE('20041101','mmdd'),
> TO_TIMESTAMP('20041101','mmdd');
> SHOW TimeZone;
at PostgreSQL 7.3.3 :
to_date to_timestamp
-- -
1999-12-01 1999-12-01 00:00:00+07
to_date to_timestamp
-- -
2004-11-01 2004-11-01 00:00:00+07
TimeZone
---
unknown
at PostgreSQL 8.0.3 :
to_date to_timestamp
-- -
1999-12-01 1999-12-01 00:00:00+07
to_date to_timestamp
-- -
2004-11-01 2004-11-01 00:00:00+07
TimeZone
Asia/Jakarta
> What operating system are you using?
I'm using SuSE Linux 9.0 for the operating system
The result from your queries are similar, so what's
wrong in my queries? What should I do? Cause I must
migrate database from PostgreSQL 7.3.3 to
PostgreSQl 8.0.3.
> BTW, it's "PostgreSQL" or "Postgres," not "postgre."
Sorry about the name :)
Thanks,
Louise
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] AGE function
am 06.09.2005, um 22:05:06 -0700 mailte Louise Catherine folgendes:
> When I execute this statement :
> select AGE(TO_DATE('20041101','mmdd'),
> TO_DATE('19991201','mmdd'))
> ...
> 4 years 11 mons 1 day
> 4 years 11 mons
>
> My question :
> 1. How does postgre 7.3.3 calculate AGE function?
> 2. Why the result produced by postgre 7.3.3
> is different from postgre 8.0.3 ?
How long is a month? 28 days, 29 days, 30 days, 31 days?
select TO_DATE('20041101','mmdd') - TO_DATE('19991201','mmdd');
This is under 7.2.1 and 8.0.3 tha same: 1797. I guess, this is a
rounding problem.
Regards, Andreas
--
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
===Schollglas Unternehmensgruppe===
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] AGE function
On Tue, Sep 06, 2005 at 10:05:06PM -0700, Louise Catherine wrote: > 1. How does postgre 7.3.3 calculate AGE function? > 2. Why the result produced by postgre 7.3.3 > is different from postgre 8.0.3 ? The change appears to have been committed in 7.4 and later in response to Bug #1332: http://archives.postgresql.org/pgsql-bugs/2004-12/msg00013.php http://archives.postgresql.org/pgsql-committers/2004-12/msg9.php http://archives.postgresql.org/pgsql-committers/2004-12/msg8.php -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] AGE function
Louise Catherine <[EMAIL PROTECTED]> writes:
> When I execute this statement :
> select AGE(TO_DATE('20041101','mmdd'),
> TO_DATE('19991201','mmdd'))
> at postgre 7.3.3, the result :
> age
> -
> 4 years 11 mons 1 day
With TimeZone set to 'Asia/Jakarta' on a Linux machine, I can reproduce
that behavior in 7.3.* but not 7.4 and later. I believe this is the
relevant change:
2004-12-01 14:57 tgl
* src/backend/utils/adt/timestamp.c (REL7_4_STABLE): Fix
timestamptz_age() to do calculation in local timezone not GMT, per
bug 1332.
and here is a link to the discussion that prompted the change:
http://archives.postgresql.org/pgsql-bugs/2004-12/msg00013.php
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] AGE function
On Wed, Sep 07, 2005 at 08:24:54AM -0600, Michael Fuhr wrote: > On Tue, Sep 06, 2005 at 10:05:06PM -0700, Louise Catherine wrote: > > 1. How does postgre 7.3.3 calculate AGE function? > > 2. Why the result produced by postgre 7.3.3 > > is different from postgre 8.0.3 ? > > The change appears to have been committed in 7.4 and later in > response to Bug #1332: Specifically, 7.4.7 and later. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Help with multistage query
I have a perl script that issues a series of SQL statements
to perform some queries. The script works, but I believe there must be a
more elegant way to do this.
The simplified queries look like this:
SELECT id FROM t1 WHERE condition1; ;returns
about 2k records which are stored in @idarray
foreach $id (@idarray) {
SELECT x FROM t2 WHERE id=$id; ;
each select returns about 100 records which are saved in a perl variable
}
At this point I have a list of about 200k records, from
which I can manually filter based on x values.
There are indices on id in both t1 and t2, so the first two
queries are both index scans. I cannot afford a table scan on t2 due to
the size of the table.
Like I said, this works (and uses only index scans), but I
would think it would be better to somehow select the 200k records into a temp
table. Because the temp table would be relatively small, a seq scan is ok
to produce my final list.
Also, I am now issuing the second query about 2k times…this
seems inefficient.
I would think there would a way to restate the first
two queries as either a join or a subselect. My initial attempts result
in a table scan (according to EXPLAIN) on t2.
For example I tried
SELECT x FROM t2 WHERE id in ( SELECT id FROM
t1 WHERE condition1);
but this gives a seq scan.
Any ideas are appreciated.
Re: [SQL] uuid type (moved from HACKERS)
Mark, Nathan, I'm moving this over to the PGSQL-SQL list, away from -hackers, as it's no longer a -hackers type discussion. Hope you don't mind! > On Wed, Sep 07, 2005 at 11:31:16AM -0700, Josh Berkus wrote: > > I'm also a little baffled to come up with any real application where > > making an id number for most tables "unguessable" would provide any > > kind of real protection not far better provided by other means. For > > your "users" table, sure, but that's a very special case. > > It should never be the sole means of defense, however, it can be quite > effective at prevention. > > For a rather simple example, consider a site that associates a picture > with each member. If the pictures are named 1.jpg, 2.jpg, 3.jpg, etc. > it makes it ridiculously easy to write a script to pull all of the > pictures off the site. This can be bothersome, as the only type of > person who would do this, is the type of person with an illegitimate > motivation. I want the data to be easily and freely accessible as > specific objects, but I do not wish to provide an easy way of > dumping all of the data as a unit. > > By making the picture identifier unguessable, it discourages the most > common sort of abuse of the system. If the number is unguessable, and > they can't access the directory as a listing, it will be sufficiently > difficult as to discourage the common abuser of the system. On the > other hand, an obviously guessable identifier may *encourage* the > common person to consider abuse. > > In my case, it isn't only pictures. I don't want people pulling all > the data off the site as a dump, and using it how they wish, but I do > wish to make the data freely available, and easily accessible from a > web browser. > > I'm not under the impression that it is impossible for a competent > person to dump my database. I am under the impression that the people > who would do such a thing, tend not to be intelligent, and will be > stopped by this simple tactic. > > I could use any identifier at all. It could be a random sequence of > characters. The UUID appeals to me, as I don't have to re-invent > the concept. This use of UUID falls outside the scope of using it > to join tables. It's a handle that is associated with the data, > for external identification of the object. Seems like this would be better served by simply encrypting the three-part universal key (server|table|row) using an encryption key which is not public/obvious. That would preserve the obscurity of object naming while still allowing the UUID to contain useful information. > I happen to also use it as an internal primary key for the objects > that fit this category, as I wish to benefit from the built-in merge > capabilities of UUID over SERIAL, and I don't currently see the > point of keeping a SERIAL and a UUID for each object. On the last > point, I did start to do this, but every single one of my queries > become more complicated as a result. Using the SERIAL for joining, > and the UUID for identifying a set of rows was becoming a little > ridiculous for my purposes. Using only the UUID to provide for > all my purposes is suiting my requirements for the cost of 1.5X > the size of a primary key index, 2X the size of a index for > a n to n relation mapping UUID to UUID, and an far less significant > increase in table space (much less than 1.5X, although I haven't > finished calculating it yet). > > Not that everybody should rip out SERIAL and replace it with UUID, > but it really isn't that bad, and in some cases, such as mine, > I don't see the point of using both, and choose to instead allow > UUID to solve many of my concerns at the same time, with an > acceptable for me cost in database pages. Oh, you won't get any argument from me on that one -- no need for *two* surrogate keys in a table. IME, most tables don't need even *one*. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Help with multistage query
I have a perl script that issues a
series of SQL statements to perform some queries. The script works, but
I believe there must be a more elegant way to do this.
The simplified queries look like
this:
SELECT id FROM t1 WHERE
condition1; ;returns about 2k records which are stored in
@idarray
foreach $id (@idarray)
{
SELECT x FROM t2
WHERE id=$id; ; each select returns about 100 records which are
saved in a perl variable
}
how about
select t1.id from t1, t2 where t1.id = t2.id and
t2.id = x
[SQL] column names, types, properties for a table
Hi, Is it possible to issue an SQL query that lists column names, types (int, varchar, boolean, etc.), properties (like NOT NULL or UNIQUE) for a given table name ? Regards, Roger Tannous. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] column names, types, properties for a table
Is it possible to issue an SQL query that lists column names, types (int, varchar, boolean, etc.), properties (like NOT NULL or UNIQUE) for a given table name ? Start psql with the -E option. Then "\d yourtable". It will print out the queries that are run internally to show you the table info... for example: % psql -E cc_8004 Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit cc_8004=# \d rep_general; * QUERY ** SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relname ~ '^rep_general$' ORDER BY 2, 3; ** * QUERY ** SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_catalog.pg_class WHERE oid = '21548032' ** * QUERY ** SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = '21548032' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum ** * QUERY ** 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 = '21548032' AND c.oid = i.indrelid AND i.indexrelid = c2.oid ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname ** * QUERY ** SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '21548032' ORDER BY inhseqno ASC ** Table "public.rep_general" Column |Type | Modifiers -+-+ id | integer | not null loc_id | integer | not null dt | timestamp without time zone | not null num_active_visits | integer | not null default 0 num_passive_visits | integer | not null default 0 min_visit_length| integer | not null default 0 max_visit_length| integer | not null default 0 total_visit_length | integer | not null default 0 total_time_before_touch | integer | not null default 0 total_time_of_touch | integer | not null default 0 num_coupons_printed | integer | not null default 0 num_passive_promos | integer | not null default 0 num_active_promos | integer | not null default 0 Indexes: "rep_general_pk" primary key, btree (id) "rep_general_dt_idx" btree (dt) "rep_general_loc_id_idx" btree (loc_id) cc_8004=# ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Help with multistage query
- Original Message -
From:
Russell Simpkins
To: pgsql-sql@postgresql.org
Sent: Wednesday, September 07, 2005 4:05
PM
Subject: Re: [SQL] Help with multistage
query
I have a perl script that issues
a series of SQL statements to perform some queries. The script works,
but I believe there must be a more elegant way to do this.
The simplified queries look like
this:
SELECT id FROM t1 WHERE
condition1; ;returns about 2k records which are stored in
@idarray
foreach $id (@idarray)
{
SELECT x FROM t2
WHERE id=$id; ; each select returns about 100 records which are
saved in a perl variable
}
how about
select t1.id from t1, t2 where t1.id = t2.id and
t2.id = x
or more correctly, based on the OP's example:
select t2.x from t1, t2 where t1.id = t2.id and t1.id =
--
Matt
Re: [SQL] Searching for results with an unknown amount of data
> Now, I want to find all objects that have at most properties 1,2,3, say (so > something with (1,2) is okay, as is (1,2,3)). I can't see a way to do this > -- can anyone help? It sounds like you are trying to find all objects that do not have any properties outside of a specific list. One way to get that list is: That's exactly right. SELECT a_id FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.a_id AND b.property NOT IN (1, 2, 3) ) ; Yupp, this appears to do it! The 'double negation' is very clever. Thanks a lot, DL ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] column names, types, properties for a table
OK, I found the solution ( after a little bit of research and testing :) )
Does anyone have recommendations regarding the following query ?
SELECT pg_attribute.attname, pg_attribute.attnotnull,
pg_attribute.atthasdef, pg_type.typname, pg_attrdef.adsrc AS
default_value, pg_constraint.contype, pg_constraint.conname
FROM pg_attribute
INNER JOIN pg_class ON (pg_class.oid = pg_attribute.attrelid AND
pg_class.relkind = 'r')
INNER JOIN pg_type ON (pg_type.oid = pg_attribute.atttypid AND
pg_type.typname NOT IN ('oid', 'tid', 'xid', 'cid'))
LEFT JOIN pg_attrdef ON (pg_attrdef.adrelid = pg_attribute.attrelid AND
pg_attrdef.adnum = pg_attribute.attnum)
LEFT JOIN pg_constraint ON (pg_constraint.conrelid = pg_attribute.attrelid
AND (pg_constraint.conkey[1] = pg_attribute.attnum OR
pg_constraint.conkey[2] = pg_attribute.attnum OR pg_constraint.conkey[3] =
pg_attribute.attnum OR pg_constraint.conkey[4] = pg_attribute.attnum OR
pg_constraint.conkey[5] = pg_attribute.attnum OR pg_constraint.conkey[6] =
pg_attribute.attnum) OR pg_constraint.conkey[7] = pg_attribute.attnum OR
pg_constraint.conkey[8] = pg_attribute.attnum)
WHERE pg_class.relname = 'sip_groupe_sanguin';
Best Regards,
Roger Tannous.
--- Philip Hallstrom <[EMAIL PROTECTED]> wrote:
> > Is it possible to issue an SQL query that lists column names, types
> (int,
> > varchar, boolean, etc.), properties (like NOT NULL or UNIQUE)
> > for a given table name ?
>
> Start psql with the -E option. Then "\d yourtable". It will print out
> the queries that are run internally to show you the table info... for
> example:
>
> % psql -E cc_8004
> Welcome to psql 7.4.2, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> cc_8004=# \d rep_general;
> * QUERY **
> SELECT c.oid,
>n.nspname,
>c.relname
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE pg_catalog.pg_table_is_visible(c.oid)
>AND c.relname ~ '^rep_general$'
> ORDER BY 2, 3;
> **
>
> * QUERY **
> SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
> FROM pg_catalog.pg_class WHERE oid = '21548032'
> **
>
> * QUERY **
> SELECT a.attname,
>pg_catalog.format_type(a.atttypid, a.atttypmod),
>(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
> WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
> a.atthasdef),
>a.attnotnull, a.attnum
> FROM pg_catalog.pg_attribute a
> WHERE a.attrelid = '21548032' AND a.attnum > 0 AND NOT a.attisdropped
> ORDER BY a.attnum
> **
>
> * QUERY **
> 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 = '21548032' AND c.oid = i.indrelid AND i.indexrelid =
> c2.oid
> ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
> **
>
> * QUERY **
> SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
> WHERE c.oid=i.inhparent AND i.inhrelid = '21548032' ORDER BY inhseqno
> ASC
> **
>
> Table "public.rep_general"
> Column |Type | Modifiers
>
-+-+
> id | integer | not null
> loc_id | integer | not null
> dt | timestamp without time zone | not null
> num_active_visits | integer | not null
> default 0
> num_passive_visits | integer | not null
> default 0
> min_visit_length| integer | not null
> default 0
> max_visit_length| integer | not null
> default 0
> total_visit_length | integer | not null
> default 0
> total_time_before_touch | integer | not null
> default 0
> total_time_of_touch | integer | not null
> default 0
> num_coupons_printed | integer | not null
> default 0
> num_passive_promos | integer | not null
> default 0
> num_active_promos | integer | not null
> default 0
> Indexes:
> "rep_general_pk" primary key, btree (id)
> "rep_general_dt_idx" btree (dt)
> "rep_general_loc_id_idx" btree (loc_id)
>
> cc_8004=#
>
>
__
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redc
Re: [SQL] uuid type (moved from HACKERS)
Josh Berkus writes: > Mark, Nathan, > > I'm moving this over to the PGSQL-SQL list, away from -hackers, as it's no > longer a -hackers type discussion. Hope you don't mind! > > > On Wed, Sep 07, 2005 at 11:31:16AM -0700, Josh Berkus wrote: > > > I'm also a little baffled to come up with any real application where > > > making an id number for most tables "unguessable" would provide any > > > kind of real protection not far better provided by other means. For > > > your "users" table, sure, but that's a very special case. > > > > It should never be the sole means of defense, however, it can be quite > > effective at prevention. > > > > For a rather simple example, consider a site that associates a picture > > with each member. If the pictures are named 1.jpg, 2.jpg, 3.jpg, etc. > > it makes it ridiculously easy to write a script to pull all of the > > pictures off the site. This can be bothersome, as the only type of > > person who would do this, is the type of person with an illegitimate > > motivation. I want the data to be easily and freely accessible as > > specific objects, but I do not wish to provide an easy way of > > dumping all of the data as a unit. Of course you could have just done the same thing using an hmac (or a simple hash like crypt) and not had to store an extraneous meaningless piece of information in your database. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
