Re: [SQL] [GENERAL] id and ID in CREATE TABLE
On Fri, 19 Jul 2002, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > But anyway this is not so important, but why upper cases are bad ? > > It's well established that all-lower-case text is more readable than > all-upper-case ... Agreed. Absolutely. But, since the SQL standard says upper case, wouldn't it be useful to at least have a switch (run time, initdb time, or ./configure time???) called something like FOLDTOUPPER (in upper case of course :-) If it's an easy win I'd be willing to do it. I'm not the world's greatest C hacker, but I still remember enough of it to be competant. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] id and ID in CREATE TABLE
On Fri, 19 Jul 2002, Bruce Momjian wrote: > scott.marlowe wrote: > > On Fri, 19 Jul 2002, Tom Lane wrote: > > > > > [EMAIL PROTECTED] writes: > > > > But anyway this is not so important, but why upper cases are bad ? > > > > > > It's well established that all-lower-case text is more readable than > > > all-upper-case ... > > > > Agreed. Absolutely. But, since the SQL standard says upper case, > > wouldn't it be useful to at least have a switch (run time, initdb time, > > or ./configure time???) called something like FOLDTOUPPER (in upper case > > of course :-) > > > > If it's an easy win I'd be willing to do it. I'm not the world's greatest > > C hacker, but I still remember enough of it to be competant. > > Yea, I guess we could. I think the consensus is that the uppercase > default is so weird, we don't know anyone who would want to use it. > Would you? Not if I had a choice. But it isn't about what I want or what you want, it's about doing what makes postgresql the most useful for the most people. I can see many situations where this would make postgresql a better choice than it is right now for certain projects, like: writing an app to be as portable as possible (i.e. the reason we have standards...) -or- migrating from some other database that follows the standard (oracle does I believe) and don't want to go in and re-write SQL queries from all the front end apps that hit the database. -or- making closed source database mining tools more likely to get along with postgresql I can think of a lot of reasons people could desire this, even though I myself would prefer to NOT have upper case. My other thought on it was whether we could make it case insensitive. Again, settable by some switch. It could attribute names in mixed case, but all comparisons would be done in upper or lower case. But I don't know how much that would cost us in processing power, especially if it had to fold case based on locale where we can't just use a simple bit flip to make everything lower or upper case when parsing it. By the way, there have been messages about the problems with case folding in this mailing list for the last six months about once a month, so if there was a simple switch to make it behave the way people need it to behave instead of the way it currently behaves, answering those questions would be as easy as pointing to the right place in the docs. So, I would propose a run time setting that has the four possible settings: case_handling: fold_to_lower (default) fold_to_upper (i.e. SQL92/99 compatibility mode) case_insensitive (this one may be more work than it's worth) case_sensitive (i.e. unix mode) If case insensitive were implemented it I would recommend we do in a way that allows the database to store the tables in mixed case as put in, but parse internally on lower or upper case only. Is local an issue, or does postgresql expect you to do everything in ascii 8 bit with no funny chars in attribute names? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] id and ID in CREATE TABLE
On Fri, 19 Jul 2002, Tom Lane wrote: > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > Agreed. Absolutely. But, since the SQL standard says upper case, > > wouldn't it be useful to at least have a switch (run time, initdb time, > > or ./configure time???) called something like FOLDTOUPPER (in upper case > > of course :-) > > The main problem with this is what do you do with the system catalogs? > If we start folding to upper case then existing clients will break > unless we also rename pg_class to PG_CLASS, etc; and that will break > them anyway if they wrote "pg_class". > > I don't believe that conforming to this particular small aspect of the > spec is worth the pain it would cause. But it's not a small aspect if it means someone either can't use an app with postgresql because it was written to spec, or someone has to spend a bunch of time rewriting all their queries to work with postgresql. I'm not talking about something I'd want on all my tables or all my databases, but there are many instances (look over this list's archive and you'll see them) where following spec would help migration issues. Plus the pg_class stuff is kind of a blind ally. If we're looking at foldtoupper as a setting, then we're already admitting that we're doing it to be interchangable with other dbmses. If that's the case, no one is gonna be accessing the pg_* tables, because you wouldn't do that in an app you're writing to be portable. And if you're migrating to postgresql, you won't have anything there that would access pg_* as well. Leave the system catalogs in lower case, and don't fold calls to anything that's a system catalog. Or wrap them all to upper if the fold to upper is set. I don't see this being a setting that many people would change, but then again, most people don't change sql_inheritance or transform_nulls_equals but their both there for the same reason as what I'm talking about. Some other databases do things a little differently, and if we want to be a drop in replacement, we can either accomodate them or thumb our noses at them and tell them it's not our problem. In all honesty, this change is more "right" than transform_nulls_equals since transform_nulls_equals makes postgresql BREAK the SQL standard, and this one would make it follow it. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [GENERAL] arrays
On Sun, 29 Sep 2002, Mike Sosteric wrote: > On Sun, 29 Sep 2002, Bruce Momjian wrote: > > Apologies in advance if there is a more appropriate list. > > We are currently developing a database to host some complicated, XMl > layered data. We have chosen postgres because of its ability to store > multidimensional arrays. We feel that using these will allow us to > simplify the database structure considerably by storing some data in > multidimensional arrays. the long and the short of it is that arrays are useful to store data, but should not be used where you need to look up the data in them in a where clause. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PHP] [SQL] Parent table has not oid?
On Sun, 10 Nov 2002, Tom Lane wrote: > "Zhidian Du" <[EMAIL PROTECTED]> writes: > > CREATE TABLE Link ( > >Protein_ID oid > >CONSTRAINT one > >REFERENCES Protein (oid) > >ON DELETE CASCADE, > >Link varchar(128) > > ); > > When I insert a record into this child table, it says > > "ERROR: constraint one: table protein does not have an attribute oid" > > How old is your Postgres? > > IIRC, this was made to work in 7.2 or thereabouts. > > Note that using OID as a foreign key is not really a good idea, because > it's problematic to dump and restore. You'd be better off with a serial > column as primary key. Yeah, I found that out the hardway and spent a couple days rewriting an app that had used OIDs in a way it really shouldn't have. The only time I use OIDs now is to get rid of duplicate rows by hand or such like that. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [HACKERS] [GENERAL] Bug with sequence
On 21 Nov 2002, Rod Taylor wrote: > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > > Of course, those would be SQL purists who _don't_ understand > > concurrency issues. ;-) > > Or they're the kind that locks the entire table for any given insert. Isn't that what Bruce just said? ;^) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [HACKERS] [GENERAL] Bug with sequence
On 21 Nov 2002, Rod Taylor wrote: > On Thu, 2002-11-21 at 15:09, scott.marlowe wrote: > > On 21 Nov 2002, Rod Taylor wrote: > > > > > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > > > > Of course, those would be SQL purists who _don't_ understand > > > > concurrency issues. ;-) > > > > > > Or they're the kind that locks the entire table for any given insert. > > > > Isn't that what Bruce just said? ;^) > > I suppose so. I took what Bruce said to be that multiple users could > get the same ID. > > I keep having developers want to make their own table for a sequence, > then use id = id + 1 -- so they hold a lock on it for the duration of > the transaction. I was just funnin' with ya, but the point behind it was that either way (with or without a lock) that using something other than a sequence is probably a bad idea. Either way, under parallel load, you have data consistency issues, or you have poor performance issues. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] subselects - which is faster?
On Thu, 12 Jun 2003, Cedar Cox wrote: > It's been a while since I've done much SQL.. > > . I'm wondering which of these two queries is faster (both get the > same result)? > > . Which one is more correct? Does it even matter or are they the > same? The first one reads easier to me. > > . What's the difference between "InitPlan" and "SubPlan"? > > explain SELECT eqid, >(select name from tbleqattrtypes where id= > (select eqattrtypeid from tbleqattrs > where id=main.eqattrid)) > as attrtype, eqattrid from tbleqattrmap as main; > > NOTICE: QUERY PLAN: > Seq Scan on tbleqattrmap main (cost=0.00..1.15 rows=15 width=8) > SubPlan > -> Seq Scan on tbleqattrtypes (cost=0.00..1.04 rows=1 width=12) > InitPlan > -> Seq Scan on tbleqattrs (cost=0.00..1.09 rows=1 width=4) > > > explain SELECT eqid, >(select > (select name from tbleqattrtypes where id=sec.eqattrtypeid) >from tbleqattrs as sec where id=main.eqattrid) > as attrtype, eqattrid from tbleqattrmap as main; > > NOTICE: QUERY PLAN: > Seq Scan on tbleqattrmap main (cost=0.00..1.15 rows=15 width=8) > SubPlan > -> Seq Scan on tbleqattrs sec (cost=0.00..1.09 rows=1 width=4) > SubPlan > -> Seq Scan on tbleqattrtypes (cost=0.00..1.04 rows=1 width=12) > > One additional detail: right now the tables are all very small, and > tbleqattrtypes will not grow much, but tbleqattrs will eventually be > very large. A couple of quick points. 1: Postgresql uses a cost based planner, not a rule based planner. This means you need to run analyze every so often to let the database know how many rows of what kind of data are in each table. This also means that if you are going to have 100,000 rows when you go live, then you need to create 100,000 representative rows now in order to figure out which is faster. 2: User 'explain analyze select ...' to make the database actually run the query and time it for you. Then you'll know which is faster. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to make a IN without a table... ?
On Thu, 12 Jun 2003, David Pradier wrote: > On Thu, Jun 12, 2003 at 01:16:27PM +0200, Christoph Haller wrote: > > > > > > i ran today in a problem when doing some (i mean too much for me) > > advanced sql... > > > > > > What i want to do is something like this: > > > > > > SELECT > > > my_var1, > > > my_var2, > > > my_function(my_var1, my_var2) > > > FROM ( > > > SELECT > > > '1', > > > '2', > > > '3', > > > '4' > > > ) AS my_var1_values, > > > ( > > > SELECT > > > '1', > > > '2', > > > '3', > > > '4' > > > ) AS my_var2_values > > > > > > In short, i want to calculate the result of the function my_function > > for > > > some values of my_var1, cross by some values of my_var2. > > > These values are not taken in a table, but put in directly. > > > They are a lot, so i would prefer not to write the whole thing, line > > > after line. (Let's say 10 values for the first, and 40 for the second > > => > > > 400 lines of code to maintain...) > > > > > > I really don't see how to do this :-/ > > > > > What about using a TEMP TABLE? > > I avoid as hell to use temporary tables. This is part of a complex > database, with more than 250 different tables. So i don't even want to > think about adding temporary tables, brrr ! Just FYI, temporary tables in pgsql are invisible to other connections even if they have the same name, and are auto-dropped when the connection is dropped. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] select date range?
On Tue, 17 Jun 2003, ko wrote: > Hi, > > When I use sql statement to select the date range from $_POST value,It doesn't work. > > --- > "select * from mydatabase where mydate between '$_POST[start_date]' and > '$_POST[end_date]' " > > There is no error,but the result is not I want. > > ps: > (1)mydate attribute is timestamp > (2)$_POST[start_date] is something like '2003/05/12' You may be having problems with how PHP interprets (or more correctly, doesn't) arrays inside of strings. the problem is that inside of a string the autointerpretation of a string won't work for arrays, only simple scalars. Change your string to this: "select * from mydatabase where mydate between '".$_POST[start_date]."' and '".$_POST[end_date]."'" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] join syntax
On Tue, 17 Jun 2003, Jodi Kanter wrote: > One of my colleagues has created a database where he has the same field > name in two tables and uses this field to link his tables rather than > some arbitrary value. For example, he has used "exp_id" in two tables. > When writing his joins he uses a syntax that says something like JOIN ON > EXP_ID. Can someone tell me what that syntax should be? I am not very > familiar with it since I typically use the syntax where one field is set > equal to the other. > Personally I prefer not to set databases up this way but cannot seem to > convince him of this. And yet I am supposed to now help him with his > database and application.. Is there some documentation that would define > this type of syntax? How is this handled if you have more than one table > in the join? It does not appear that this format would allow for this. If you're joining two tables on a field that has the same name you can use a natural join. select * from table1 natural join table2; ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Text insert max. size.
On Wed, 18 Jun 2003, Rudi Starcevic wrote: > Hi, > > I'm doing some planning for storing some xml data and am wondering > what is the maximum amount I could insert with one SQL statement. There's no limit to max SQL statement size in postgresql, but practically, you might want a REALLY big machine if you're gonna toss around 100 megs in a single query. > For example if I read an xml file off the hard drive and wanted to store > that in a text column what would be the largest file I could insert. > How about a 10 KB file ? How about 1 gig. Nice limit, eh? :-) > I guess also each programing language may have limitations too. > I this case I'm interested in using PHP. Generally you're gonna hit a limit with the max memory size set in the php.ini file, which defaults to 8 meg max process size before abort. You can crank this up so high your machine starts to swap out. I'd guess PHP is likely limited to some number internally, but I've messed around with a hundred or so megs before. If you wanna build a HUGE SQL query, you're likely better off to use fread and build the query in a file then run it with `psql dbname
Re: [SQL] CREATE table1 FROM table2
On Mon, 23 Jun 2003, L.V.Boldareva wrote: > Hello! > > many people posted their answer to this simple question. however, > neither > CREATE TABLE AS > nor > SELECT INTO > > do not take care about keys and triggers, etc. > > The commands above only copy the structure of the table, and the data. > Are there any workarounds fr copying the table as a whole object? You probably want to use pg_dump dbname -t tablename and edit the file thus created by hand to create a new table etc... ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] need some help with a delete statement
On Fri, 27 Jun 2003, Matthew Hixson wrote:
> Hi, I have a bunch of records that I need to delete from our database.
> These records represent shopping carts for visitors to our website.
> The shopping carts I'd like to delete are the ones without anything in
> them. Here is the schema:
>
> create sequence carts_sequence;
> create table carts(
> cart_id integer default nextval('carts_sequence') primary key,
> cart_cookie varchar(24));
>
> create sequence cart_contents_sequence;
> create table cart_contents(
> cart_contents_id integer default nextval('cart_contents_sequence')
> primary key,
> cart_id integer not null,
> content_id integer not null,
> expire_time timestamp);
>
> I'm trying to use this query to delete the carts that are not
> referenced from the cart_contents table.
>
> delete from carts where cart_id in (select cart_id from carts except
> (select distinct cart_id from cart_contents));
>
> My dev machine is running Postgres 7.3.3 and is a 550Mhz Titanium
> running MacOS X 10.2.6. It has 1GB of RAM. I have 266777 entries in
> v_carts and only 3746 entries in v_cart_contents. Clearly there are a
> very large number of empty carts. Running the delete statement above
> runs for over 15 minutes on this machine. I just cancelled it because
> I want to find a faster query to use in case I ever need to do this
> again. While the query is running the disk does not thrash at all. It
> is definitely CPU bound.
>Limiting the statement to 1 item takes about 12 seconds to run:
>
> delete from carts where cart_id in (select cart_id from carts except
> (select distinct cart_id from cart_contents) limit 1);
> Time: 12062.16 ms
While in() is notoriously slow, this sounds more like a problem where your
query is having to seq scan due to mismatching or missing indexes.
So, what kind of index do you have on cart_id, and what happens if you:
select cart_id from carts except
(select distinct cart_id from cart_contents) limit 1;
then feed the cart_id into
explain analyze delete from carts where cart_id=id_from_above;
from psql?
Is cart_id a fk to another table (or is another table using it as a fk?)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] select column from table
On Mon, 30 Jun 2003, Abdul Wahab Dahalan wrote: > Hi everybody! > > I just want to know is there a way in SQL command that can handle column > selection?. > The case is : > If I've a table with 10 columns and I want to select 8 of them without > rewrite the columns/fields name in the SQL query? > eg : Current SQL query is :- Select a,b,c,d,e,f,g,h from abctable; - > greps 8 columns >Can SQL do something like Select * from abctable without i,j ? - > greps all 8 columns? Not really like that, but you can create a view: create view test as selct a,b,c,d,e,f,g,h from table then select * from test; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] need some help with a delete statement
On Mon, 30 Jun 2003, Matthew Hixson wrote:
> On Monday, June 30, 2003, at 05:06 AM, scott.marlowe wrote:
>
> > On Fri, 27 Jun 2003, Matthew Hixson wrote:
> >
> >> Hi, I have a bunch of records that I need to delete from our database.
> >> These records represent shopping carts for visitors to our website.
> >> The shopping carts I'd like to delete are the ones without anything in
> >> them. Here is the schema:
> >>
> >> create sequence carts_sequence;
> >> create table carts(
> >> cart_id integer default nextval('carts_sequence') primary key,
> >> cart_cookie varchar(24));
> >>
> >> create sequence cart_contents_sequence;
> >> create table cart_contents(
> >> cart_contents_id integer default nextval('cart_contents_sequence')
> >> primary key,
> >> cart_id integer not null,
> >> content_id integer not null,
> >> expire_time timestamp);
> >>
> >> I'm trying to use this query to delete the carts that are not
> >> referenced from the cart_contents table.
> >>
> >> delete from carts where cart_id in (select cart_id from carts except
> >> (select distinct cart_id from cart_contents));
> >>
> >> My dev machine is running Postgres 7.3.3 and is a 550Mhz Titanium
> >> running MacOS X 10.2.6. It has 1GB of RAM. I have 266777 entries in
> >> v_carts and only 3746 entries in v_cart_contents. Clearly there are a
> >> very large number of empty carts. Running the delete statement above
> >> runs for over 15 minutes on this machine. I just cancelled it because
> >> I want to find a faster query to use in case I ever need to do this
> >> again. While the query is running the disk does not thrash at all.
> >> It
> >> is definitely CPU bound.
> >>Limiting the statement to 1 item takes about 12 seconds to run:
> >>
> >> delete from carts where cart_id in (select cart_id from carts except
> >> (select distinct cart_id from cart_contents) limit 1);
> >> Time: 12062.16 ms
> >
> > While in() is notoriously slow, this sounds more like a problem where
> > your
> > query is having to seq scan due to mismatching or missing indexes.
> >
> > So, what kind of index do you have on cart_id,
>
> Its is a btree index.
>
> Table "public.carts"
> Column| Type |Modifiers
> -+---
> +--
> cart_id | integer | not null default
> nextval('carts_sequence'::text)
> cart_cookie | character varying(24) |
> Indexes: v_carts_pkey primary key btree (cart_id),
> cart_cart_cookie btree (cart_cookie)
>
>
> > and what happens if you:
> >
> > select cart_id from carts except
> > (select distinct cart_id from cart_contents) limit 1;
> >
> > then feed the cart_id into
> >
> > explain analyze delete from carts where cart_id=id_from_above;
> >
> > from psql?
>
> #explain analyze delete from carts where cart_id=2700;
> QUERY PLAN
>
>
> Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1 width=6)
> (actual time=162.14..162.17 rows=1 loops=1)
> Index Cond: (cart_id = 2700)
> Total runtime: 162.82 msec
> (3 rows)
what does the output of psql say if you have the /timing switch on?
>
>
> >
> > Is cart_id a fk to another table (or is another table using it as a
> > fk?)
>
> cart_id is the pk of the carts table. cart_contents also has a cart_id
> and that is the fk pointing to its entry in the carts table. There is
> nothing else using cart_id in either of those tables as a fk.
>Thanks for the reply,
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] need some help with a delete statement
On Tue, 1 Jul 2003, Matthew Hixson wrote:
>
> On Monday, June 30, 2003, at 12:00 PM, scott.marlowe wrote:
>
> > On Mon, 30 Jun 2003, Matthew Hixson wrote:
> >
> >> On Monday, June 30, 2003, at 05:06 AM, scott.marlowe wrote:
> >>
> >>> On Fri, 27 Jun 2003, Matthew Hixson wrote:
> >>>
> >>>> Hi, I have a bunch of records that I need to delete from our
> >>>> database.
> >>>> These records represent shopping carts for visitors to our website.
> >>>> The shopping carts I'd like to delete are the ones without anything
> >>>> in
> >>>> them. Here is the schema:
> >>>>
> >>>> create sequence carts_sequence;
> >>>> create table carts(
> >>>> cart_id integer default nextval('carts_sequence') primary key,
> >>>> cart_cookie varchar(24));
> >>>>
> >>>> create sequence cart_contents_sequence;
> >>>> create table cart_contents(
> >>>> cart_contents_id integer default nextval('cart_contents_sequence')
> >>>> primary key,
> >>>> cart_id integer not null,
> >>>> content_id integer not null,
> >>>> expire_time timestamp);
> >>>>
> >>>> I'm trying to use this query to delete the carts that are not
> >>>> referenced from the cart_contents table.
> >>>>
> >>>> delete from carts where cart_id in (select cart_id from carts except
> >>>> (select distinct cart_id from cart_contents));
> >>>>
> >>>> My dev machine is running Postgres 7.3.3 and is a 550Mhz Titanium
> >>>> running MacOS X 10.2.6. It has 1GB of RAM. I have 266777 entries
> >>>> in
> >>>> v_carts and only 3746 entries in v_cart_contents. Clearly there
> >>>> are a
> >>>> very large number of empty carts. Running the delete statement
> >>>> above
> >>>> runs for over 15 minutes on this machine. I just cancelled it
> >>>> because
> >>>> I want to find a faster query to use in case I ever need to do this
> >>>> again. While the query is running the disk does not thrash at all.
> >>>> It
> >>>> is definitely CPU bound.
> >>>>Limiting the statement to 1 item takes about 12 seconds to run:
> >>>>
> >>>> delete from carts where cart_id in (select cart_id from carts except
> >>>> (select distinct cart_id from cart_contents) limit 1);
> >>>> Time: 12062.16 ms
> >>>
> >>> While in() is notoriously slow, this sounds more like a problem where
> >>> your
> >>> query is having to seq scan due to mismatching or missing indexes.
> >>>
> >>> So, what kind of index do you have on cart_id,
> >>
> >> Its is a btree index.
> >>
> >> Table "public.carts"
> >> Column| Type |Modifiers
> >> -+---
> >> +--
> >> cart_id | integer | not null default
> >> nextval('carts_sequence'::text)
> >> cart_cookie | character varying(24) |
> >> Indexes: v_carts_pkey primary key btree (cart_id),
> >> cart_cart_cookie btree (cart_cookie)
> >>
> >>
> >>> and what happens if you:
> >>>
> >>> select cart_id from carts except
> >>> (select distinct cart_id from cart_contents) limit 1;
> >>>
> >>> then feed the cart_id into
> >>>
> >>> explain analyze delete from carts where cart_id=id_from_above;
> >>>
> >>> from psql?
> >>
> >> #explain analyze delete from carts where cart_id=2700;
> >> QUERY PLAN
> >> --
> >> --
> >>
> >> Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1
> >> width=6)
> >> (actual time=162.14..162.17 rows=1 loops=1)
> >> Index Cond: (cart_id = 2700)
> >> Total runtime: 162.82 msec
> >> (3 rows)
> >
> > what does the output of psql say if you have the /timing switch on?
>
> # select cart_id from carts except (select distinct cart_id from
> cart_contents) limit 1;
> cart_id
> -
> 2701
> (1 row)
> Time: 10864.89 ms
>
> # explain analyze delete from carts where cart_id=2701;
> QUERY PLAN
>
>
> Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1 width=6)
> (actual time=0.50..0.52 rows=1 loops=1)
> Index Cond: (cart_id = 2701)
> Total runtime: 1.06 msec
> (3 rows)
> Time: 257.83 ms
Well, it looks like the fks are running really slow, which may well mean
that they are seq scanning. Examine your table definition and make sure
that they are the same types on both ends, and if not, recreate the table
so that they are either the same types or one is coerced to the other when
referencing it.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] need some help with a delete statement
On Tue, 1 Jul 2003, Matthew Hixson wrote:
>
> On Tuesday, July 1, 2003, at 05:47 AM, scott.marlowe wrote:
> >>>
> >>> what does the output of psql say if you have the /timing switch on?
> >>
> >> # select cart_id from carts except (select distinct cart_id from
> >> cart_contents) limit 1;
> >> cart_id
> >> -
> >> 2701
> >> (1 row)
> >> Time: 10864.89 ms
> >>
> >> # explain analyze delete from carts where cart_id=2701;
> >> QUERY PLAN
> >> --
> >> --
> >>
> >> Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1
> >> width=6)
> >> (actual time=0.50..0.52 rows=1 loops=1)
> >> Index Cond: (cart_id = 2701)
> >> Total runtime: 1.06 msec
> >> (3 rows)
> >> Time: 257.83 ms
> >
> > Well, it looks like the fks are running really slow, which may well
> > mean
> > that they are seq scanning. Examine your table definition and make
> > sure
> > that they are the same types on both ends, and if not, recreate the
> > table
> > so that they are either the same types or one is coerced to the other
> > when
> > referencing it.
>
> Here are my table definitions.
>
> # \d v_carts;
> Table "public.carts"
> Column| Type |Modifiers
> -+---
> +--
> cart_id | integer | not null default
> nextval('carts_sequence'::text)
> cart_cookie | character varying(24) |
> Indexes: carts_pkey primary key btree (cart_id),
> cart_cart_cookie btree (cart_cookie)
>
> # \d cart_contents;
>Table "public.cart_contents"
>Column |Type |
> Modifiers
> --+-
> +--
> cart_contents_id | integer | not null default
> nextval('cart_contents_sequence'::text)
> cart_id | integer | not null
> content_id | integer | not null
> expire_time | timestamp without time zone |
> Indexes: cart_contents_pkey primary key btree (cart_contents_id),
> cart_contents_cart_id btree (cart_id),
> cart_contents_content_id btree (content_id)
>
>
> The fk cart_contents.cart_id points to the pk carts.cart_id, and they
> are both integers.
Try reindexing cart_contents_pkey and carts_pkey and see if that helps.
You may have index growth problems. Just guessing.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Weird order problem (ignoring whitespaces?)
What locales are the two databases initdb'd to? On Wed, 2 Jul 2003 [EMAIL PROTECTED] wrote: > Hello, > > I used to run a program on a box with postgres 7.1 > There was data like: > 'abc 234' > 'abc 1234' > > (not the extra blank before 234) > > so after a select * from x order by field i got > 'abc 234' > 'abc 1234' > > but since this program runs on a 7.3 postgres version > i get. > > 'abc 1234' > 'abc 234' > > it seems that postgres ignores the blank. > > What can i do to get it work again? > > Thanx, > Heiko Irrgang > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] need some help with a delete statement
On Wed, 2 Jul 2003, Matthew Hixson wrote: > Thanks for all your help, Scott. A friend of mine whipped out this > script which runs a lot faster than trying to use the original query I > posted. >-M@ > > begin; > create temporary table cart_temp as select distinct a.cart_id, > a.cart_cookie from v_carts a, v_cart_contents b where a.cart_id = > b.cart_id; > delete from v_carts; > insert into v_carts (cart_id, cart_cookie) select cart_id, cart_cookie > from cart_temp order by cart_id; > drop table cart_temp; > commit; > vacuum analyze verbose v_carts; > reindex table v_carts; Good ole batch processing. :-) Glad you got it working. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Inquiry From Form [pgsql]
On Thu, 3 Jul 2003, Bruno Wolff III wrote: > On Wed, Jul 02, 2003 at 16:26:09 -0300, > Chris Schneider <[EMAIL PROTECTED]> wrote: > > I know this is basic, but couldn\'t find and in a hurry to know the answer. When > > interfacing with PostgreSQL through PSQL, it appears that DML statements are > > auto-commited, that is, a change I make in one session is seen from another > > without the original session issueing a COMMIT. Is this a result of PSQL > > interface and if so, can it be turned off. Is PostgreSQL transactional in the > > sense that I can issue several DMLs and then ROLLBACK. If so, how. Thanks and > > sorry for the newbie question. > > Autocommit is the default mode for psql. Use "begin;" to start a transaction. > And use "end;" to end a transaction. Note that unlike in sqlplus with Oracle, > any errors while in the transaction will abort it and you have to start > over. > > All DML and most DDL statements are rollbackable by 7.3. I believe in 7.4 > all DDL statements will be rollbackable. Except drop database. That's one that'll probably never be rollbackable ;^) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] sort for ranking
I'm gonna guess you stored your ranking as a "text" field, but now you'd
like to treat it like an int / numeric.
While it would be better to go ahead and convert it, you can always cast
it:
select * from table order by textfield::int;
On Mon, 7 Jul 2003, Andreas Schmitz wrote:
>
> Hello *,
>
> I have a little problem that confuses me. We are gathering values from a table
> as a sum to insert them into another table. I also need to get a ranking at
> insert (i.e. Highest points will get first place and so on). I tried ton
> invole a sequence to qualify the ranking by select at insert.
>
> So I tried the following (smaller example)
>
> select setval('tipp_eval_seq',1);
> select sum_user,nextval('tipp_eval_seq')-1 as ranking from tbl_sums order by
> ranking desc, user_sum asc;
>
> user_sum | ranking
> --+-
>46 | 30
>45 | 26
>44 | 28
>43 | 25
>42 | 1
>41 | 2
>39 | 3
>38 | 27
>36 | 19
>35 | 18
>34 | 20
>31 | 24
>30 | 17
>29 | 15
>28 | 16
>27 | 12
>26 | 11
>25 | 23
>24 | 21
>23 | 10
>19 | 13
>16 | 9
>12 | 7
>11 | 8
>10 | 29
> 8 | 6
> 7 | 5
> 6 | 14
> 2 | 4
> 1 | 22
> (30 rows)
>
>
> As you can see, the sums are sorted correctly but the ranking is a mess. I
> recongnized that the select seems to follow primarily the internal table
> order. Is there any way to solve this nicely. Hints and solutions are
> appreciated.
>
> Thanks in advance
>
> -Andreas
>
>
>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] avoid select expens_expr(col) like unneccessary calculations
On 8 Jul 2003, Markus Bertheau wrote: > Hi, > > when you have > select expensive_expression(column), * from table offset 20 limit 40 > > can you somehow save the cost for the first 20 calculations of > expensive_expression? Might a functional or partial index work here? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] how to copy table to another database?
On Fri, 18 Jul 2003, Yudie wrote: > Hi, > Anyone know how the procedure or commands to copy table to another database. > or querying from another database if possible? If you want to copy a table from one db to another, you can use this: pg_dump dbname -t tablename |psql dbname -e If you want cross database queries, look in the /contrib/dblink dir in the tarball of postgresql ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] PostgreSQL or pl/psSQL equivalent to MS SQL Server's
On Thu, 24 Jul 2003 [EMAIL PROTECTED] wrote: > Does anyone know of the PostgreSQL or pl/psSQL equivalent to MS SQL Server's > xp_cmdshell? This is the command that allows you issue command-line > statements from within SQL, e.g., you would do xp_cmdshell 'dir c:\' if you > wanted to see the contents of the c: drive. That can only be done inside an untrusted procedural language. plpgsql is trusted, so it can't do that type of thing. C, pltclu, plprelu, and, as of 7.4 plpython will let you do that. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Canceling other backend's query
On Wed, 27 Aug 2003, daq wrote: > Hi, > > Can i cancel querys runing on other backends, or disconnect a client > from the server? I can kill the backend process, but sometimes this > causing shared memory troubles. If you kill -9 a backend, you will cause the shared memory problem. Try just a plain kill . That should work without causing shared memory to dump. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Fw: Change column data type
On Tue, 2 Sep 2003, Kumar wrote: > > Dear Friends, > > Using Postgres 7.3.4 over the linux server 7.3. > > Is it possible to alter/change the data type of a existing table's > column, with out dropping and recreating a column of same name. Only for certain types, and only by hacking the system catalogs, which is a procedure fraught with danger. i.e. backup all your data, then proceed with caution. Generally, it's best to create a new column and put the data in there and drop the old column. Note you can do this in a transaction, so you can roll it back should you realize you've made some kind of error. begin; alter table t1 add column c1 int8; update t1 set c1=c2; alter table t1 drop column c2; commit; vacuum; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] undefine currval()
On Mon, 8 Sep 2003, Bruce Momjian wrote: > I don't know how you could have an application that doesn't know if it > has issued a nextval() in the current connection. Unless you can explain > that, we have no intention of playing tricks with currval() for > connection pooling. Actually, I would think the very act of using connection pooling would ensure that applications may well not know whether or not a nextval had been called. In other words, how is an application supposed to know if the previous bit of code that used this connection issued a nextval() when you're connection pooling and any piece of code could have run before you. On the other hand, using currval as a test to see if a value has been used is probably not the best way of doing things either. I'd imagine some kind of static or session var would be better suited to that task. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] undefine currval()
On Mon, 8 Sep 2003, Tom Lane wrote: > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > On Mon, 8 Sep 2003, Bruce Momjian wrote: > >> I don't know how you could have an application that doesn't know if it > >> has issued a nextval() in the current connection. Unless you can explain > >> that, we have no intention of playing tricks with currval() for > >> connection pooling. > > > Actually, I would think the very act of using connection pooling would > > ensure that applications may well not know whether or not a nextval had > > been called. > > The point is that it's not very sensible to be using currval except > immediately after a nextval --- usually in the same transaction, I would > think. I'm pretty sure my second paragraph agreed with you on that. > Certainly, not resetting currval implies that there is > *potential* coupling between different transactions that happen to share > a connection. But ISTM that such coupling would represent a bug in the > application. And that one too. > Chris said he was using currval being undefined to know that no rows > were inserted, but this seems less than compelling to me (why not look > at the results of the insert commands you used?). I'd support adding a > currval-reset feature if someone can make a more compelling argument why > a connection-pooling application would need it. I'd say that if someone is looking at that, it would be better to have some kind of reset_connection call that makes a connection look like you just established it. Bit I'd never use it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] virus warning
On Fri, 19 Sep 2003, Paul Thomas wrote: > > On 19/09/2003 07:17 Tomasz Myrta wrote: > > Hi > > Recently I receive massive mail attack. This attack comes from some > > postgresql mailing list users. All send-to adresses are taken from users > > mailboxes which contain postgresql posts. Currently I found two kinds of > > viruses: > > 1. Empty post with "Undelivered message to..." body > > 2. Microsoft "Dear Customer... " based on www.microsoft.com design. > > Both mails contains some .exe attachement. > > > > Regards, > > Tomasz Myrta > > So far I've had nearly 150 of these in the last 12 hours or so. Somebody > on these lists has a lot of explaining to do! Fortunately my spam filters > are up to scratch and I run Linux :) Others may not be so lucky. Keep in mind, if you check the headers on the emails you'll see that they are forged. I've been getting about 20 emails a day telling me a message I know I didn't send was infected with a virus. I got 432 last night inbound, some with names forged from this list, others from names unknown. But I don't think it's not the folks on this list, I think it's a windows worm that looks in people's email, harvests names at random, and forged email based on it. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Using sql statements in file
On Mon, 22 Sep 2003, Suresh Basandra wrote: > Hi, > > I would like to do the following using files: > > 1. put create database, create tables sql statements in a file and > execute through prompt > 2. insert or update data that is put in a file > > Please let me know if there are any examples that lists how sql > statements can be put in a file and used. psql dbnamehere http://archives.postgresql.org
Re: [SQL] RFC: i18n2ascii(TEXT) stored procedure
On Thu, 25 Sep 2003, Michael A Nachbaur wrote: > I've created the following stored procedure to allow me to do > international-insensitive text searches, e.g. a search for "Resume" would > match the text "Résumé". > > I wanted to know: > > a) am I missing any characters that need to be converted? My first (and only > language) is English, so I'm in the dark when that is concerned; > b) is there a better and/or faster way of implementing this? I don't want > searches to bog down (at least too badly) as a result of this. > > CREATE OR REPLACE FUNCTION i18n2ascii (TEXT) RETURNS TEXT AS ' > my ($source) = @_; > $source =~ > tr/áàâäéèêëíìîïóòôöúùûüÁÀÂÄÉÈÊËÍÌÎÏÓÒÔÖÚÙÛÜ//; > return $source; > ' LANGUAGE 'plperl'; You could probably accomplish the same thing without using perl via the built in function translate(). Look in the functions-string.html in the 7.3.x documentation. Also, the regex version of substring() is quite powerful. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Result set granularity..
On Sat, 27 Sep 2003, Rasmus Aveskogh wrote:
>
> Hi,
>
> Since I went from Oracle to PostgreSQL I've been missing the "invisable"
> column 'rownum'. I often used it to lower the granularity of my data.
> For example, say I have a large table containing some sort of statistical
> data and want to plot a graph using it.
> If the graph is 600 pixels wide I might as well lower the granularity of
> my incoming data to 600 measure points before plotting.
>
> In Oracle I used to do this by using the modulus operator on the rownum
> column as a restriction.
>
> SELECT FROM WHERE mod(rownum, 5) = 0;
>
> The query above would give me every fifth row of the original result set
> and would save me from sending the data over my database connection and do
> the lowering of the granularity in the application.
>
> I have two questions, one dependent on the answer on the other one..
>
> 1) Is it possible to achieve this any other _easy_ way?
> Perhaps it would be feasible to write a wrapper using a counter which
> makes the SELECT and then return every fifth row to the SELECT calling the
> wrapper. But then I assume the data still has to "travel" one step which
> puts on some overhead.
The standard "trick" in Postgresql is to create a temporary sequence and
select that as well:
create temp sequence aaa;
postgres=# select *, nextval('aaa') from accounts;
to get a row number. The sequence creation and use is pretty fast, and
I've used it before.
>
> 2) Would it be possible to add one more limit argument to the non-standard
> set of LIMIT and OFFET - a 'SCALE n' or 'GRANUL n' argument which would
> return every n row of the initial result set. I think that would be
> gladly accepted for folks working with statistical data.
Using the same trick, you can get every 5th row like this:
select * from (select *, nextval('aaa') as row from accounts) as a where a.row%5=4;
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [PERFORM] sql performance and cache
On Tue, 14 Oct 2003, Wei Weng wrote: > On Sat, 11 Oct 2003, Christopher Kings-Lynne wrote: > > > > > > I have two very similar queries which I need to execute. They both have > > > exactly the same from / where conditions. When I execute the first, it takes > > > about 16 seconds. The second is executed almost immediately after, it takes > > > 13 seconds. In short, I'd like to know why the query result isn't being > > > cached and any ideas on how to improve the execution. > > > > > > > > > OK - so I could execute the query once, and get the maximum size of the > > > array and the result set in one. I know what I am doing is less than optimal > > > but I had expected the query results to be cached. So the second execution > > > would be very quick. So why aren't they ? I have increased my cache size - > > > shared_buffers is 2000 and I have doubled the default max_fsm... settings > > > (although I am not sure what they do). sort_mem is 8192. > > > > PostgreSQL does not have, and has never had a query cache - so nothing > > you do is going to make that second query faster. > > > > Perhaps you are confusing it with the MySQL query cache? > > > > Chris > > > Is there plan on developing one (query cache)? Not really, Postgresql's design makes it a bit of a non-winner. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query planner: current_* vs. explicit date
I'd guess that the planner doesn't know what current_date::timestamp is ahead of time, so it chooses a seq scan. On Wed, 22 Oct 2003, Chris Gamache wrote: > PsotgreSQL 7.2.4: > > Query planner is behaving strangely. It operates differently for explicit dates > and derived dates... any ideas on why? ( and why I might not have noticed this > before... ) > > CREATE TABLE trans_table ( > id serial, > user_name varchar(50), > trans_type varchar(50), > trans_data varchar(50), > trans_date timestamptz, > trans_uuid uniqueidentifier, > CONSTRAINT trans_table_pkey PRIMARY KEY (id) > ) WITH OIDS; > > ... Insert lots of data ... > > CREATE INDEX trans_table_date_idx ON trans_table USING btree (trans_date); > CREATE INDEX trans_table_user_date_idx ON trans_table USING btree > (user_name,trans_date); > CREATE INDEX trans_table_uuid_idx ON trans_table USING btree (trans_uuid); > > VACUUM ANALYZE trans_table; > > EXPLAIN SELECT id FROM trans_table WHERE trans_date >= current_date::timestamp; > Seq Scan on trans_table (cost=0.00..177369.52 rows=315267 width=4) > > EXPLAIN SELECT id FROM trans_table WHERE trans_date >= '10/22/2003 00:00:00 > AM'::timestamp; > Index Scan using trans_table_date_idx on trans_table (cost=0.00..1474.69 > rows=417 width=4) > > CG > > __ > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > http://shopping.yahoo.com > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Query Help
On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > I'm interested in finding the minimim A.id such that the following holds: > > select A.charge > , B.user_id > , C.employee_id > from A > inner join B using (user_id) > inner join C using (employee_id) > > except > > select X.charge > , Y.user_id > , Z.employee_id > from X > inner join Y using (user_id) > inner join Z using (employee_id) > > -- > > I can't do the following, since the number of selected columns have to match: > > select A.id > , A.charge > , B.user_id > , C.employee_id > from A > inner join B using (user_id) > inner join C using (employee_id) > > except > > select X.charge > , Y.user_id > , Z.employee_id > from X > inner join Y using (user_id) > inner join Z using (employee_id) Maybe you can add a dummy field in the second half like this: except select -1 , X.charge , Y.user_id , Z.employee_id ??? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Regular expression problem
On 24 Oct 2003, Manuel Sugawara wrote:
> Matias Surdi <[EMAIL PROTECTED]> writes:
>
> > Manuel Sugawara wrote:
> > >Use something like '^[a-z]{2}$'
> >
> > so, is this a bug
>
> No it is not. The sintax you are using is not supported.
It certainly seems to work in Postgresql 7.4 beta 4:
create table test2 (info text);
CREATE TABLE
insert into test2 values ('ab');
INSERT 109169538 1
insert into test2 values ('abc');
INSERT 109169539 1
marl8412=# select * from test2 where info ~ '^[a-z]{2}$';
info
--
ab
Or was there more to that message I wasn't getting?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Regular expression problem
On 24 Oct 2003, Manuel Sugawara wrote:
> "scott.marlowe" <[EMAIL PROTECTED]> writes:
>
> > It certainly seems to work in Postgresql 7.4 beta 4:
> >
> > create table test2 (info text);
> > CREATE TABLE
> > insert into test2 values ('ab');
> > INSERT 109169538 1
> > insert into test2 values ('abc');
> > INSERT 109169539 1
> >
> > marl8412=# select * from test2 where info ~ '^[a-z]{2}$';
> > info
> > --
> > ab
> >
> > Or was there more to that message I wasn't getting?
>
> He was trying to use '[a-z]{2,2}', which doesn't work in PostgreSQL.
Oh, ok. Thanks.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Skip dups on INSERT instead of generating an error ...
On Fri, 12 Dec 2003, Marc G. Fournier wrote: > > I need to be able to run an INSERT INTO / SELECT FROM UNION which combines > two tables into one ... *but* ... the INTO table has a primary key on the > first column, so if the result of the UNION generates dups, by default, of > course, it will generate errors ... what I'd like is to have it so that it > just skips over those records. > > First thought would be to write a quite plpgsql function that would do a > SELECT first, to see if the value already exists, and if not, then do the > INSERT ... but am wondering if maybe there is a cleaner way that I'm not > thinking of? I thought unions, by definition, couldn't create dups, unless you used the all keyword... OR do you just mean that you have dup pks, not the whole row? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Skip dups on INSERT instead of generating an error ...
On Fri, 12 Dec 2003, Marc G. Fournier wrote: > > I need to be able to run an INSERT INTO / SELECT FROM UNION which combines > two tables into one ... *but* ... the INTO table has a primary key on the > first column, so if the result of the UNION generates dups, by default, of > course, it will generate errors ... what I'd like is to have it so that it > just skips over those records. > > First thought would be to write a quite plpgsql function that would do a > SELECT first, to see if the value already exists, and if not, then do the > INSERT ... but am wondering if maybe there is a cleaner way that I'm not > thinking of? Crap, just read what you actually wrote. Hmmm. I'd guess a trigger on the table might be able to do it, but performance is gonna suck. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] restoring database
On Mon, 22 Dec 2003, Theodore Petrosky wrote: > As I am not someone with a lot of experience > (depending on the size of the 'dumpall') I would > create a new database to suck up the 'all' then > pg_dump the table I really cared about. > > Obviously, if the dump_all is a terabyte database this > method is inconvenient (to say the least). > > However it will work. In the past I've used 'cat -n filename'|grep -4 database to find the starting points of individual databases etc... and then used split to break it into the exact right size pieces to do this. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how to show table structure?
On Fri, 9 Jan 2004, Bing Du wrote: > Greetings, > > How can I see the layout of a table in PostgreSQL 7.4? I've checked > several books and on-line documents, but was not able to figure out how > PostgreSQL does 'describe ' like it's done in other databases. If in psql, use the \d commands (\? will show you all of them. However, if you've not got psql to do it, you can look through the information_schema for anything like that, like so: select * from information_schema.tables; and so on. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Adding a column to a VIEW which has dependent objects.
On Sat, 10 Jan 2004, Tom Lane wrote: > Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: > > How do people extend a parent view which has > > lot of dependent views? > > The parent view cannot be dropped because that will > > require recreating a dozen of dependent views. > > You're out of luck, you'll have to drop and remake them all. > In future we could think about some kind of ALTER VIEW ADD COLUMN > operation, but it ain't there now. > > (I suppose if you were really desperate you could think about manually > hacking the system catalogs, but this would be pretty risky on a > production database.) > > > Also is there an easy way of dumping the definitions > > of all the dependent views of a given object. > > You can chase the links in pg_depend to see what the dependent objects > are, but extracting their definitions would be a tad harder ... Note that the definitions for views are stored in pg_views as well. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] can insert 'null' into timestamp type field from command
On Tue, 13 Jan 2004, Bing Du wrote: > The table is like this: > > > maxware=# \d test; >Table "public.test" > Column |Type | Modifiers > +-+--- > a | timestamp without time zone | > b | integer | > = > > The following insert command works fine: > > maxware=# insert into test (a,b) values (null,'1'); > > But importing from an input data file does not seem to work as shown below: > > = > maxware=# copy tbl_spcase from '/home/bdu/test/input.data' delimiter as '|'; > ERROR: invalid input syntax for integer: "null" > CONTEXT: COPY tbl_spcase, line 1, column col_id_spcase: "null" > == > > The input.data file just has one line. But no matter what I did, neither > of the following input format worked. > > 1. null|1 > 2. |1 > 3. ''|1 Here's a simple way to find out. use pg_dump to dump the table: psql db=>create table test (dt timestamptz, id int); db=>insert into test (dt, id) values (NULL,22); db=>\q pg_dump db -t test -- Data for TOC entry 3 (OID 705319) -- Name: test; Type: TABLE DATA; Schema: public; Owner: marl8412 -- COPY test (dt, id) FROM stdin; \N 22 \. Note that a null is imported / exported as \N ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Sometimes referential integrity seems not to work
On Mon, 2 Feb 2004, Jan Wieck wrote: > Stephan Szabo wrote: > > > On Sat, 31 Jan 2004, Tom Lane wrote: > > > >> Stephan Szabo <[EMAIL PROTECTED]> writes: > >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> >> In a database I am working, I sometimes have to delete all the records in > >> >> some tables. According to the referential integrity defined in the creation > >> >> of the tables, postmaster should not delete the records, but it does. I have > >> >> used the following commands: "delete from table_1" and "truncate table_1". > >> >> ... > >> >> can the postgres user delete records despite referential integrity? > >> > >> I think the first PG release or two that had TRUNCATE TABLE would allow > >> you to apply it despite the existence of foreign-key constraints on the > >> table. Recent releases won't though. > > > > Yeah, truncate didn't worry me much, but the implication that delete from > > table_1; worked did. > > TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > for foreign keys. So I guess Enio is getting but ignoring the error > message when trying the delete, but then the truncate does the job in > his pre-7.3 database. Yes it can. I think it was starting in 7.3. => select * from test2; info - abc'123 123 (2 rows) => begin; BEGIN => truncate test2; TRUNCATE TABLE => rollback; ROLLBACK => select * from test2; info - abc'123 123 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Sometimes referential integrity seems not to work
On Tue, 3 Feb 2004, Jan Wieck wrote: > scott.marlowe wrote: > > > On Mon, 2 Feb 2004, Jan Wieck wrote: > > > >> Stephan Szabo wrote: > >> > >> > On Sat, 31 Jan 2004, Tom Lane wrote: > >> > > >> >> Stephan Szabo <[EMAIL PROTECTED]> writes: > >> >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> >> >> In a database I am working, I sometimes have to delete all the records in > >> >> >> some tables. According to the referential integrity defined in the creation > >> >> >> of the tables, postmaster should not delete the records, but it does. I have > >> >> >> used the following commands: "delete from table_1" and "truncate table_1". > >> >> >> ... > >> >> >> can the postgres user delete records despite referential integrity? > >> >> > >> >> I think the first PG release or two that had TRUNCATE TABLE would allow > >> >> you to apply it despite the existence of foreign-key constraints on the > >> >> table. Recent releases won't though. > >> > > >> > Yeah, truncate didn't worry me much, but the implication that delete from > >> > table_1; worked did. > >> > >> TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > >> for foreign keys. So I guess Enio is getting but ignoring the error > >> message when trying the delete, but then the truncate does the job in > >> his pre-7.3 database. > > > > Yes it can. I think it was starting in 7.3. > > Okay, so you're the third one correcting me on this. Now can any of you > violate a foreign key constraint with anything else than using truncate > in a pre-7.3 database? Because I can't do that and that was the original > problem. OK, I just tested the truncate foreign key truncate on 7.2, and other than truncate, I've not found any way to delete the fk data from the parent table. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Sometimes referential integrity seems not to work
On Tue, 3 Feb 2004, Jan Wieck wrote: > scott.marlowe wrote: > > > On Mon, 2 Feb 2004, Jan Wieck wrote: > > > >> Stephan Szabo wrote: > >> > >> > On Sat, 31 Jan 2004, Tom Lane wrote: > >> > > >> >> Stephan Szabo <[EMAIL PROTECTED]> writes: > >> >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> >> >> In a database I am working, I sometimes have to delete all the records in > >> >> >> some tables. According to the referential integrity defined in the creation > >> >> >> of the tables, postmaster should not delete the records, but it does. I have > >> >> >> used the following commands: "delete from table_1" and "truncate table_1". > >> >> >> ... > >> >> >> can the postgres user delete records despite referential integrity? > >> >> > >> >> I think the first PG release or two that had TRUNCATE TABLE would allow > >> >> you to apply it despite the existence of foreign-key constraints on the > >> >> table. Recent releases won't though. > >> > > >> > Yeah, truncate didn't worry me much, but the implication that delete from > >> > table_1; worked did. > >> > >> TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > >> for foreign keys. So I guess Enio is getting but ignoring the error > >> message when trying the delete, but then the truncate does the job in > >> his pre-7.3 database. > > > > Yes it can. I think it was starting in 7.3. > > Okay, so you're the third one correcting me on this. Now can any of you > violate a foreign key constraint with anything else than using truncate > in a pre-7.3 database? Because I can't do that and that was the original > problem. Our production machine is running 7.2, and I get this: begin; BEGIN =# truncate test; ERROR: TRUNCATE TABLE cannot run inside a transaction block =# commit; COMMIT =# select * from test; info | id --+ abc | 1 def | 2 (2 rows) So, at least in 7.2, it won't let me truncate. I'm not running any 7.3 boxes, just 7.4 and 7.2, so I can't test it on 7.3. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL]
On Tue, 3 Feb 2004, Prashanthi Muthyala wrote: > Hi > > I have a postgresql in my red hat linux machine which will be our webserver. > previously we had mysql in suse linux in another machine. so we are having > a new webserver now with postgresql. I want to transfer my tables in mysql > to postgresql so that I continue with my stuff in the new server. > > I tried to search for the transfer of tables from mysql to postgresql > located on different machines, but could not figure out exactly how to > start and proceed. > > could you please guide me through this process Download the postgresql source package, untar it, and look in the /contrib/mysql directory, or go to: http://ziet.zhitomir.ua/~fonin/code/ and download the latest version there. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] techniques for manual ordering of data ?
On Sat, 7 Feb 2004, Peter Galbavy wrote: > I tried googling, but the keywords here are so general, that I couldn't find > much useful. > > I am looking for reommendations on how to build an ordered list of media (in > this case photographs) in a particular "context". What I have - which I have > somewhat simplified maually - is: > > CREATE TABLE contexts ( > photo_idint8 REFERENCES photos ..., > catalogue_idint8 REFERENCES catalogues ..., > collection_id int8 REFERENCES collections ..., > > orderingint > ); > > Here, a photograph may appear in multiple catalogue/collection places. What > I want to do is to allow the admin to reorder the display by using + / - > style buttons. My initial thought was to use an arbitrary "ordering" column, > like old BASIC line numbers, which would be initially allocated in 10s or > 100s, and then updating the value via the UI and occassionally applying a > "renum" style function when the list gets uneven. > > To avoid performance hits through too many updates, I am trying to avoid the > admin UI from UPDATEing all the rows in a specific catalogue/collection > every time a button is pressed - both the SQL and the subsequent fragmented > table. Hmm. > > Anyone got any "prior art" or suggestions on how they acheive this ? > > Note that I am NOT including ordering based on an photograph specific > metadata like time or location. That's another story and another interface. I've got several ideas on how to do it. however, since I'm a consumate liar, you'd not want to listen to anything I had to say. good day. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
On Tue, 17 Feb 2004, Tom Lane wrote: > elein <[EMAIL PROTECTED]> writes: > > This is an example of the problem. It used to expand > > the middle thing to 15. > > > elein=# select 'x' || ' '::char(15) || 'x'; > > ?column? > > -- > > xx > > (1 row) > > Still does, but then the spaces go away again when the value goes into > the concatenation, because concatenation is a text operator. But then this: select 'x'||' '||'x' should produce xx, but it produces x x. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Jeremy Smith wrote:
> Hi,
>
> in mysql I was able to make an insert such as:
>
> INSERT INTO TABLE (integervariable) VALUES ('')
>
> and have it either insert that variable, or insert the default if it had
> been assigned. In postgresql it gives and error every time that this is
> attempted. Since I have so many queries that do this on my site already, is
> there any way to set up a table so that it just accepts this sort of query?
First off, the reason for this problem is that Postgresql adheres to the
SQL standard while MySQL heads off on their own, making it up as they go
along. This causes many problems for people migrating from MySQL to
almost ANY database.
Phew, now that that's out of the way, here's the standard ways of doing
it.
Use DEFAULT: If no default is it will insert a NULL, otherwise the
default will be inserted:
insert into table (integervar) values (DEFAULT);
OR
Leave it out of the list of vars to be inserted
insert into table (othervars, othervars2) values ('abc',123);
OR
Insert a NULL if that's what you want:
insert into table (integervar) values (NULL);
Note that NULL and DEFAULT are not quoted.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Jeremy Smith wrote:
> Scott,
>
> I understand that MySQL's adherence to the standards must be lazy as I am
> running into frequent issues as I transfer my site. Unfortunately I have
> over 2500 queries, and many more of them needed to be rewritten than I ever
> would have imagined. I guess MySQL is the IE of open source DB, and
> PostgreSQL is Netscape / Mozilla, in more ways than one.
Good comparison.
> I guess in some sense, since I relied on MySQL's laziness, my code also
> became a bit lazy. There are many locations where I accept user input from
> a form, and then have a process page. And on that process page I might have
> hundreds of variables that look like:
>
> $input = $_POST['input'];
>
> and in the old days, if that was an empty value and inserted into a mysql
> query, it would just revert to the default. Now it looks like I need to:
>
> $input = $_POST['input'];
> if (!$input) {
> $input = DEFAULT;
> }
I've run into this kind of thing before. IT helps if you have an array of
all your fields like:
$fields = array("field1","field3","last_name");
and then you can foreach across the input:
foreach($fields as $f){
if (!$_POST[$f]){
$_POST[$f]='DEFAULT';
} else {
$_POST[$f] = "'".$_POST[$f]."'";
}
}
> over and over and over and over :) I guess I am just looking for a
> shortcut since the site conversion has already taken a week and counting,
> when I originally was misguided enough to think it would take hours.
Well, you might find yourself rewriting fair portions of your site, but
usually you wind up with better code and better checking, so it's a bit of
a trade off.
> Anyway, the help on this list is much appreciated..
>
> Jeremy
>
> -Original Message-
> From: scott.marlowe [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 18, 2004 2:44 PM
> To: Jeremy Smith
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] Inserting NULL into Integer column
>
>
> On Wed, 18 Feb 2004, Jeremy Smith wrote:
>
> > Hi,
> >
> > in mysql I was able to make an insert such as:
> >
> > INSERT INTO TABLE (integervariable) VALUES ('')
> >
> > and have it either insert that variable, or insert the default if it had
> > been assigned. In postgresql it gives and error every time that this is
> > attempted. Since I have so many queries that do this on my site already,
> is
> > there any way to set up a table so that it just accepts this sort of
> query?
>
> First off, the reason for this problem is that Postgresql adheres to the
> SQL standard while MySQL heads off on their own, making it up as they go
> along. This causes many problems for people migrating from MySQL to
> almost ANY database.
>
> Phew, now that that's out of the way, here's the standard ways of doing
> it.
>
> Use DEFAULT: If no default is it will insert a NULL, otherwise the
> default will be inserted:
> insert into table (integervar) values (DEFAULT);
>
> OR
>
> Leave it out of the list of vars to be inserted
> insert into table (othervars, othervars2) values ('abc',123);
>
> OR
>
> Insert a NULL if that's what you want:
>
> insert into table (integervar) values (NULL);
>
> Note that NULL and DEFAULT are not quoted.
>
>
>
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Jeremy Smith wrote:
>
>
> I've run into this kind of thing before. IT helps if you have an array of
> all your fields like:
>
> $fields = array("field1","field3","last_name");
>
> and then you can foreach across the input:
>
> foreach($fields as $f){
> if (!$_POST[$f]){
> $_POST[$f]='DEFAULT';
> } else {
> $_POST[$f] = "'".$_POST[$f]."'";
> }
> }
>
> Wow, great idea. I will definitely do this, thanks alot.
>
>
>
> Well, you might find yourself rewriting fair portions of your site, but
> usually you wind up with better code and better checking, so it's a bit of
> a trade off.
>
>
> No doubt that this is true. Of course even without the better code and
> error checking, the extra features like stored procedures and automatic row
> locking was more than enough to make the switch worth it.
>
> Thanks again!
You're welcome! Enjoy getting to know Postgresql and all the great folks
on the lists, I know I have.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Rod Taylor wrote:
> > and then you can foreach across the input:
> >
> > foreach($fields as $f){
> > if (!$_POST[$f]){
> > $_POST[$f]='DEFAULT';
> > } else {
> > $_POST[$f] = "'".$_POST[$f]."'";
> > }
> > }
>
> Default in quotes isn't going to work, and please tell me you escape
> those things with pg_escape_string() at some point.
Note that the ' marks aren't part of the string, they are the delimiter of
the string, and I always run every server with magic_quotes_gpc on.
anything else? :-)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Rod Taylor wrote: > > Note that the ' marks aren't part of the string, they are the delimiter of > > the string, and I always run every server with magic_quotes_gpc on. > > > > anything else? :-) > > Good point. I looked at the single quotes of the second line and somehow > the DEFAULT got quoted as well ;) Oh, and I'm stuck using add_slashes (or the magic_quotes_gpc thingie) 'cause I'm on a server that's being eoled in favor of .net, and it's running PHP 4.0.6... ugh. We really gotta get it upgraded soon. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Distributed Transactions
On Wed, 18 Feb 2004, George A.J wrote: > Hi all, > > i am using PostgreSQL 7.3.2. How can i do distributed transactions in PostgreSQL. > is there a transaction coordinator available for Postgres.. there isn't one, really, but you could likely roll something that worked. Oh, and update your version of postgresql. There's a nasty "won't startup" bug in 7.3.2 you'd just as soon rather avoid. It doesn't lose data, but there's nothing like restarting your database only to have it complain about some boundary condition in the write ahead log and then have to spend an hour or so with it down while you download the updates to get it working. 7.3.5 is the latest version of the 7.3 branch, but it looks like rpm wise, the latest on the sites is 7.3.4. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] DISTINCT ON troubles
On Thu, 19 Feb 2004, Brian Knox wrote:
> ( sorry if this is a repeat, my mail server is being wonky today )
>
> I'm looking for a way, within SQL, given a starting date and an ending
> date, to get back the number of months between the start and end date.
> If I "SELECT end_date - start_date", I get back an interval in days; I
> need months.
Maybe date_part?
select date_part('month','2004-08-02 12:00:00'::timestamp) -
date_part('month','2004-05-01 12:00:00'::timestamp);
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] DISTINCT ON troubles
On Thu, 19 Feb 2004, scott.marlowe wrote:
> On Thu, 19 Feb 2004, Brian Knox wrote:
>
> > ( sorry if this is a repeat, my mail server is being wonky today )
> >
> > I'm looking for a way, within SQL, given a starting date and an ending
> > date, to get back the number of months between the start and end date.
> > If I "SELECT end_date - start_date", I get back an interval in days; I
> > need months.
>
> Maybe date_part?
>
> select date_part('month','2004-08-02 12:00:00'::timestamp) -
> date_part('month','2004-05-01 12:00:00'::timestamp);
Note that I think you need a +1 at the end of that...
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Date Foo.
I'm still not sure what you were looking for.
If you have, say, March 16th, and the next date is August 23rd, do you
want to count March, April, May, Jun, July, August = 6? Or do you want to
count the number of 30 day periods?
Using date_part gets you the first one.
Plus, since months can be 28, 29, 30, or 31 days long, how exactly does
one convert 58 days to months? Without knowing the month the start and
end dates have, you wouldn't know for sure if it was two or three. and
what about parts of months, a week in the end of march, all of april, and
the first two weeks of May, is that two months or three?
I guess my point is that I'm not sure what question you were asking, so
I'm not sure how Perl's Date::Calc did better or worse than would
postgresql.
On Fri, 20 Feb 2004, Brian Knox wrote:
> Sorry, Not looking for a way to extract a month from a timestamp. I'm
> looking for a way to convert an interval from days to months. I'm not
> sure after digging into it that there is a way to handle it in SQL, as
> the interval that results from subtracting one timestamp from another is
> not away of what months the interval spans, so there'd be no proper way
> to take month lengths into account.
>
> I gave up on the sql and used Date::Calc from Perl. Thanks for your
> answer anyway.
>
> scott.marlowe wrote:
> > On Thu, 19 Feb 2004, Brian Knox wrote:
> >
> >
> >>( sorry if this is a repeat, my mail server is being wonky today )
> >>
> >>I'm looking for a way, within SQL, given a starting date and an ending
> >>date, to get back the number of months between the start and end date.
> >>If I "SELECT end_date - start_date", I get back an interval in days; I
> >>need months.
> >
> >
> > Maybe date_part?
> >
> > select date_part('month','2004-08-02 12:00:00'::timestamp) -
> > date_part('month','2004-05-01 12:00:00'::timestamp);
> >
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] randomized order in select?
On Wed, 10 Mar 2004, Enver ALTIN wrote: > Hi, > > I have got a simple table like this: > > create table tips ( > id integer primary key unique, > tiptext text > ); > > and, I've got a website where I'm willing to show these tips in a random > order. Each visitor will get a randomly selected tip. So for now, I have > to do 2 queries: > > select id from tips > > collect an ID list, choose one randomly and retrieve it. I wish I could > do something like: > > select tiptext from tips order by random limit 1 You mean like: select * from table order by random(); That Works as far back as 7.2, maybe before. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Alter table
On Wed, 10 Mar 2004, David wrote: > Ok another very newbie question. How can i change the data type a column can > accept? at the moment it will only take character(7) i want to change it to > varchar(30), but i cant figure how, ideas? While there are ways to tinker with the system catalogs to change between different text types / lengths, none of these are "officially supported" and may well screw up your database if you do something wrong. I believe the archives likely have this question over and over in them. The proper way to do this is to make a new column, put the old column in it, and then drop the old column: create table test (a char(7)); insert a few thousand lines to test...; begin; alter table test add column b varchar(30); update test set b=a; alter table test drop column a; commit; (or rollback; should things go horribly wrong...) vacuum full test; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] randomized order in select?
On Thu, 11 Mar 2004, Iain wrote: > If you have a lot of tips, you could create a unique indexed tip number > column. Select the highest tip number using: > > select tip_number from tips order by tip_number desc limit 1; > > Then generate a random number and select using that tip_number. > > Of course, you would have to allow for the possibility of missing tip > numbers, by repeating the random number generation/read sequence until you > find something. Since the tip_number isn't the PK of the table, you can > regenerate the tip numbers to eliminate holes from deletions any time you > like. Just reset the sequence to 1 and update all rows with the > nextval(tipnumber_seq). > > Sounds like a lot of work to me though... Assuming there are ten rows, you can use this: select * from table limit 1 offset random()*10; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] designer tool connect to PostgreSQL
On Thu, 11 Mar 2004 [EMAIL PROTECTED] wrote: > Hi, > > thanks to all for the responnd...i've look into all the tools mentioned > and found many tools which has a great feautre such as designing/modelling > the object/table but it seems look like that there is no tools that can do > the ETL process. thanks in advance from a google search for postgresql and etl: http://www.safe.com/news/2003/sept16_2003.htm http://cloveretl.berlios.de/ There may be others, I'm not sure. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] DB question - Merging data from one table to another.
On Thu, 1 Apr 2004, malia, sean wrote: > Hello, > > I'm not sure if this is even possible, but I'll throw it by you anyway. > > Say I have 2 tables: > > Table1: With columns number and name > > 1.1 test1 > 1.2 test2 > 1.3 test3 > 1.4 test4 > > Table2: With column number and results > > 1.1 pass > 1.2 fail > 1.3 pass > 1.4 fail > > What I would like to do is add a new column to Table2 called name and > populate the name from table 1 and add it to table 2. So, table 2 will look > like: > > 1.1 passtest1 > 1.2 failtest2 > 1.3 passtest3 > 1.4 failtest4 You may want to consider using a view to do this, especially if you don't have a 1:1 correspondence. I.e. there are a lot of entries in table2 for 1.1 etc... create view bigview as select * from table2 t2 join table1 t1 on (t2.number=t1.number); ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pgsql multi-database queries
On Thu, 8 Apr 2004, yek ching wrote: > Dear Sir/Miss, > > Is there a way to do a multi-database select query??.. I fail to find the > related solution... currently i i created seperate table call stockmaster > under seperate database for each seperate organizations... When i want to > queries out the stock item qty for multi database, i face a problem where > sql only can work for single database.. > > How can i use the same SQL statement in Pgsql??.. > "SELECT * FROM A.stockmaster,B.stockmaster where A.item=B.item" Look in the contrib/dblink directory in the source tar ball. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] full join...using and version of pgsql
On Wed, 14 Apr 2004, Tina Messmann wrote: > hello list, > > i am using pgsql 7.2.1 > i need something like > > select * from t1 full join t2 using(bla) full join t3 using(bla) > > i searched the archives and found that this is not possible with my > version of postgres. > is this implemented in newer versions of postgres? since which version? > > i'll try the alternative select * from t1 full join t2 on(t1.bla=t2.bla) > full join t3 on(t1/t2.bla=t3.bla) > but this is not the best solution for me i think I think you are looking for a natural join. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Update is very slow on a bigger table
On Sat, 17 Apr 2004, Bruno Wolff III wrote: > On Fri, Apr 16, 2004 at 12:47:58 -0500, > Yudie <[EMAIL PROTECTED]> wrote: > > Hi guys, > > I would like to create a trigger that execute some programs, > > is there a way to create a plpgsql function that execute shell command or > > any other way to run a program? > > Thanks for your help. > > Only untrusted languages can be used to run external programs. perlu might > be something you can use. Also, take a look at plsh if all you need is to run shell functions: http://developer.postgresql.org/~petere/pgplsh/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SQL script
On Thu, 15 Apr 2004 [EMAIL PROTECTED] wrote: > good day toall.. > imm running postgreSQL on linux. > my main goal is to create a script that will dump a data from our server too my > local PC. the content of the script would be execute sql command in one shot: > 1. dump data > 2. update and insert some rows to selected table. > > how can i accommplish this task and also how can i run this script? is there any one > ccan help me. any idea will appreciate very much. Just FYI, this might get better response on the admin mailing list... So, what is your local PC running? Linux, BSD, Solaris, Windows? If NOT windows, then just install a copy of postgresql on it real quick to get access to the pg_dump utilities et. al. and use that to remotely dump the data out of the server onto your local machine. Are you updating / inserting back into the big server, or locally? either way, the easiest way I've found to do it is to use a scripting language I'm familiar with (perl, PHP, python, tcl, etc...) and massage the data in whatever ways you need to and then insert it into the database with that scripting languages pg interface package. Other ways of doing it would be to get it into a SQL dump format and use psql to insert it. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] working with schema
On Wed, 12 May 2004, William Anthony Lim wrote:
> Christoph,
>
> First, is it safe for multi user? I mean maybe first user need working with D200402,
> second one need with D200403, if I do this in first user connection:
>
> SET search_path to D200402 ;
>
> does it affect to the second user search path?
No, search paths are session vars.
> Second, I want it dinamic. So, if I want to using D200402, I just need to pass
> 'D200402' string in the argument of the function. Got my point?
You should be able to do it with dot notation:
postgres=# create schema a;
CREATE SCHEMA
postgres=# create schema b;
CREATE SCHEMA
postgres=# create table a.test (info text);
CREATE TABLE
postgres=# create table b.test (info text);
CREATE TABLE
postgres=# insert into a.test values ('abc');
INSERT 1400496 1
postgres=# insert into b.test values ('123');
INSERT 1400497 1
-- Now we try to look up the table without setting a search path and no
-- dot notation:
postgres=# select * from test;
ERROR: relation "test" does not exist
ERROR: relation "test" does not exist
-- Now we set the search path, notice the order:
postgres=# set search_path=public,a,b;
SET
postgres=# select * from test;
info
--
abc
(1 row)
-- Reverse the order of a and b
postgres=# set search_path=public,b,a;
SET
postgres=# select * from test;
info
--
123
(1 row)
-- now without a
postgres=# set search_path=public,b;
SET
postgres=# select * from test;
info
--
123
(1 row)
postgres=# set search_path=public,a;
SET
postgres=# select * from test;
info
--
abc
(1 row)
-- Now we use dot notation. first a, then b. Notice that
-- b, which isn't in our search path, works fine.
postgres=# select * from a.test;
info
--
abc
(1 row)
postgres=# select * from b.test;
info
--
123
(1 row)
>
> Thanks anyway,
>
> William
>
> >>
> >> Hi all,
> >>
> >> I'm just experimenting with schema usage. I'm going to use it as a fake
> >> 'multi-database' system. Is Postgresql support coding schema name using string
> >> variable so I can pass it with parameter? I'm give u an example:
> >>
> >> I have schema: D200401,D200402.D200403,D200404, etc.
> >>
> >> I've set my user just like the schema name, so who login with D200401 will be
> >> using D200401 schema. When someone using D200401 schema, they sometime want to
> >> access another schema, so in my thought I can use variable like this:
> >>
> >> sPointer='D200403'
> >>
> >> select * from sPointer.myTable -- Question: How to write it to work properly?
> >>
> >> Thanks
> >>
> >>
> >> William
> >>
> >>
> >SET search_path to D200401 ;
> >SET search_path to D200402 ;
> >...
> >should do the job.
> >
> >Regards, Christoph
> >
> >
> >---(end of broadcast)---
> >TIP 4: Don't 'kill -9' the postmaster
> >
>
>
>
> Need a new email address that people can remember
> Check out the new EudoraMail at
> http://www.eudoramail.com
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Info
On Tue, 11 May 2004, [koi8-r] "Slava Ilijin[koi8-r] " wrote: > > > > Hello, I have inserted a data bank in SQL server 2000 and from it have > generated a Script. I would like to convert to this Script in > postgresql, but I don't know like this does. I become to you very > thankfully for every kind of information. Yours sincerely Sven Is this just data, or are you using TSQL in Microsoft, or some other language like php or vb to access the data? If it's in TransactSQL you'll have to learn a new language if you don't know on of the dozen or so supported by postgresql to write your own user defined function. Some of the languages available are plruby, plperl, plphp, plpgsql, plsql, plpython, pltcl, and of course, plC. plpgsql, pltcl, and plsql are the most "shaken out" with lots of development having been done in them. C requires a fair investment in your time and effort to begin development in, and generally isn't recommend for beginners. plpgsql is pretty much the default language, and a lot has been done in it. For some examples, see: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html IF you have written an app in vb/c# etc... you can probably just get the oledb connector and start hacking your code around that. http://gborg.postgresql.org/project/oledb/projdisplay.php Also, the mono project appears to be working on an OLEDB connector too that might work. Or, you could abandon whatever you've used and start it over in php, perl, C, or whatever else you want, using the native postgresql connection libs for those languages... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] invalid input syntax for integer: ""
On Sat, 1 May 2004, Sebastian Tewes wrote: > Hello :-) > > got a little prob... > > so my table > > create table warengruppen ( kennung char (1), > holder int, > HauptWarenGruppe int, > BezHWG varchar (50), > WarenGruppe int, > BezWG varchar (50)); > > > the copy syntax > > copy warengruppen FROM '/usr/pgsql/datanorm.wrg' with DELIMITER ';'; > > and a smal part of my 'datanorm.wrg' > > > S;;011;Dachsteine+Formst.;;; > S;;011;;0111;Dachst.glatt(Biber); > S;;011;;0112;Dachst.glatt(Tegal.); the problem here is that "" is not an integer. 0 is. If you were to write a simple script file to process the input file and turn the empty fields into 0s where they should be because they're integers you're set. OR, you can build a temp table with all text columns, import into that, then use a select query to change the blank text columns to the character 0, then import that into the target table. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] start
On Mon, 3 May 2004, H.J. Sanders wrote: > > Hello list. > > > I have difficulties starting the postmaster automatically at boot time > (everything I tried is done by 'root'). > > Can someone give me an example for LINUX (SUSE 8). Just FYI, this probably belongs on admin, but no biggie. I start my postgresql database with this line: su - postgres -c 'pg_ctl start | rotatelogs $PGDATA/pglog 86400 2>1&' If you have apache rotatelogs in the postgres user's path, you can start the database as postgresql AND set the logs to be rotated every 24 hours with this line. If not, and you don't need to log the database's notices and such, then just enter this: su - postgres -c 'pg_ctl start 2>1&' ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Permissions not working
On Thu, 29 Apr 2004, Pallav Kalva wrote: > Hi , > > I am having some problems with setting up permissions in Postgres. I > have a database for ex: 'ups' and it was owned previously by > 'postgres(superuser)' but now i have changed the ownership to new user > 'ups' all the tables are owned by these user 'ups'. This database doesnt > have any schemas except for 'Public'. I have created another user lets > say 'test' and i didnt give 'test' user any permissions to access the > tables owned by 'ups' but still when i login to 'ups' database as psql > ups test and run a select on the tables owned by 'ups' database it > goes through. >I dont want user 'test' to access any tables from the 'ups' > database, i tried revoking permissions it still doesnt work. Can anyone > tell me what is wrong here ? Log in as the superuser (usually postgres) and see what you get from this query: select usesuper from pg_shadow where usename='test'; if usesuper is t, then test is a superuser and can do anything he wants. You need to issue the command: alter user test with nocreateuser; If that isn't the problem, let us know. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Permissions not working
On Thu, 29 Apr 2004, Pallav Kalva wrote: > scott.marlowe wrote: > > >On Thu, 29 Apr 2004, Pallav Kalva wrote: > > > > > > > >>Hi , > >> > >>I am having some problems with setting up permissions in Postgres. I > >>have a database for ex: 'ups' and it was owned previously by > >>'postgres(superuser)' but now i have changed the ownership to new user > >>'ups' all the tables are owned by these user 'ups'. This database doesnt > >>have any schemas except for 'Public'. I have created another user lets > >>say 'test' and i didnt give 'test' user any permissions to access the > >>tables owned by 'ups' but still when i login to 'ups' database as psql > >>ups test and run a select on the tables owned by 'ups' database it > >>goes through. > >> I dont want user 'test' to access any tables from the 'ups' > >>database, i tried revoking permissions it still doesnt work. Can anyone > >>tell me what is wrong here ? > >> > >> > > > >Log in as the superuser (usually postgres) and see what you get from this > >query: > > > >select usesuper from pg_shadow where usename='test'; > > > >if usesuper is t, then test is a superuser and can do anything he wants. > >You need to issue the command: > > > >alter user test with nocreateuser; > > > >If that isn't the problem, let us know. > > > > > Thanks! for the quick reply, I ran the above query and it is 'f' for the > 'test' user, 'test' is not a super user. Ok, then what does \z tablename where tablename is one of the tables you don't want test to access. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Function
On Mon, 16 Feb 2004, Sumita Biswas (sbiswas) wrote: > Thanks for the answer. > I have one more issue. How do I test a function that I wrote? > I was able to create a function called Proc_ConferenceSummary(). > In SQL Server I used to run it through query analyzer by writing the > following command: > exec Proc_ConferenceSummary '12/1/2003','1/23/2004',1,1,0,5001 > > But I don't know how to do it in Postgresql. select functioname(arg1,arg2,arg3); ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] date format in 7.4
On Fri, 20 Feb 2004, Silke Trissl wrote: > Hi, > > I have an application where users can enter the date via a web interface. > > Recently I upgrated my PostgreSQL version from 7.3 to 7.4.1. > > On 7.3 I run several tests about the format of the date and found, > that Postgres accepts almost everything. Today I found out, that 7.4.1 > only accepts dates in the format mm-dd-yy, although the documentation > still states the following > # > > 5. > >Otherwise the date field ordering is assumed to follow the > DateStyle setting: mm-dd-yy, dd-mm-yy, or yy-mm-dd. Throw an error if a > month or day field is found to be out of range. > > # > ref: http://www.postgresql.org/docs/7.4/interactive/datetime-appendix.html > > Does anyone know, if it is intentional to restrict it to mm-dd-yy format > or is is just a bug of 7.4.1? It is intentional. But, it's not as restricted as you might think. You can set the style to one of several ways you can set it. http://www.postgresql.org/docs/7.4/static/datatype-datetime.html the idea behind fixing this in 7.4 was that if you set a date style of SQL, DMY then it would be wring for the database to access a date of 3/14/04 and convert it to March 14th, since your date style said that march 14th should come in as 14/03/04. I.e. it's better at checking ranges and throwing out the ones that don't fit. Better to have a problem getting the wrong data into the database than trying to get it back out a couple years down the road. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
