출처 ; http://database.sarang.net/?inc=read&aid=3547&criteria=pgsql&subcrit=&id=&limit=20&keyword=unix&page=7
첨부파일: pgsql_tip.txt (28,543bytes)
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
==============================================
uname email
=================================================
20. RedHat 6.2 에서 PostgreSQL 을 이용하고 있습니다. 그런데 " Too many open
files!" 라는 에러를 만났습니다. 어떻게 해결하면 될까요?
Tim>
>> I set to 16384. with about 100 clients this seems to be enough. Best
>> way to set it is in your local rc script (/etc/rc.d/rc3.d/*local)
Tim>
Tim> How do you go about doing this on a RedHat 6.2 system? There is an
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
of /etc/rc.d/rc.local:
echo 8192 > /proc/sys/fs/file-max
echo 32768 > /proc/sys/fs/inode-max
It increases file handles to 8192, you are supposed to have four times as many
inode handles as file handles so it increases those too.
21. PLPGSQL 로 프로그램 작성중 다음과 같은 에러를 만났습니다. 무엇이
문제일까요?
----------------
NOTICE: plpgsql: ERROR during compile of add_one
near line 1
"RROR: parse error near "
----------------
PLPGSQL 을 작성히 M$ 에서 이용하는 에디터를 사용하지 마시기 바랍니다.
PLPGSQL 은 \r\n 을 newline 으로 생각하지 않습니다. PLPGSQL 은 \r 을
whitespace 로 여기지 않기 때문입니다.
(물론 이것은 7.1 에서는 바뀌었습니다)
22. PLPGSQL 내에서 Transaction 을 다시 열수 있는가?
정답은 안된다 이다. PostgreSQL 은 아직까지 nested transaction을 지원하지 않기 때문이다.
23. JDBC 내에서 nested query 가 가능한가.
import java.sql.*;
import java.util.*;
import java.io.*;
import java.lang.*;
import javax.servlet.*;
import javax.servlet.http.*;
/*
Adds forecast to nominated project.
*/
public class simpleExample extends HttpServlet {
public static String usernamepassword = "postgres";
private boolean debug=true;
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException,IOException
//doGet > doPost, enables servlet to responde to both types of http calls.
{
doPost(req, res);
}
//
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException,IOException
{
PrintWriter out = res.getWriter();
res.setContentType("text/html");
Connection m_connection = null;
Statement stmt = null;
ResultSet rs = null;
out.println("servlet simpleExample called.");
//
try
{ //open data base connection.
out.println("opening connection.");
m_connection = GetDBConnection(out);
m_connection.setAutoCommit(false);
out.println("connection opened, creating statement.");
stmt = m_connection.createStatement();
out.println("statement created.");
//
try
{//
getListPlayers(m_connection, out);
//
stmt.close();
m_connection.commit();
//commit only needed for servlets updating or inserting. not req'd 4 select use anyway
m_connection.close();
}//
catch(Exception ex)
{//
out.println("Error retrieving project information."+ex.toString()+"");
out.println("");
}//
} catch(Exception ex){// out.println("Error connecting to database. Exception information"+ex.toString()+"");
out.println("");
}//end try catch
} // end doGet
//methods below.
//
public void getListPlayers(Connection m_connection, PrintWriter out)
{
boolean debug=true;
Statement stmt = null;
String getPersDetails = "select * from simpleperson";
int age = 0;
String fName=null, team = null;
try
{
stmt = m_connection.createStatement();
//
if(debug)out.println("sampleMethod1 executing getPersDetails="+getPersDetails+"");
ResultSet rs = stmt.executeQuery(getPersDetails);
out.println("");
out.println(" fName age team ");
if (rs !=null)
while (rs.next() )
{
age = rs.getInt("age");
fName = rs.getString("fName");
team = rs.getString("team");
out.println(" "+fName+" "+age+" ");
out.println(" "+team+""); getListTeams(m_connection, out, team); out.println(" ");
out.println(" "+team+""); getListTeams(m_connection, out, team); out.println(" ");
out.println(" "+team+""); getListTeamsWstmt(stmt, out, team); out.println(" ");
out.println(" "+team+""); getListTeamsWstmt(stmt, out, team); out.println(" ");
out.println(" "+team+""); getListTeamsWstmt(stmt, out, team); out.println(" ");
out.println(" ");
}
out.println(" ");
rs.close();
stmt.close();
}
catch (Exception ex)
{
if(debug)out.println("Exception trapped in getListPlayers. "
+ "ex.toString() = " + ex.toString() + "");
}//
}
//
public void getListTeams(Connection m_connection, PrintWriter out, String tName)
{
boolean debug=false, noRecords=true;
Statement stmt = null;
String getTeamInfo = "select * from simpleTeam where name <> '"+tName+"'";
int age = 0;
String teamName=null;
try
{
stmt = m_connection.createStatement();
//
if(debug)out.println("getListTeams executing getTeamInfo="+getTeamInfo+"");
ResultSet rs = stmt.executeQuery(getTeamInfo);
if (rs !=null)
while (rs.next() )
{
teamName = rs.getString("Name");
out.println(""+teamName+"");
noRecords = false;
}
else out.println("No Records Found.");
rs.close();
stmt.close();
if(noRecords)out.println("noRecords=T.");
else out.println("noRecords=F.");
}
catch (Exception ex)
{
if(debug)out.println("Exception trapped in getListPlayers. "
+ "ex.toString() = " + ex.toString() + "");
}//
}
//
//
public void getListTeamsWstmt(Statement stmt, PrintWriter out, String tName)
{
boolean debug=false;
String getTeamInfo = "select * from simpleTeam where name <> '"+tName+"'";
int age = 0;
String teamName=null;
ResultSet rs = null;
try
{
//
if(debug)out.println("getListTeams executing getTeamInfo="+getTeamInfo+"");
rs = stmt.executeQuery(getTeamInfo);
if (rs !=null)
while (rs.next() )
{
teamName = rs.getString("Name");
out.println(""+teamName+""); }
else out.println("No Records Found.");
rs.close();
out.println("getListTeamsWstmt");
}
catch (Exception ex)
{
if(debug)out.println("Exception trapped in getListPlayers. "
+ "ex.toString() = " + ex.toString() + "");
}//
}
//
public Connection GetDBConnection(PrintWriter out)
throws Exception
/*
Returns database connection, enables easy change to connect to different connections.
*/
{
Connection Parent_connection = null;
//
Properties info = new Properties();
info.put("user",usernamepassword);
info.put("password",usernamepassword);
boolean debug=false;
try
{ //open data base connection.
if(debug)out.println("loading driver.");
Class.forName("org.postgresql.Driver");
if(debug)out.println("driver loaded, getting connection to database as "+usernamepassword+"");
Parent_connection = DriverManager.getConnection("jdbc:postgresql:template1","postgres","postgres");
if(debug)out.println("Connected");
} catch(Exception ex)
{ //
out.println("Error connecting to database. Exception information"+ex.toString()+"");
}//
return Parent_connection;
}//end
//
//
}//end ReviewForeCast
(script below to generate tables)
--simpleExample.sql
drop table simplePerson;
drop table simpleTeam;
create table simplePerson(
fName Varchar(20) NOT NULL,
age numeric(5),
team Varchar(20)
);
create table simpleTeam(
Name Varchar(20) NOT NULL,
CoachName Varchar(20)
);
--now insert some records.
insert into simplePerson (fName, age, team) values('fred', 15, 'red');
insert into simplePerson (fName, age, team) values('tim', 16, 'red');
insert into simplePerson (fName, age, team) values('mike', 14, 'red');
insert into simplePerson (fName, age, team) values('fred', 20, 'blue');
insert into simplePerson (fName, age, team) values('tim', 21, 'blue');
insert into simplePerson (fName, age, team) values('mike', 22, 'blue');
insert into simpleTeam (Name, coachName) values('red', 'Mr Smith');
insert into simpleTeam (Name, coachName) values('blue', 'Mr Jones');
insert into simpleTeam (Name, coachName) values('green', 'Ms LongName');
22. indentd 인증의 실패.
Tonight I tinkered with getting identd authentication working
with Postgres 7.0.2. It appeared to accept my configuration, but the
connections were failing when it
seemed they shouldn't. The sequence looked like this:
#####
elkhorn@ns elkhorn> psql -d elkhorn -u
psql: Warning: The -u option is deprecated. Use -U.
Username: elkhorn
Password:
psql: IDENT authentication failed for user 'elkhorn'
####
the password was correct, and the user and database named elkhorn
appear on the postgres server.
So from there I tried to verify that identd on nollie was working as
expected. Based some docs I found, I tried a basic raw identd connection:
#####
bash-2.04$ telnet nollie 113
Trying 208.196.32.199...
Connected to nollie.summersault.com.
Escape character is '^]'.
3342 , 23
3342 , 23 : ERROR : NO-USER
#########
I tried something similiar with the identd server and got a
similiar result:
root@philoxenist data]# telnet localhost 113
#########
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
23,2372
23 , 2372 : ERROR : UNKNOWN-ERROR
#############
For reference, I was using a line in pg_hbah.conf like this:
# host all 199.120.185.10 255.255.255.255 ident
sameuser
Both the host and client are running a FreeBSD 4.x
So I'm not sure what's wrong. At the moment this looks like an
identd problem rather than a Postgres issue. :) Perhaps one of you
have run into this before, though?
답변 :
Mark Stosberg writes:
> Tonight I tinkered with getting identd authentication working
> with Postgres 7.0.2. It appeared to accept my configuration, but the
> connections were failing when it
> seemed they shouldn't.
Is there a firewall or router between the client and server machines?
I've found the hard way that identd doesn't work for connections that
pass through a NAT-enabled router, because the port numbers are
different on the two sides of the router, so that the port number sent
in the ident request doesn't match anything the identd daemon can see.
(I suppose the router could fix this if it knew about ident requests,
but at least my Netopia router does not do that ...)
> The sequence looked like this:
> #####
> elkhorn@ns elkhorn> psql -d elkhorn -u
> psql: Warning: The -u option is deprecated. Use -U.
Hm, is PGHOST set in the environment? Otherwise this will try to do a
Unix-socket connection, which does not work with ident.
regards, tom lane
23. macaddr 을 text 형으로 casting 할 수 있는 방법은?
CREATE FUNCTION text(macaddr) returns text AS '
BEGIN
return ;
END;
' LANGUAGE 'plpgsql';
23. large text file 을 import 할려고 하는데, 시간이 너무 많이 걸린다.
해결법은?
fsync 옵션을 끄고 다시 import 를 시도한다.
24. PostgreSQL 을 실행도중 다음과 같으 에러메시지를 얻었다.
무엇이 잘못된 것일까요?
pq_flush: send() failed: Broken pipe
FATAL: pq_endmessage failed: errno=32
답변 :
Clients disconnecting prematurely, perhaps?
regards, tom lane
25. 특정 사용자가 없는지 확인하는 함수를 만들수 없을까?
답변:
CREATE FUNCTION f3(name) RETURNS bool AS '
DECLARE
row record;
BEGIN
SELECT * INTO row FROM pg_user WHERE usename = ;
IF FOUND THEN
RAISE NOTICE ''user % exists'', ;
RETURN ''t'';
ELSE
RAISE NOTICE ''user % not found'', ;
RETURN ''f'';
END IF;
END;
' LANGUAGE 'plpgsql';
26. Index 시 사용되는 Operator Class 를 확인할 수 있는 방법은?
SELECT am.amname AS acc_name,
opc.opcname AS ops_name,
opr.oprname AS ops_comp
FROM pg_am am, pg_amop amop,
pg_opclass opc, pg_operator opr
WHERE amop.amopid = am.oid AND
amop.amopclaid = opc.oid AND
amop.amopopr = opr.oid
ORDER BY acc_name, ops_name, ops_comp;
'컴터 > pgsql' 카테고리의 다른 글
| mysql zerofill 같은 효과 (0) | 2007.10.26 |
|---|---|
| mysql 의 concat 과 같은역할.. (0) | 2007.10.26 |
| 날짜 쿼리(2주후 까지 찾기) (0) | 2007.10.23 |
| mysql_insert_id 와 같은 postgresql 의.. (0) | 2007.10.17 |