do not know if right mailing list
in psql in Ubuntu, when beginning with a smaller terminal, eg, 80 col wide,
then moving to a larger terminal, eg, 132 col wide, the readline(?) editor in
psql still treats like 80 cols, making it impossible to edit longer text.
--
Sent via pgsql-sql mailing
have database with many partitions. each partition table has its own primary
key sequence.
Column || Modifiers
---++--
uno_id|| not null default
From: chester c young chestercyo...@yahoo.com
Subject: Re: [SQL] pg_dump not correctly saving schema with partitioned
tables?
To: Tom Lane t...@sss.pgh.pa.us
Date: Tuesday, January 31, 2012, 5:40 PM
--- On Tue, 1/31/12, Tom Lane t...@sss.pgh.pa.us
wrote:
From: Tom Lane t
have an db with about 15 tables that will handle many companies. no data
overlap between companies. is it more efficient run-time to use one database
and index each row by company id, and one database and partition each table by
company id, or to create a database for each company?
it is a
what is the best performance / best practices for frequently-used simple dml,
for example, an insert
1. fast-interface
2. prepared statement calling insert ... with binary parameters
3. prepared statement calling myfunc(... with binary parameters; myfunc takes
its arguments and performs an
: [SQL] best performance for simple dml
To: chester c young chestercyo...@yahoo.com
Cc: pgsql-sql@postgresql.org
Date: Monday, June 27, 2011, 12:35 AM
Hello
try it and you will see. Depends on network speed, hw speed. But the most fast
is using a COPY API
http://www.postgresql.org/docs/9.0
very nice pointers. thank you very much!
--- On Mon, 6/27/11, Pavel Stehule pavel.steh...@gmail.com wrote:
From: Pavel Stehule pavel.steh...@gmail.com
Subject: Re: [SQL] best performance for simple dml
To: chester c young chestercyo...@yahoo.com
Cc: pgsql-sql@postgresql.org
Date: Monday, June
a very cool paradigm, but is it actually a good idea?
--- On Mon, 6/27/11, Pavel Stehule pavel.steh...@gmail.com wrote:
From: Pavel Stehule pavel.steh...@gmail.com
Subject: Re: [SQL] best performance for simple dml
To: chester c young chestercyo...@yahoo.com
Cc: pgsql-sql@postgresql.org
Date
Le 20/07/09 15:19, chester c young a écrit :
within a trigger need to know if the UPDATE statement
set a column. the column might be set to the old value
or a different value.
(want to make sure the app is sending all necessary
values)
thanks
If the column to test is known
within a trigger need to know if the UPDATE statement set a column. the column
might be set to the old value or a different value.
(want to make sure the app is sending all necessary values)
thanks
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to
is there a way for COPY FROM to ignore unused columns in CSV?
in other words, if table t1 has columns c1, c2, and if csv has columns c1, c3,
c2, could I do something like
COPY t1( c1, null, c2 ) FROM 'file.csv'
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make
for example:
selectt1.*
from t1
left join t2 on( expensive condition );
since t2 is not used in the result nor is it used in determining the result, is
the expensive condition to left join t2 used?
(the question goes as to how much generated sql needs to be cleaned to be
efficient)
2. perhaps global could mean simply that
the definition is global - if called for session and not
exist in session, then session prepares it first from the
global def. there would need to be a back reference in
case the global def was changed or dropped.
Yes, this seems to be a good
1. like the idea because right now I'm having to track which prepared statement
(sets) are in which session. using xcache to track this, but little bit
dangerous. could query the database first but the whole idea is to take a load
off the db.
2. perhaps global could mean simply that the
create table access (name text, address ip)
I want to construct a SELECT statement which will return ONLY tuples
containing IP and name pairs IF there is an IP that has two or more
NAMEs associated with it.
many ways:
select a1.* from access a1 where exists(
select 1 from access
--- Mag Gam [EMAIL PROTECTED] wrote:
Hi All,
I have a view that generates output similar to this.
select * from foo.view;
ts | size
---+-
2002-03-16| 11
2002-03-17| 16
2002-03-18| 18
2002-03-19| 12
I am
have several schemae, each with identical tables.
in create scripts have been taking great care to fully qualify, eg,
col1 references schema1.tab1( col1 )
however, just got burnt big time on sequences! need to qualify them as
well, eg
col1 integer default nextval( 'schema1.seq1' )
\dt is
--- Emi Lu [EMAIL PROTECTED] wrote:
Isn't this a bug about trim both.
select trim(both 'BR/' from 'BR/ROI Engineering Inc.');
btrim
-
OI Engineering Inc.
(1 row)
R is missing? How?
you misread - 'BR/' argument is a list of characters, _not_ a
Stephan Szabo [EMAIL PROTECTED] wrote:
On Wed, 2 Apr 2008, chester c young wrote:
it appears I have a broken RI in my db.
Yeah, that looks pretty broken. Can you reproduce this from a clean
start repeatedly or is this a one off? Do you ever turn off triggers,
perhaps by modifying
--- Stephan Szabo [EMAIL PROTECTED] wrote:
Is it possible you ever had a before delete trigger that just did a
return
NULL rather than raising an exception? IIRC, explicitly telling the
system to ignore the delete will work on the referential actions.
yes, it is possible, for example, a
--- Stephan Szabo [EMAIL PROTECTED] wrote:
is it is possible, for example, a function without a body or
without a return old.
are you saying this would override the RI constraint?
If it returned something that would have prevented the delete without
an error, yes.
this is very good
it appears I have a broken RI in my db.
call_individual.clh_id references call_household.clh_id
\d call_individual
...
Foreign-key constraints:
call_individual_clh_id_fkey FOREIGN KEY (clh_id) REFERENCES
call_household(clh_id) ON DELETE CASCADE
however:
development=# select clh_id from
insert into t1( c1 )
select c1 from t2
returning t1.c1, t2.c2;
ERROR: missing FROM-clause entry for table t2
is there any way to make this work?
Never miss a thing. Make Yahoo your home page.
A sample of the current results data would be like
datesales
2008-03-07 100.00
2007-03-10 150.00
2007-03-18 50.00
and what I'm trying to do is fill in the missing dates with sales
values of 0.
what I do is have a table called days that is
When inserting a record is there a way to have postgres create a
random number for a field such that it is unique?
you could use oid
Looking for last minute shopping deals?
Find them fast with
is there any way to share a cursor between sessions?
I have a costly query whose records need to be visited by in order by n
number of concurrent sessions, and am unable to find the best way of
doing this.
I'd almost like to write a daemon that hands out the next record, but
that's a royal pain
I'm considering building a protective mechanism, and am seeking
feedback
on the idea. The approach would be to add a new column named ro to
each table at invoice level and below. Then have a trigger on
'ro'==true deny the write, and probably raise a huge stink. As
invoice
are mailed
--- Richard Broersma Jr [EMAIL PROTECTED] wrote:
I've created quite a few functions that log modifications to various
history tables. (the history table has the same name as the base
table but is prefixed by the 'History.' schema.) The only difference
between functions I can find is the table
I tried
UPDATE things JOIN inventory ON things.thing_id = inventory.thing_fk
SET number = 0
WHERE color = 'red'
use the cool from clause in the update
update things t
set number = 0
from inventory i
where t.thing_id = i.thing_fk
and i.color = 'red';
I have found that in many complex queries left join is exponentially
faster than a (not) exists clause.
I don't understand why, generally speaking, this is so frequently so
effective.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam
postgres A, db 'test', tablespace /pg/test1
postgres B, db 'test', tablespace /pg/test2
tablespace /pg/test1 only has A db 'test'
tablespace /pg/test2 only has B db 'test'
if
- A and B shut down
- /pg/test1 copied to /pg/test2
- A and B restarted
would B db 'test' be running the data that was
I'm getting lots of delimited files from Excel and MySQL users that,
mid-file, begin truncating lines if ending in null values.
for example:
1781: one,two,three,,
1782: one,two,three,,
1783: one,two,three,,
(delimited files from Open Office are well behaved)
is there any way to handle this
I'm trying to use substr() and position() functions to extract the
full host name (and later a domain) from a column that holds URLs.
substring( href from '.*://\([^/]*)' );
Pinpoint customers who
I'm trying to use substr() and position() functions to extract the
full host name (and later a domain) from a column that holds URLs.
substring( href from '.*://\([^/]*)' );
typo: no backslash in front of left paren
substring( href from '.*://([^/]*)' )
match up thru //
within
And what I'd like is something that would give me the counts for the
number of occurrences of each unique hostname. Something much like
`uniq -c'. Can anyone tell me how that's done or where I should look
for info? (I'm not sure what to look for, that's the problem).
select substring(
I was wondering if it is possible to set the 'context' for running an
sql command
Oracle has a Context('varname') that returns the value of varname for
the session. to best of my knowledge pg has nothing like this.
I guess bottom line, is it possible to execute a bunch of SQL
statements
...
When you say use rules to inject constants, how would I go about
doing this? Could you maybe give a brief example?
create view tab1_dml as select * from tab1;
-- note:
-- CONSTANT1 = 8
-- CONSTANT2 = 15
create or replace rule tab1_insert as
on insert
to tab1_dml
do instead(
I have a table with the following simplified form:
create table t (
run_id integer,
domain_id integer,
mta_id integer,
attribute1 integer,
attribute2 integer,
unique(run_id, domain_id, mta_id)
);
The table has about 1 million rows with run_id=1, another 1 million
rows with
I'm trying in SUSE to connect to a postgres db and this is the
error:
Ident Authentification failed for user root
others will guide better, but for now, in pg_hba.conf
# local is for Unix domain socket connections only
local all all ident
--- Paul Lambert [EMAIL PROTECTED] wrote:
I have some triggers in an MS SQL server database which I need to
copy
across to a PG database. I've not yet done triggers in PG so I was
hoping to get a little bit of a pointer on the first one as a place
to
start and work my way through from
CREATE TABLE items (
id INT,
typ INT...
PRIMAY KEY (seq,typ));
id typ
+-
1 'a'
2 'a'
3 'a'
1 'b'
4 'a'
2 'b'
you will need to use pre insert trigger since you cannot use column
references in default expression.
SELECT field1 / 2 AS foo,
field2 * 2 AS bar,
foo + bar AS total
WHERE foo 12;
The first two fields are fine, it's the third that's a problem. The
database reports
ERROR: column foo does not exist
First, I think it would be great if this worked - like
--- Bob Singleton [EMAIL PROTECTED] wrote:
Revisiting a Time In Status query I received help on - I'm trying to
narrow down a subset of data I return for analysis.
Given a statusLog as entityId, statusId, timestamp that might look
something like
entityId | statusId | timestamp
tables A and B: a post row trigger on A cause updates on B which has
its own post row trigger.
does the post trigger on A wait until post trigger on B is executed? -
this seems intuitive to me.
does the post trigger on B wait until the trigger on A has completed?
or is post trigger A launched
does the post trigger on A wait until post trigger on B is
executed? - this seems intuitive to me.
How can it wait until the trigger on B is executed if the trigger on
B doesn't
actually get triggered until someone updates B and it's the trigger
on A
doing the update?
trigger A
within a table pl/pgsql trigger, what's the easiest way to see if the
schema for the triggered table is the same as search_path?
Be a better Globetrotter. Get better travel answers from someone who
Hi
has anyone done any work on comparing schemas?
I'm trying to automatically propagate changes in a master schema to
child schemas. All schema changes will originate at master. Currently
child schemas are in the same database, but in the future might be in
different databases (clusters?).
on lwn I read that pg is having problems releasing because of a want of
reviewers.
although my C is far too rusty I'd like to help out, perhaps with doc
or testing.
can someone direct me to the appropriate site?
In my opinion your best bet in terms of getting around the primary
key
violation is to create a temporary table ...
good idea! from my experience it's almost always best to pull raw info
into a buffer table before introducing it into the real world.
...
instead of date_trunc('day',sent_messages.date)
why don't you have a function that takes four three arguments:
- beginning date of query
- interval, ie, reminder_services.activity_days_min
- timestamp, ie, sent_messages.date
have it return the minimum date for that interval
as I think I
them in the order they are currently stored in that variable. So take
for example this foreign application variable:
ids = 3,2,5,1,4
kludgy, but:
1. store your ids in a pg array
2. select from the array
3. on order by, write a function that takes the row.id and array as
parameters,
But I'm thinking that maybe it's a job for a database table. Each
new
row would be written with a status (10=new). And that the modem
process would poll for new rows. Problem is there will be lots of
rows,
but only a trivial few will be new. The huge index file and the
polling seem
create table table1 (
id SERIAL PRIMARY KEY
extension UNIQUE,
other fields
)
create table table2 (
id SERIAL PRIMARY KEY
extension UNIQUE,
different fields
)
Basically table 1 and table 2 both have the concept of an extension
that must be unique but the rest of the info in the
from id extracting name from several tables. which is generally the
better approach?
select case when tab_tla='usr' then (select name from users where ... )
case when tab_tla='con' then (select title from contents where
...)
endas name;
as versus
select name
fromusers
where
trying to do something like
select d.day,
c.name
from [dates between day1 and day2] d
left join c.some_table;
but cannot figure out what to put into the brackets.
Finding fabulous
--- Greg Toombs [EMAIL PROTECTED] wrote:
I'm trying to figure out how to nicely implement a C++
class-likesystem with PostgreSQL. Consider the following:
Tables Fruit, Apple, Orange
you can do this traditionally or through pg inheritance, although I do
not think inheritance is well
--- Richard Broersma Jr [EMAIL PROTECTED] wrote:
Maybe then you'll add a table basket that has a foreign key to the
fruit
table... ;-)
From the inheritance link:
...
A serious limitation of the inheritance feature is that ...
it's my understanding that inheritance has become
I'm considering implementing a search box on my review web site
http://lesculturelles.net and am looking for a simple way to match
entered words against several columns on related tables:
show.show_name, story.title, person.firtname, person.lastname, etc.
one solution would be a view:
create view search_v as select
'show'::name as tab_nm,
show_id as tab_pk,
'Show Name' as description,
show_name as search
from show
union select
'story'::name,
story_id,
'Story Title',
title
from story
union ...
What is that ::name cast for?
I am trying to modify the dabo (a python wxpython
ide for database forms creation) code to allow the
selection of tables in any schema. I need a query
that will return records with schema, table,
columname and columne type.
create view pg_cols as select
s.nspname as schema_nm,
cannot figure this out
# \set var 'value'
# select * from some_table where some_col = :var;
ERROR: column value does not exist
cannot get those quotes around the value. tried:
# \set var ''value''
# \set var value
in each case:
# \echo :var
value
thanks
--- A. R. Van Hook [EMAIL PROTECTED] wrote:
I have been trying to do an 'or' function such that if a field value
is
zero then use 1 as a multiplier:
select sum((1 | i.count) * s.cost) as COST ...
try select sum( (case when i.count=0 then 1 else i.count end) * s.cost
) as COST ...
roopa perumalraja [EMAIL PROTECTED] wrote: HiI have two tables.Tick table has fields like ticker, time, price volume andTimeseriestable has fields like ticker, time, avg_price, avg_volume.The time field in Timeseries table is different from time in tick table, its the timeseries for every
--- Luca Ferrari [EMAIL PROTECTED] wrote:
Hi all,
I guess this is an already asked question, but I didn't found an
answer, so
apologize me. Imagine I've got two tables:
skill(id,description) // primary key = id
family(id,description)// primary key = id
and I want to
My server is based MST, but web clients from Maine to Hawaii, and they wish to see timestamps based in their own locale.Can anyone tell me how they're handling this? (sorry - can't get rid of my clients)
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail.
Thanks !Michael Fuhr [EMAIL PROTECTED] wrote:Offhand I can't think of a way to do what you want with regexp_replace()but you could use PL/Perl. Something like this should work:CREATE FUNCTION mcfix(text) RETURNS text AS $$ $_[0] =~ s/\bMc([a-z])/Mc\u$1/g; return $_[0];$$ LANGUAGE plperl
column name in table bue has miscapitalized Mc names, eg, 'John Mcneil' instead of 'John McNeil'.(this should be easy but) how do you construct the update query?also, regexp_string( 'Mcneil', 'Mc(.*)', initcap('\\1') ) = 'neil' _not_ Neil' - is this correct?
All-new Yahoo! Mail - Fire up a
in a simple tree structured tabletable t( id primary key, pnt_id references t( id ), name);does anyone know an easy howbeit sneaky way of determining ancestory and decendency without recursive functions,select name from t where exists ancestor or descendentthanks for insight
Get your own web
in PHP for example, where there are multiple sessions and which you get is random:how do you know if the session you're in has prepared a particular statement?and/or how do you get a list of prepared statements?last, is there any after login trigger that one could use to prepare statements the
this does not work: select to_char(current_time,'HH24:MI')what am I missing? is it possible to format a time column in a select?thanks,stumped, aka, chester __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around
--- Robert Paulsen [EMAIL PROTECTED] wrote:
One problem with the above is that the list of attributes is fixed. I
am looking for a way to assign new, previously undefined, attributes
to
items without changing the table structure. Is it ever appropriate to
do
the following?
...
There are two
trying to do this exlusively in triggers is a forray into folly.
take advantage of instead of or do also rules to create a compound
statement before your triggers do their work. (in terms of maintenance
and sanity, it's best if a trigger touches only its own record.)
as a handsweep example:
is there any way to grant over all applicable objects in a schema,
grant select on schema pop to public; -- wrong
without specifically granting the select on each table?
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection
do not want to grant ALL PRIVILEGES to user, only SELECT - do not want
PUBLIC to have any dml priveleges on this schema
--- Michael James [EMAIL PROTECTED] wrote:
GRANT ALL PRIVILEGES ON databaseName To username;
is there any way to grant over all applicable objects in a schema,
grant
Here is my query SQL:
SELECT key100 FROM ncccr10
WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);
It is is running after 30 minutes. Here is the query plan:
I would try an outer join:
select a.key100
from ncccr10 a
left join ncccr9 b on( key100 )
where b.key100 is null;
also
--- Mark Fenbers [EMAIL PROTECTED] wrote:
select l.lid,l.fs,max(h.obstime) from location as l
inner join height as h on h.lid = l.lid
where l.fs 0.0
group by l.lid,l.fs;
The above query works as expected in that is fetches the lid, fs and
time of the latest observation in the height
anybody have a good way to impliment constants in the database?
using an immutable pgpgsql function, constant_name() - works, but not
necessarily very efficient.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
is is possible for to have a do instead trigger on a view that is a
plpgsql function?
__
Yahoo! DSL Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com
---(end of
understand that in 8.0 pg has session variables. cannot find in doc
how to use them.
__
Yahoo! DSL Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com
---(end of
is there any way within a rule to raise an exception?
__
Yahoo! for Good - Make a difference this year.
http://brand.yahoo.com/cybergivingweek2005/
---(end of broadcast)---
TIP 4: Have
table t1:
id integer primary key,
seq integer not null unique
the seq is for ordering the rows as the user likes. however, if the
rows are moved around, eg
begin
update t1 set seq=4 where id=5
update t1 set seq=5 where id=4
end
will bomb because the first update has two rows of seq=4
--- Steve SAUTETNER [EMAIL PROTECTED] wrote:
SELECT * FROM famille WHERE famille_code NOT IN
(SELECT DISTINCT famille_mere_famille_code FROM famille);
try
select * from famille f1 where not exists
(select 1 from famille f2
where f1.famille_code = f2.famille_mere_famille_code);
Tom Lane [EMAIL PROTECTED] wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
On Tue, Oct 25, 2005 at 04:56:11PM -0700, chester c young wrote:
in php (for example) it's frequently nice to get the structure of
table without any data,
Have you considered SELECT * FROM mytable LIMIT 0
Tom Lane [EMAIL PROTECTED] wrote:
chester c young [EMAIL PROTECTED] writes:
i think i misled: the goal is to retrieve _one_ row where the value
of each attribute is null.
Er, what for? There's no data content in that, by definition. Why
not retrieve zero rows and look at the metadata
in php (for example) it's frequently nice to get the structure of a
table without any data, ie, pull a single row with each attribute's
value is null. I use the query (dual is a table of one row ala
Oracle):
select m.* from dual
left join mytable m on( false );
this works every time, but if
can anybody recomend web hosting that provides postgresql? I have
found a couple, but their pricing is several times the going rate using mySql.
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
What is the best source doc for postgresql setup in a production
environment? I have read
- giving it a big chuck of shmem
- os not marking data files as accessed or modified
but cannot find the doc.
thanks,
Chester
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free,
how do you set sorting for indicies and order by? is it set once for
the database, or can it be set per index?
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
---(end of
--- Demidem Mohamed Amine [EMAIL PROTECTED] wrote:
hello,
Can anyone help me create a function that creates a table:
create function create_table( text ) returns integer as '
declare
p_tab alias for $1;
v_exec text;
begin
v_exec := ''create table '' || p_tab || ''( id integer )'';
-- standard setup:
create table t1( c1 int primary key, data text );
create domain dom_c1 int references t1 on delete cascade;
create table t2( c2 int primary key, c1 dom_c1, moredata text );
-- will not work with using
create view v1 as select t1.*, t2.moredata
from t1 join t2 using( c1 );
--
This is obtuse, but it would work: Have a function that would
dynamically build a view that has each of your type names as a column
name. It could be triggered by any changes to the types table. The
data table would be a left joined onto the customer table for each
column. Then select from the
sorry about this - braindead and cannot find in doc. what's pg's
rownum pseudo-column or function name that returns the record number of
a set?
__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com
inheriting pk and triggers
pg inheritance is quite limited. what i (and i'm sure many others)
have done is:
1. create master sequence
2. create base table
3. create base trigger procedures
4. create derived tables, using inherit
5. write procedure p( table_name ) that
a) sets pk of table_name
--- Matthew Nuzum [EMAIL PROTECTED] wrote:
I thought I would be crafty and devise a function that would always
return the highest numbered item in the table. But it doesnt work.
It always gives me a parse error at $1. Heres the function:
build the query as a string and execute it.
I could not find much documentation about SQL 92 schemas that
7.3 now supports. I understood it was a structure to group various
objects, which allows faster privilege management, namespaces
definition.
Besides that, I don't see any advantages. I'd be glad if someone
could point them
When doing database work over the web, especially when many records are
on one page, *many* updates get posted to pg that do not change the
record. Eg, the page may contain 50 records, the user changes 1, and
submits.
I assume that a no-change update takes the same resources as a real
update,
96 matches
Mail list logo
