{"id":542,"date":"2013-10-31T15:47:37","date_gmt":"2013-10-31T09:47:37","guid":{"rendered":"http:\/\/alexeyka.zantsev.com\/?p=542"},"modified":"2015-07-31T12:32:45","modified_gmt":"2015-07-31T07:32:45","slug":"postgresql-notes","status":"publish","type":"post","link":"https:\/\/alexeyka.zantsev.com\/?p=542","title":{"rendered":"PostgreSQL notes"},"content":{"rendered":"

1. Edit postgresql.conf line listen_addresses = '10.14.1.21'<\/code> to your appropriate value.
\n2. Add to pg_hba.conf a line, which will allow connection to the database from certain IP address\/subnet: host\tjohnsdb \tjohn\t10.14.1.201\/32\t\tmd5<\/code>
\n3. service postgresql reload<\/code>
\n4. As root, ‘su - postgres<\/code>‘ , then ‘psql<\/code>‘ , then:<\/p>\n

4.1. postgres=#<\/strong> CREATE USER john WITH ENCRYPTED PASSWORD 'johnstailislong' ;<\/code>
\n4.2. postgres=#<\/strong> CREATE DATABASE johnsdb WITH OWNER john ;<\/code>
\n ( If the database owner is wrong, change it: postgres=#<\/strong> ALTER DATABASE johnsdb OWNER TO john ;<\/code> )
\n5. Then connect from outside to the already created database and create a table inside it:
\npsql -d johnsdb -U john -h 10.14.1.21<\/code>
\njohnsdb=> <\/strong>CREATE TABLE johnstable (type varchar(6),name varchar(128),secret varchar(128),context varchar(128),host varchar(128)) WITH (OIDS=FALSE);<\/code><\/p>\n

Hints:<\/p>\n

Change DB’s owner (as postgresql user):
\nALTER DATABASE johnsdb OWNER TO john ;<\/code><\/p>\n

Change table’s owner (as postgresql user):
\nALTER TABLE johnstable OWNER TO john ;<\/code><\/p>\n

Table permissions: (as postgresql user):
\nGRANT ALL ON TABLE johnstable TO john ;<\/code><\/p>\n

Change default values in a column:
\nALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT 'green,red' ;<\/code><\/p>\n

Rename a column:
\nALTER TABLE tablename RENAME COLUMN oldcolname TO newcolname;<\/code><\/p>\n

Change column type:
\nALTER TABLE tablename ALTER COLUMN columnname TYPE varchar(30);<\/code><\/p>\n

Get last N rows:
\nSELECT * FROM mytable ORDER BY record_date DESC LIMIT 5;<\/code><\/p>\n

Get first N rows:
\nSELECT * FROM mytable ORDER BY record_date ASC LIMIT 5;<\/code><\/p>\n

Select between X and Y values:
\nSELECT name,callerid FROM sip_conf WHERE name BETWEEN '1301' and '1310' ORDER BY name;<\/code><\/p>\n

Show databases:<\/p>\n

\r\nastertest=> \\list\r\n                                  List of databases\r\n   Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   \r\n-----------+-----------+----------+-------------+-------------+-----------------------\r\n astertest | astertest | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | \r\n postgres  | postgres  | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | \r\n template0 | postgres  | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c\/postgres          +\r\n           |           |          |             |             | postgres=CTc\/postgres\r\n template1 | postgres  | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c\/postgres          +\r\n           |           |          |             |             | postgres=CTc\/postgres\r\n(4 rows)\r\n<\/pre>\n
Show tables in the current database:<\/p>\n
astertest=> \\d\r\n                   List of relations\r\n Schema |          Name          |   Type   |   Owner   \r\n--------+------------------------+----------+-----------\r\n public | sip_conf_office        | table    | astertest\r\n public | sip_conf_office_id_seq | sequence | astertest\r\n(2 rows)<\/pre>\n
Show table structure:<\/p>\n
astertest=> \\d sip_conf_office\r\n                                     Table \"public.sip_conf_office\"\r\n     Column     |          Type          |                          Modifiers                           \r\n----------------+------------------------+--------------------------------------------------------------\r\n id             | integer                | not null default nextval('sip_conf_office_id_seq'::regclass)\r\n name           | character varying(80)  | not null default ''::character varying\r\n accountcode    | character varying(20)  | \r\n amaflags       | character varying(7)   | \r\n callgroup      | character varying(10)  | \r\n callerid       | character varying(80)  | \r\n canreinvite    | character varying(3)   | default 'no'::character varying\r\n context        | character varying(80)  | \r\n defaultip      | character varying(15)  | \r\n dtmfmode       | character varying(7)   | \r\n fromuser       | character varying(80)  | \r\n fromdomain     | character varying(80)  | \r\n host           | character varying(31)  | not null default ''::character varying\r\n insecure       | character varying(4)   | \r\n language       | character varying(2)   | \r\n mailbox        | character varying(50)  | \r\n md5secret      | character varying(80)  | \r\n nat            | character varying(5)   | not null default 'no'::character varying\r\n...<\/pre>\n
To insert many lines in SQL-syntax from txt file:<\/p>\n
\r\npsql -U asterisk -h localhost -d asterisk -f \/root\/peers.txt\r\n<\/pre>\n
… assuming that peers.txt looks like:<\/p>\n
\r\nINSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0001','Operator 0001 <0001>','0001','operators','5060');\r\nINSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0001d','Operator 0001 <0001>','0001d','operators','5061');\r\nINSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0002','Operator 0002 <0002>','0002','operators','5060');\r\nINSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0002d','Operator 0002 <0002>','0002d','operators','5061');\r\n...\r\nINSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('9999','Operator 9999 <9999>','9999','operators','5060');\r\nINSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('9999d','Operator 9999 <9999>','9999d','operators','5061');\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
1. Edit postgresql.conf line listen_addresses = ‘10.14.1.21’ to your appropriate value. 2. Add to pg_hba.conf a line, which will allow connection to the database from certain IP address\/subnet: host johnsdb john 10.14.1.201\/32 md5 3. service postgresql reload 4. As root, ‘su – postgres‘ , then ‘psql‘ , then: 4.1. postgres=# CREATE USER john WITH ENCRYPTED […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[94,140,93],"class_list":["post-542","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-postgresql","tag-psql","tag-sql"],"_links":{"self":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts\/542","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=542"}],"version-history":[{"count":20,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts\/542\/revisions"}],"predecessor-version":[{"id":861,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts\/542\/revisions\/861"}],"wp:attachment":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=542"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=542"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=542"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}