Re: [SQL] grant select on ALL(?) to dbuser;
Hm, I usually use pg_dump -f smthng.out dbname -u after that program asks for username and password, there were some possibilities within postgres too, but you can find them yourself. And why not to dump all data as superuser, that way u have no problems with privileges at all. Antti On Tue, 3 Oct 2000, Marcin Mazurek wrote: > Hi, > is something like this possible? > I have to grant select priviliges to dump database, and I was wondering if > there is easer way to do it then name one, by one every table. > > maz > > Marcin Mazurek > > -- > Kierownik Dzia³u Systemowego > MULTINET SA o/Poznan > http://www.multinet.pl/ >
Re: [SQL] insert value of form - checkboxes
On Thu, 9 Nov 2000, Astrid Hexsel wrote:
> Hello all,
>
> I have a form which has got checkboxes and I am having problems to have their
> values stored in different rows of a table.
>
>
> What I have done is:
>
> # colour_id is the name of my checkboxes in the input tag
>
>
> etc ...
>
> # I put all the values into the array and split them
>
> $colour = $formdata{'colour_id'};
> @colours = split (/,/, $colour);
Try to print the array out, to check whether all values are there.
>
> #then I want to insert then into a table (called cart)in the row called colour_id.
> If I have more than one value they have to be in different rows.
>
> # my code:
>
> foreach $colour(@colours)
> {
>$qry = INSERT INTO cart VALUES
> ('$session_id', '$range_id', '$colour')
> }
>
Try to specify what are you inserting, and use double quotes:
foreach $colour (@colours) {
$qry = qq{
INSERT INTO cart (session_id,range_id,colour) VALUES
('$session_id','$range_id','$colour')
};
$dbh->do(qq{ $qry });# how about this?
}
Btw, try print out in the loop to see, if you are getting right inserts.
> And what happens is that I am only getting just
> one value now inserted in the row. If for example I checked 3 boxes I will get
> only the last
> one.
>
> The foreach loop works fine out of the query.
>
> Thanks a lot for any help.
Advice, don't take my CGI as pure gold. I use Embperl, and so my cgi may
be not so error-free :)
Greetings,
Antti
[SQL] Postgres 7.0.X and arrow keys
Morning. I installed new Mandrake 7.2 and was eager to try new postgres. So I installed it and used pgsql. What surprised me was that the arrow keys wouldn't work anymore as history, instead I get those ascii codes. I was wondering is it the matter of configuration or is it a new feature that one has to live with? Btw, sorry to post it here, but I'm too lazy to order a new list just for this question. Greetings, Antti
Re: [SQL] psql question(actually readline etc.)
Good morning. Since I asked this question before psql question, I would mention other aspects as well. I had same problem, history not working etc. So I was told to install readline. I checked, and readline rpm was installed. So next thing, I was told, was to upgrade my postgres. I checked for update rpm-s and installed them. Problem solved, case closed. I didn't mention, that all that happened with Mdk 7.2 distribution. So it isn't always readline or postgres. Maybe its readline, postgres 7.0.2 and distribution, because postgres 7.0.3 works well now. Ok, lets end this thread Greetings, Antti
[SQL] interval query.
Good morning. Is there some way to make interval query? Towns table(estonia towns, heh :P) id | name 1 Elva 2 Tartu Tallinn 3 Tallinn/Haabersti 4 Tallinn/Mustamae ... etc. What I need is when the town's id= I want to make query where id= OR id=[3..12] for example. I could generate bunch of OR's like id=3 OR id=4 ... but is there some more elegant solution? Greetings, Antti
[SQL] Birthday search.
I have a table with personal data, including date of birth in date format. What i need is a select that searches birthdays in certain range. E.g. I need to find all birthdays starting from 15.april until 20th december. As you all understand, year doesn't matter :P Any simple and elegant solutions to it? Greetings, Antti ---(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] RE:Table corrupted and data lost (second time in onemonth!!)
Hm, about memory exhausting I don't know, but the other day I tested the limits of the text field in Postgres 6.5.(2 or 3). When ppl inserted text in windows environment through html forms, then they got no more than 5000+a bit more symbols. Ok, I decided to test and inserted 8000+bit more from linux and html form. What I got was corrupted table and no way to restore data. And I am interested in parser or something too so I can see table fields etc. to restore data if something so shitty happens. And yes I didn't have backup, and yes it was important data :P Oh, about upgrading, I don't maintain that machine, and half of its software should be upgraded :P At least some little proggie to convert binary data to text would be of some help in the future. Just my idle thoughts though. Greetings, Antti On Wed, 25 Apr 2001, Mark Kirkwood wrote: > > Previously... > >FATAL 1: Memory exhausted in AllocSetAlloc() > >pqReadData() -- backend closed the channel unexpectedly. > >This probably means the backend terminated abnormally > >before or while processing the request. > > > >A table has been corrupted and i don't know why... > >It's really hard to recover the table, and last time i lost some data ;-( > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] BLOB
Hallo. I was just searching documents how to define BLOB in postgres and couldn't find any hints. Could somebody enlighten me? Postgres is 7.0.3. I need to insert into DB text field more than 8000 symbols and there is no way, I can upgrade postgres to 7.1, so only way I see it's possible, is by defining a blob. Greetings, Antti ---(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
[SQL] TEXT field size
Hallo. Maybe its not the right list, but does the Postgres 7.1 support now unlimited text field? In previous versions it was limited to max 31k, or even less, depending on the compilation etc. Greetings, Antti ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Arithmetic operation on DATE
Ok, so here's my small knowledge on this matter
select date(date('2001-08-20')+interval('6 months'));
date
2002-02-20
I'm sure there's more elegant and shorter ways of doing this, but a quick
answer, maybe you're stuck somewhere :)
Antti
Tested it on 7.0.2.
On Mon, 13 Aug 2001, macky wrote:
> is it possible to add a date datatype column to a number resulting to a
> date..
>
>
> theoretically it should do this,,,
>
> X is in months
>
> date + X = date
>
> --> 2001-08-20 + 6 = 2002-02-20
>
>
>
>
>
>
> --
> The information contained in this message (including any attachments)
> is confidential and intended solely for the attention and use of the
> named addressee(s). It must not be copied, distributed nor disclosed
> to any person. If you are not the intended recipient, please delete
> it from your system and notify sender immediately. Any disclosure,
> copying or distribution thereof or any action taken or omitted to be
> taken in reliance thereon is prohibited and may be unlawful.
> --
>
>
>
> ---(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
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[SQL] extract last months data
Lo. Today I digged in manuals and other docs, but didn't find any hint, how to get data from table, where some cols are in date or datetime format, and the data was about last month, or about special month. Psql help was unhelpful too. Here's example: In table workers is data with different date. When I need to make summary, I just want to extract last months data, but I don't know whether the month ended with 29,30 or 31 (external program, that uses postgres), so I cant use select * from workers where date<31.1.2000 and date>1.1.2000 I think I can get month and year number at least, but I can't use mday attribute. All suggestions are welcome :P Stucked alligator :)
[SQL] LEFT JOIN
Greetings. I have a problem. I have 2 tables. E.g. work and workers. I want to select records, that 1st table has, and the second hasn't(both have id attribute). I mean I can't do it with is NULL, because those records don't exist. I was shown, how it is done with mysql select first.id,second.id_first from first left join second on id=id_first where id_first is NULL; but when I tried it in psql, it said, not implemented. Antti
Re: Antw: [SQL] LEFT JOIN
> I think, the following select will solve your problem > > select first.id > from first > except > second.id_first > from second; > Nay, I got parse error. Antti
[SQL] GROUP by
I have table job, and I want to select data from it, grouping by workgroups. When I use select id,name from job where workgroup='top leaders' AND ... group by workgroup; When I want to group, I get ERROR: Illegal use of aggregates or non-group column in target list I'm confused, why does query work only with sum,min etc. alone in select. Antti
[SQL] Group BY ...(cont.)
Ok, if I want to use group by, then I have to put every select field afterwards to group by. But I do want to group by one field. Hence select workgroup,id from job group by workgroup,id; would create groups of 1? But I want to group by workgroups. Sorry if my explanation is fuzzy. A.
[SQL] GROUP by finish&&last day of month
Ok, if I want to get non-aggregat data in groups, I use order by. This group by seemed so logical though, but the fruit that u can't have, is usually the most sweet. New question, how to get the last day of month(order data by last day of month). And to prevent chain letter from misunderstanding, no I don't intend to look it up from calendar and then input it manually. A.
[SQL] Aww, sorry (last day of month)
I thought, I finished that group by theme. What i ment was, that I have information about workers, and I have to make report on them. Report should contain status about workers with the restriction , that the data is about last day of the month. I use perl as a scripting language, and how the hell should I know how many days were in that particular month (28,29,30,31) :). If there are no means by doing it in sql, I write a code to ask for a , mmm, special day. *sigh* Maybe something with date_trunc? Thanx for discussion though. Antti, greetings from estionia :P
[SQL] Ancient postgres+EXCEPT
Lo. I have in my local machine quite new POstgresql
[PostgreSQL 6.5.3 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66]
but the machine, I am writing scripts for has some ancient postgres(btw,
how to see the version on psql, if its not shown at the start? ).
I made a query
SELECT ajaperiood.isik_id,struktuuriyksus,ametikood,tase,eesnimi_perenimi
FROM ajaperiood,ametikoht,isik,tootaja
WHERE perioodi_tyyp='tooleping'
AND
(
algus<=timestamp(date_trunc('month', timestamp 'today')-interval '1 day')
OR
algus is NULL
)
AND
(
lopp>=timestamp(date_trunc('month', timestamp 'today')-interval '1 day')
OR
lopp is NULL
)
AND
tootaja.isik_id=ajaperiood.isik_id
AND
ametikoht.id=tootaja.ametikoht_id
AND
ametikoht.ametikood='10'
EXCEPT
SELECT ajaperiood.isik_id,struktuuriyksus,ametikood,tase,eesnimi_perenimi;
and now I have to put it in such state, that doesn't have except, but does
the same thing. I guess something twice as big but with ANDs,ORs, or
smthng like this :(. But I don't have a clue.
As a comments, I can offer you this: I have four tables-
job,workers,persons and timeintervals. Job is connected through workers
with persons(that is the table that binds persons and jobs), and every
person can have multiple time intervals such as 'vacation','contract',
'stopped contract','unpaid vacation' ...
What I need is to get the list of workers, that have contract, but aren't
on vacation etc. It would be nice to get them with one query, but if its
impossible, than I have to code it the hard way *yawn*.
Antti
P.S. Sorry for not translating the field and table names, but I have a
mighty Sandman around me.
[SQL] Opposite of LOCK
Is there any possibility to unlock tables after they've been locked? The manual part of lock was fuzzy, so I ask from experts instead. MySQL uses lock and unlock. Antti.
[SQL] Extracting data by months
Lo. I'm in dire need of knowledge, how to extract data by month. Monthday and year arent' important, those I can't give from perl script, but what I do give to postgres are the numbers of the months. Date field is in timestamp. I thought about date_trunc, but I can't think of, how to get data with it. In script will be 12 queries, for each month, to get the news from that month. Btw, is there somewhere a manual about date_trunc, the docs that come with RH6.1 distribution are somewhat short and lacking explanation of date_trunc. Antti
