1. SQL 문을 이용하여 내가 만든 table(relation)의 field(attribute) 이름과 그
자료형을 알고자 할때...
SELECT pg_attribute.attname, pg_type.typname
FROM pg_class, pg_attribute, pg_type
WHERE pg_class.relname = '%s' AND
pg_attribute.attnum > 0 AND
pg_attribute.attrelid = pg_class.oid AND
pg_attribute.atttypid = pg_type.oid
여기서 '%s'를 자신이 조사하길 원하는 'relation name'을 적어준다.
2. NULL과 empty string은 같지 않다.
empty string은 길이가 0인 문자열이고, NULL은 값 자체가 없는 것을 의미한다.
그러므로 insert into table_name (name) values ('');
insert into table_name (name) values (NULL);
은 다른 문장이 되며, name의 값은 서로 다르다.
3. row의 번호가 들어 있는 field가 없을 경우 row의 serial no를 볼수 있는 방법은?
SELECT oid, * FROM tablename;
4. date 형으로 정의된 필드가 존재할때 7일 전 자료를 보고자 할 경우.
age()라는 함수를 이용한다.
select * from sometable
where date_part( 'epoch' , age( 'now', somedate ) ) > 604800
5. date 형을 가진 필드에 자료를 넣고자 할때
hjk=> \d control_veh
Table = control_veh
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| paterno | char() | 25 |
...many fields...
| ultpago | char() | 2 |
| fecalta | date | 4 |
+----------------------------------+----------------------------------+-------+
이런 자료 구조를 보이고 있다면...
hjk=> update control_veh set fecalta='01-01-1800' where plact='440JAS';
ERROR: Relation control_veh does not have attribute fecalta
이렇게 자료 입력을 시도하면 에러가 난다.
hjk=> update control_veh set fecalta='01-01-1800'::date where plact='440JAS';
이와 같이 해 주어야 한다. (type cast를 이용)
6. field 값이 NULL인 field를 찾으려면...
brecard5=> select * from test where name is null;
Field| Value
-- RECORD 0 --
code| 5678
-- RECORD 1 --
code| 8888
(2 rows)
7. timestamp 형의 자료로 부터 유닉스의 time()과 같은 형의 timedate를 얻으려면
date_part를 이용한다.
webdb=> select date_part('epoch'::datetime,'now'::datetime) as date2;
date2
---------
915556343
(1 row)
8. 함수 인자로 예약어 (reserved word) 를 굳이 사용해야 하는 경우?
답변:
> CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD
> SELECT fun1(new);
>
> Thing is that backend gives this error.
> ERROR: parser: parse error at or near ")"
Try double quoting "new", since it's a reserved word.
9. ACL 을 어떻게 알수 있을까요?
1. the information can be found in the system-table-column PG_CLASS.RELACL:
SELECT RELACL
FROM PG_CLASS
WHERE RELNAME = 't_dummy';
The result seems to be an array of granted permissions, so you should
treat it is an array and look for the appropriate user- or group-name.
Hope this helps ...
2.
SELECT relacl FROM pg_class WHERE relname = '[table name]'
The relacl column contains specific information that must then be parsed to
determine the users which have access to a the table and the privileges
granted.
Example:
{"=","dan=arw","group developers=arw"}
The first section means the PUBLIC does not have any privileges on the
table. Each permission (SELECT, INSERT, UPDATE/DELETE, RULE) is signified
by a letter. r = select, a = insert, w = update/delete, R = rule. User
"dan" has SELECT, INSERT, UPDATE/DELETE rights, but not RULE rights. Group
"developers" has the same privileges.
10. SSL with PostgreSQL 을 이용할 수 있는가?
> I couldn't find any other info about using SSL with PostgreSQL.
http://www.postgresql.org/devel-corner/docs/postgres/ssl-tcp.htm
I seem to recall hearing that the SSL code was broken in 7.0.*. If so,
you may want to try 7.1beta.
11. PL/Perl 사용법
1) 질문
I'd like to be able to only store the database of usernames and passwrods
here locally as a md5 hash. (in case the black hats come to visit....I'd
like to make life hard for them) Using AuthPG, I should be able to create
a SQL call to postgres....but there is no native md5 hashing function.
In my ideal blue-sky world....the SQL call would like this:
SELECT name FROM Sample_table WHERE ( (userid='12345') AND
(userhashed=md5out('abc')) )
With the sample table looks like this:
Sample_table:
name userid userhashed
fred 12345 900150983cd24fb0d6963f7d28e17f72
I'd get the string 'fred' in name from Sample_table.
Idea 1) A call to a shell script. A question was asked back in 1999 if
there was a way to use a shell script in an SQL call.....that person had
no public responses. Moved onto
Idea 2) use PL/Perl to take in the text to be hashed, and output the
hash. Read the docs, looked on the list for more examples......
This perl code works as I'm expecting.
use MD5;
my $mdval = new MD5;
my $result ;
my $out;
$mdval->add('abc');
$result = $mdval->digest();
$out= unpack("H*" , $result );
print $out;
Attempting to xlate to PL/Perl
settle=# create function md5out3(varchar) returns varchar(32) as '
settle'# use MD5;
settle'# my $mdval = new MD5;
settle'# my $result ;
settle'# my $out;
settle'# $mdval->add($_[0]);
settle'# $result = $mdval->digest();
settle'# $out= unpack("H*" , $result );
settle'# return $out;'
settle-# LANGUAGE 'plperl';
CREATE
settle=# select md5out3('fred');
ERROR: creation of function failed : require trapped by operation mask at
(eval 6) line 2.
So.......
What did I do wrong WRT PL/Perl? (Let me guess....having perl call perl
modules causes breakage) Should I be trying something different
to get to my desired end goal?
2) 답변1
> Idea 1) A call to a shell script. A question was asked back in 1999 if
> there was a way to use a shell script in an SQL call.....that person had
> no public responses. Moved onto
> Idea 2) use PL/Perl to take in the text to be hashed, and output the
> hash. Read the docs, looked on the list for more examples......
Nice try :) Good idea, however, you should take into account two things:
a) your functions run under "use Safe" and very restricted as far as what
they could do
b) your function is _not_ a package, it is only a sub, and hence cannot
'use' anything.
A thing to try for you is:
a) change plperl, and where it does 'require Safe;' do 'use MD5; require
Safe;', recompile, etc.
b) change plperl and change permit_only(':default') to
permit_only(':default','require')
It MIGHT work. You might have to add more ops that MD5 code uses though...
Good luck :)
3) 답변2
Actually, a thing to consider would be to have a 'untrusted' PL/perl
language in postgres with use Safe disabled, along with a 'trusted' one.
(pluntrustedperl? plunsafeperl?) Same could be done for pltcl...
> Attempting to xlate to PL/Perl
>
> settle=# create function md5out3(varchar) returns varchar(32) as '
> settle'# use MD5;
> settle'# my $mdval = new MD5;
> settle'# my $result ;
> settle'# my $out;
> settle'# $mdval->add($_[0]);
> settle'# $result = $mdval->digest();
> settle'# $out= unpack("H*" , $result );
> settle'# return $out;'
> settle-# LANGUAGE 'plperl';
> CREATE
> settle=# select md5out3('fred');
> ERROR: creation of function failed : require trapped by operation mask at
> (eval 6) line 2.
You can't use external modules ("use", "require") for security reasons.
FWIW, if I were to write an MD5 function then I'd take one of the
implementations floating around (mhash, Kerberos, OpenSSL, RFC) and make a
C function wrapper around it.
Incidentally, someone has already done this for the upcoming 7.1 release,
but since the function call interface has changed the back port won't be
trivial.
12. 수치 출력값이 3.68009074974387 로 나옵니다. 이것을 소수 둘째자리까지만
출력하고자 합니다. 어떻게 하면 될까요.
1)
cast it to numeric(x,2)
(where x is the total number of digits, and 2 is two decimal places).
template1=# select 3.68009074974387::numeric(3,2);
?column?
----------
3.68
2)
or use round(value,2)
template1=# select round(3.68009074974387, 2);
round
-------
3.68
(1 row)
3)
or
test=# select to_char(3.68009074974387, '99.99');
to_char
---------
3.68
(1 row)
13. 시스템의 안정성 보다는 일단 빠른 속도를 얻고 싶습니다.
postmaster 시작시 -o -F 옵션을 추가해 주도록 합니다.
14. superuser 로서 postgresql 사용자와 그 권한을 모두 보고자 한다면...
> as a pgsql superuser, how can i list all the users and their rights?
SELECT * FROM pg_user;
15. PHP 에서 시간 정보를 DBMS 로 저장하고자 한다. PHP 에서 mktime()-unixtime 함수에
해당하는 postgresql 의 자료형이 무엇인가?
play=> select now()::abstime::integer;
?column?
-----------
979337141
(1 row)
play=> select 979337141::integer::abstime;
?column?
------------------------
2001-01-12 17:05:41-05
(1 row)
The "official" way to get from a datetime type to a Unix timestamp is
date_part('epoch', timevalue):
play=> select date_part('epoch', now());
date_part
-----------
979337212
(1 row)
but I don't know of any easy way to go in the other direction except by
casting to abstime.
16. 사용자 권한 설정을 알고 싶다. 어떻게 하면 되겠는가?
SELECT relacl FROM pg_class WHERE relname = '[table name]'
The relacl column contains specific information that must then be parsed to
determine the users which have access to a the table and the privileges
granted.
Example:
{"=","dan=arw","group developers=arw"}
The first section means the PUBLIC does not have any privileges on the
table. Each permission (SELECT, INSERT, UPDATE/DELETE, RULE) is signified
by a letter. r = select, a = insert, w = update/delete, R = rule. User
"dan" has SELECT, INSERT, UPDATE/DELETE rights, but not RULE rights. Group
"developers" has the same privileges.
For more details --
http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm
17. varchar 로 정의된 필드를 int 형으로 cast 할수없다. 어떻게 하면 되겠는가?
먼저 text 로 casting 한 후에 다시 int로 casting 하라.
select varchar_field::text::int from table_name;
18. PostreSQL 의 Maximum Limitation 은 어떻게 되는가?
I updated the maximum number of columns:
Maximum size for a database? unlimited (60GB databases exist)
Maximum size for a table? 64 TB on all operating
systems
Maximum size for a row? unlimited in 7.1 and later
Maximum size for a field? 1GB in 7.1 and later
Maximum number of rows in a table? unlimited
Maximum number of columns in a table? 250-1600 depending on column
types
Maximum number of indexes on a table? unlimited
19. JDBC 연결이 잘 되지 않습니다. 간단한 예제를 볼수 없는지요?
다음 파일을 참고로 하기 바란다. classpath 에 다음과 같이 jdbc driver 가
추가되어 있어야 한다.
advance@database:~/web/java-test$ echo $CLASSPATH
.:/usr/local/lib/java/postgresql.jar
ex1.jsp
==============================================
<% page info="Example JSP pre-compiled"
import="java.io.*,java.sql.*,java.text.*" %>
Java-Test: hello
<%!
Connection db;
Statement st;
ResultSet rs;
%>
20. RedHat 6.2 에서 PostgreSQL 을 이용하고 있습니다. 그런데 " Too many open
files!" 라는 에러를 만났습니다. 어떻게 해결하면 될까요?
>> I set to 16384. with about 100 clients this seems to be enough. Best
Tim> S99local file but nothing in it on setting maximum open files.
I had to do this last week. On RedHat 6.2 put this at the end
inode handles as file handles so it increases those too.
21. PLPGSQL 로 프로그램 작성중 다음과 같은 에러를 만났습니다. 무엇이
PLPGSQL 을 작성히 M$ 에서 이용하는 에디터를 사용하지 마시기 바랍니다.
PLPGSQL 은 \r\n 을 newline 으로 생각하지 않습니다. PLPGSQL 은 \r 을
whitespace 로 여기지 않기 때문입니다.
22. PLPGSQL 내에서 Transaction 을 다시 열수 있는가?
정답은 안된다 이다. PostgreSQL 은 아직까지 nested transaction을 지원하지 않기 때문이다.
23. JDBC 내에서 nested query 가 가능한가.
Adds forecast to nominated project.
//doGet > doPost, enables servlet to responde to both types of http calls.
out.println("servlet simpleExample called.
");
{ //open data base connection.
out.println("opening connection.
");
out.println("connection opened, creating statement.
");
out.println("statement created.
");
//commit only needed for servlets updating or inserting. not req'd 4 select use anyway
out.println("Error retrieving project information.
"+ex.toString()+"
");
