I would say that pg_catalog is the more complete one whereas the
information_schema the more generic, standards-conformant place. I would
stick with the information_schema unless that becomes inadequate. A case
in point may be sequences. Apart from
information_schema.columns.column_default I haven'
The following looks like a bug to me, but please someone review and tell
me what I might be missing. Seems that past a certain result set size a
"[NOT] IN (subquery)" stops behaving as expected and returns 0 matches
even when there should be matches. No errors are returned, just faulty
data. The ex
Yes, yes, of course... There are nulls in my t1 table. And, of course,
NOT IN can return THREE possible values: not just TRUE or FALSE but also
NULL...
select distinct (moo.goo not in (null)) from moo;
--> null
select count(*) from moo where moo.goo not in ('gai', 'pan', null)
--> 0, no matter wh
What does The SQL Standard say about this one?
create table foo (a varchar, b int);
insert into foo (a, b) select null, null from bar;
-- no problem
insert into foo (a, b) select distinct null, null from bar;
-- ERROR: column "b" is of type integer but expression is of type text
-- HINT: You w
> "Better" is in the eye of the beholder.
sorry for the value-laden term. "laxer" is more appropriate, of course!
the funny thing is that had they cast the NULLs to TEXT it would have
failed there too (they do not do implicit TEXT to INT).
> It surprises me not at all that
> Microsoft would be
> Sure, but in this example the required type of the value is clear from
> immediate context (ie, the INSERT). This is one of the cases where
> the SQL spec requires a bare NULL to be accepted. (BTW, 'no' and '4'
> in this example are *not* values of type text; they are
> untyped literals which
or, from the "stupid tricks" category:
SELECT
n.user_id,
max(lpad(extract(epoch from n.modified_date),12,'0') || ' ' || note)
FROM notes n
GROUP by n.user_id
i am not *really* suggesting this!
---(end of broadcast)---
TIP 5: don't forget
> > test=# insert into foo values (4::int,4::int);
> > INSERT 0 1
> > test=# insert into foo values (4::text,4::text);
> > ERROR: column "b" is of type integer but expression is of type text
> > HINT: You will need to rewrite or cast the expression.
> > test=# insert into foo values (cast(4 as
I have city and postal_code tables linked by city_postal_code through a
city_id and postal_code_id. The postal_codes have latitude/longitude,
the cities don't. I want to set the city lat/long to the average of the
associated postal codes (abstract for a minute on whether that actually
makes sense f
I have a table of names with two subsets of entities. I want to find
those names from set 1 that are substrings of names from set 2 from the
same table. Basically the pared down query I want is something like
this:
select t1.myname, t2.myname
from mytable t1
inner join mytable t2
what would you expect the following command to insert into column a:
copy foo (a,b) from stdin with csv;
"bar" , 3
\.
i was expecting to see 'bar', but instead i get 'bar ' (the spaces
between the double quote and the comma get inserted.
select length(a), * from foo;
length | a| b
--
Anybody have a clever way to quickly find whether there are any unused
indexes in a PG DB? One way I have done is to take queries from the DB
log, prepend an explain to each and grep the results, but I am wondering
if there are either any index usage stats maintained somewhere inside
Postgres or i
And don't forget that \COPY and especially COPY are usually much faster
(and, IMHO, easier to compose/maintain) than gobs of INSERTs.
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Aaron Bono
> Sent: Tuesday, October 10, 2006 1:46 PM
> To: Dirk Ja
> I'm trying to figure out how i can query the postgres information
> schema from a bourne shell script on linux. I need to know if a
> user/table exists. Does someone mind giving me a quick example of how
> this works, is this possible?
% for tn in `psql -Umnp -dmnp_gp -hstgdb0 -tA -c"select ta
simplest might be psql -l.
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Jon Horsman
> Sent: Friday, October 27, 2006 8:13 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] How to query information schema from shell script
>
> In my original
I am trying to do some record comparisons using IS DISTINCT FROM and I
feel like I am missing something. Basically comparisons between manually
constructed records work as expected, but if I have a record returned by
a select on one (or both sides) of the comparison I get errors "ERROR:
operator do
t type(s). You may
need to add explicit type casts.
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan
> Sent: Tuesday, October 31, 2006 1:23 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] record datatype comparison
Should be entirely executable in postgres using psql variables (once you
get around the psql quoting weirdnesses). do this:
gp_test=# \set item '\''Apple'
gp_test=# \set qty 6
gp_test=# \echo :item
'Apple'
gp_test=# \echo :qty
6
and then run his query.
Now, i am not sure what DBMS lets Mr. Celk
Why don't you start by presenting the query in a more readable form
(hint use SQL-standard JOIN syntax for all of your joins) and maybe
narrowing just to a couple of tables to isolate the problem. As it is,
it is hard to understand. In the process of rewriting you will be forced
to think about each
In 8.1 I have a situation where nullability of user defined datatypes
does not seem to be enforced. Using the following steps I end up with a
table that has a column that should not be nullable, but has nulls in
it. Here's a pared down outline of the steps:
-- create a datatype that should enforc
Yes, the thread did not seem to go very far. The SQL standard does seem
inconsistent in this area, but that is not an argument for allowing data
constraint violation. Until the standard is modified I think it would be
good for the reputation of the DBMS we all love to come up with a fix...
Even t
all my SQL-writin' life i have been assuming that expressions like =,
IN, BETWEEN in the WHERE clause are, in the most general sense,
alternative ways of doing the same things. i am hitting some very very
bizarre results in PGSQL:
i have a (very involved) view, say v_foo, largely optimized to be
q
> > BYs on user_id and various subqueries, but my basic thought is that
> > should not really matter...
>
> You're unlikely to get any useful comment on this when you have not
> shown any of those details, nor even an EXPLAIN.
yes, i know. i guess i was partially just venting. sorry. the problem
Thanks all for the various useful thoughts. Let me backtrack a bit and
state my real underlying issue a bit with actual examples. Hope not to
bore you with the length of this. Looks to me like an optimizer issue
unless I am missing something.
So, suppose I have a query:
select *
from stuff
inner
>Is there a way to see from the log files on what sql statement is
> currently by which user? In other words, I want to monitor
> the DB activity.
for a current snapshot you don't need the logs, try:
select * from pg_stat_activity;
(command string needs to be enabled for your database.)
> How will I enable command string to see the commands?
in your postgresql.conf set stats_command_string = true
read
http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html for
details
---(end of broadcast)---
TIP 3: Have you checked
I would like to be able to drop a schema with all of its objects, but
don't want to accidentally drop objects that are in other schemas. If
there are such objects I'd prefer the drop to fail. If I use DROP SCHEMA
FOO CASCADE I run the risk of dropping objects from other schemas that
depend on objec
> > As an immediate solution can anyone share a comprehensive query to
> > INFORMATION_SCHEMA/PG_CATALOG that can be used to check for any
> > cross-schema dependencies and halt before issuing a DROP
> with a CASCADE?
>
> Issue the DROP without CASCADE and read the error message.
Well, to be pre
before i start reinventing the wheel does anyone have handy a function (or
straight SQL) for calculating the centerpoint coordinates and the radius of the
minimum bounding circle (not the the circumcircle) of a triangle (coordinates
of 3 points given as inputs).
a bonus would be a generalizati
ks!
george
> -Original Message-
> From: Brad Nicholson [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 01, 2006 9:12 AM
> To: George Pavlov
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] finding unused indexes?
>
> On Tue, 2006-08-01 at 09:05 -0700, Geor
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 10, 2007 6:36 AM
>
> "George Pavlov" <[EMAIL PROTECTED]> writes:
> > so is it safe to say that an index that has
> > pg_stat_user_indexes.idx_scan,
&g
your problem is a little unorthodox, but i will spare you the "why the
heck do you want to do this?" discussion and assume you have good
reasons... so here's a "dynamic SQL" approach:
select 'create table test (id bigint, '|| array_to_string(array(select
a||' text' from foo),', ')||');';
not pret
1. not exactly what you were looking for, but i answer this partially by
putting a commented-out CVS expansion tag (e.g. $Id:) in the body of the
function so that it gets into the catalog and can be searched:
CREATE OR REPLACE FUNCTION foo ()
RETURNS void AS
$BODY$
-- $Id: foo.sql,v 1.6
33 matches
Mail list logo
