Re: [GENERAL] creating "user" table
Thanks for the information !!
delyx.
Daniel Åkerud.
> > "Chris" == Chris <[EMAIL PROTECTED]> writes:
>
> Chris> Hi,
> Chris> This one is more for the developers, but other
comments welcome.
>
> Chris> Since we can't create a table called user in a
normal database due to
> Chris> the reserved keyword problem,
>
> False precondition!
>
> template1=# create database demo;
> CREATE DATABASE
> template1=# \c demo
> You are now connected to database demo.
> demo=# create table "user" (name text, age int);
> CREATE
> demo=# \d
>List of relations
> Name | Type | Owner
> --+---+
> user | table | merlyn
> (1 row)
>
> demo=# \d user
> Table "user"
> Attribute | Type | Modifier
> ---+-+--
> name | text|
> age | integer |
>
> demo=# insert into "user" values ('Randal', 39);
> INSERT 1034607 1
> demo=# insert into "user" values ('Russ', 38);
> INSERT 1034608 1
> demo=# insert into "user" values ('Ron', 35);
> INSERT 1034609 1
> demo=# select * from "user" order by age;
> name | age
> +-
> Ron| 35
> Russ | 38
> Randal | 39
> (3 rows)
>
> demo=#
>
> You can quote any reserved word to get any name you want.
> You just need to think of the table name of demo as
_ "demo" _.
>
> --
> Randal L. Schwartz - Stonehenge Consulting Services,
Inc. - +1 503 777 0095
> <[EMAIL PROTECTED]>
http://www.stonehenge.com/merlyn/>
> Perl/Unix/security consulting, Technical writing, Comedy,
etc. etc.
> See PerlTraining.Stonehenge.com for onsite and open-
enrollment Perl training!
>
> ---(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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] "user"
Forgott to say that I try to create a table named user. > Why is this so very commonly used word reserved? > Is that some kind of #define so you easily can recompile > PostgreSQL? If so, please guide me to the place. This is > truly annoying. > > Thanks! > > Daniel Åkerud > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] "user"
Why is this so very commonly used word reserved? Is that some kind of #define so you easily can recompile PostgreSQL? If so, please guide me to the place. This is truly annoying. Thanks! Daniel Åkerud ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Encrypting columns, security
I have two questions: (1) SECURITY BY OBSCURITY :) I have a table CREATE TABLE tbl ( password text, created timestamp not null default current_timestamp ); I want to make two triggers on select/insert for this. The trigger for insert should xor password with the first byte (or something) from created. The trigger for select should xor it back using the same byte from created. Is this possible in plpgsql? Where can I read about it? Has anyone done it? I want to combine this with: (2) ENCRYPTING A PASSWORD COLUMN Has anyone got a tip of how to keep a column encrypted to assure yourself that noone can steal it? I have thought of a hash algorithm like ripemd160 but it seems this can't do the trick because i have to save the key somewhere and saving it in the database suck and giving it to the user gives him extra trouble. Anyone has a tip of doing this? It would be extra good if I then ould make a function called verifyUser(text, text) (username/password) that decrypted it and verifyed it (plpgsql, don't like the idea of putting C in there). Hope I was clear enought, thanks for reading through! Daniel Åkerud ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] What could be the reason for EXTREMELY slow INSERTs?
I have this little test application that compares retreiving and inserting data using indexes and not using indexes. I have tried this program on two different machines. One is a PIII 533 256 meg memory and SCSI. The other one is a dual 400 (something) 512 meg memory. The thing is that the PII _SELECTS_ faster (this is OK since the program uses one thread/process) than the dual 400 but inserts is a complete different story. 1 inserts takes 665 seconds on the PIII 533, while on the dual 400 it takes about 13 seconds (*GULP!*). I have done a clean reinstallation (dpkg --purge postgresql, apt- get install postgresql) and tried it again. Same results. Do you guys know what could be the reason? Thanks. Daniel Åkerud ---(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: Re: [GENERAL] Dissapearing indexes, what's that all about?
Wohooo, deluxe :-) THANKS EVERYBODY!! Can't see the logic behind that though The jump in the b-tree must save about 5000 checks... half the table?? Thanks! Daniel Åkerud > Daniel ?erud <[EMAIL PROTECTED]> writes: > > and filling it with 1 rows made out of > > $pwgen 8 1 > data [enter] > > and then running VACUUM and VACUUM ANALYZE > > still yields a sequential scan doing a > > select * from index_with where name > 'm'; > > namely > > seq scan on index_with (cost=0.00..189 rows 5170 width=16) > > So? You're asking it to retrieve over half of the table (or at least > the planner estimates so, and I don't see any evidence here that its > estimate is wildly off). An indexscan would still be a loser in this > scenario. > > If you want to see an indexscan with an inequality query, try giving > it a reasonably tight range. Probably > > select * from index_with where name > 'm' and name < 'n'; > > would use the index in this example. > > regards, tom lane > ---(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
[GENERAL] Tables grow in size when issuing UPDATEs! Why??
Hi, I have noticed a strange thing when dealing with postgreSQL. Foreword: this is all about postgres tables increasing in size extremely fast when doing updates. My sainness is decreasing in approximatly the same speed. Please look. When I do this: * create database foo * create table test CREATE TABLE test ( foo int4 ); and make, for example, 20 inserts into this. Arbitrary data. Then, I make in another terminal: (user with permission) cd /var/lib/postgres/data/base/foo then, watch -n 1 'ls -al test' On my system it is now 8k big. then (a couple of times), update test set foo=5; 10 times is enough for it to get 16k big. I realised that it increases in size EXTREMELY fast when only UDPATing. I find this strange, as I update timestamp in my real database often and in 5 minutes it is 2 megabytes big and containing only 18 rows! (NO BULLSHIT!!) Please help me with this I am quite desperate... Daniel Åkerud ---(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
