PostgreSQL

36 Notes
+ Show Schemas (June 5, 2022, 1:28 a.m.)

SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;

+ Schemas and privileges (June 5, 2022, 1:25 a.m.)

Users can only access objects in the schemas that they own. It means they cannot access any objects in the schemas that do not belong to them. To allow users to access the objects in the schema that they do not own, you must grant the USAGE privilege of the schema to the users: GRANT USAGE ON SCHEMA schema_name TO role_name; To allow users to create objects in the schema that they do not own, you need to grant them the CREATE privilege of the schema to the users: GRANT CREATE ON SCHEMA schema_name TO user_name; Note that, by default, every user has the CREATE and USAGE on the public schema.

+ Create / Alter / Drop schema (June 5, 2022, 1:11 a.m.)

CREATE SCHEMA sales; Create a schema for a user: CREATE SCHEMA AUTHORIZATION john; ALTER SCHEMA schema_name RENAME TO new_name; ALTER SCHEMA schema_name OWNER TO { new_owner | CURRENT_USER | SESSION_USER}; DROP SCHEMA IF EXISTS accounting; DROP SCHEMA IF EXISTS finance, marketing; DROP SCHEMA sales CASCADE; ----------------------------------------------------------------------- To add the new schema to the search path: SET search_path TO sales, public; ----------------------------------------------------------------------- Now, if you create a new table named staff without specifying the schema name, PostgreSQL will put this staff table into the sales schema: ----------------------------------------------------------------------- To access the "staff" table in the "sales" schema you can use one of the following statements: SELECT * FROM staff; Or SELECT * FROM sales.staff; The "public" schema is the second element in the search path, so to access the "staff" table in the "public" schema, you must qualify the table name as follows: SELECT * FROM public.staff; If you use the following command, you will need to explicitly refer to objects in the "public" schema using a fully qualified name: SET search_path TO public; The public schema is not a special schema, therefore, you can drop it too. -----------------------------------------------------------------------

+ What is a schema? (June 5, 2022, 1:10 a.m.)

A schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, stored procedures, and operators. A database can contain one or multiple schemas and each schema belongs to only one database. Two schemas can have different objects that share the same name. For example, you may have "sales" schema that has "staff" table and the "public" schema which also has the "staff" table. When you refer to the "staff" table you must qualify it as follows: public.staff Or sales.staff ----------------------------------------------------------------------------------------- Why do you need to use schemas? There are some scenarios that you want to use schemas: - Schemas allow you to organize database objects e.g., tables into logical groups to make them more manageable. - Schemas enable multiple users to use one database without interfering with each other. ----------------------------------------------------------------------------------------- The public schema: PostgreSQL automatically creates a schema called "public" for every new database. Whatever object you create without specifying the schema name, PostgreSQL will place it into this "public" schema. Therefore, the following statements are equivalent: CREATE TABLE table_name( ... ); and CREATE TABLE public.table_name( ... ); ----------------------------------------------------------------------------------------- The schema search path: In practice, you will refer to a table without its schema name e.g., "staff" table instead of a fully qualified name such as "sales.staff" table. When you reference a table using its name only, PostgreSQL searches for the table by using the "schema search path", which is a list of schemas to look in. PostgreSQL will access the first matching table in the schema search path. If there is no match, it will return an error, even if the name exists in another schema in the database. The first schema in the search path is called the current schema. Note that when you create a new object without explicitly specifying a schema name, PostgreSQL will also use the current schema for the new object. The current_schema() function returns the current schema: SELECT current_schema(); Here is the output: current_schema ---------------- public (1 row) This is why PostgreSQL uses "public" for every new object that you create. To view the current search path, you use the SHOW command in psql tool: SHOW search_path; The output is as follows: search_path ----------------- "$user", public (1 row) -----------------------------------------------------------------------------------------

+ Querying across schemas (June 5, 2022, 12:49 a.m.)

SELECT column_name from schema_name.table_name where ...

+ Connect to remote host (June 5, 2022, 12:29 a.m.)

psql -h 192.168.1.50 -p 5432 -d testdb -U testuser -W -W option will prompt for password. For example:

+ Drop Table (Feb. 26, 2022, 8:23 a.m.)

DROP TABLE author; DROP TABLE IF EXISTS author; DROP TABLE authors CASCADE; DROP TABLE tvshows, animes;

+ Rename database (Feb. 20, 2022, 12:33 p.m.)

ALTER DATABASE people RENAME TO customers;

+ Change owner of database (Feb. 20, 2022, 12:30 p.m.)

ALTER DATABASE name OWNER TO new_owner;

+ Delete a row (Jan. 21, 2022, 11:34 p.m.)

delete from software where id=6; delete from sample1 where id=6 AND age= 23; DELETE FROM software where exists (select 1 from system where system.id = software.id AND system.name = software.name ); delete from system where id IN(2,3)

+ Remove a column (Jan. 18, 2022, 4:48 p.m.)

alter table users drop column created_at;

+ List all columns (Nov. 24, 2021, 1:38 p.m.)

db=# select * from table_name where false; db=# \d db=# \d+ db=# \dt+

+ Increase max connections (June 16, 2019, 10:20 a.m.)

Edit postgresql.conf: max_connections = 400 shared_buffers = 512MB

+ Removing a Constraint (Feb. 1, 2017, 11:32 a.m.)

To remove a constraint you need to know its name. If you gave it a name then that's easy. Otherwise, the system assigned a generated name, which you need to find out. The psql command \d tablename can be helpful here; other interfaces might also provide a way to inspect table details. Then the command is: ALTER TABLE products DROP CONSTRAINT some_name; This works the same for all constraint types except not-null constraints. To drop a not null constraint use ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; (Recall that not-null constraints do not have names.)

+ Adding a Constraint (Feb. 1, 2017, 11:31 a.m.)

To add a constraint, the table constraint syntax is used. For example: ALTER TABLE products ADD CHECK (name <> ''); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; To add a not-null constraint, which cannot be written as a table constraint, use this syntax: ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; The constraint will be checked immediately, so the table data must satisfy the constraint before it can be added.

+ PostgreSQL history file (Feb. 1, 2017, 11:31 a.m.)

Similar to the Linux ~/.bash_history file, PostgreSQL stores all the SQL command that was executed in a history file called ~/.psql_history as shown below. cat ~/.psql_history

+ Turn on timing and check how much time a query takes to execute (Feb. 1, 2017, 11:30 a.m.)

# \timing — After this, if you execute a query it will show how much time it took for doing it. # \timing Timing is on. # SELECT * from pg_catalog.pg_attribute ; Time: 9.583 ms

+ Change databse user password (Feb. 1, 2017, 11:29 a.m.)

Root user: ALTER USER postgres WITH PASSWORD 'tmppassword'; ------------------------------------------------- psql cdrdb alter user cdr with password 'abcdef';

+ Export JSON from PostgreSQL (May 11, 2016, 10:41 p.m.)

select row_to_json(words) from words; {"id":6013,"text":"advancement","pronunciation":"advancement",...} ---------------------------------------- select row_to_json(row(id, text)) from words; {"f1":6013,"f2":"advancement"} This will name the columsn as `f1`, 'f2`, 'f3`, ... To solve the problem: select row_to_json(t) from ( select id, text from words ) t {"id":6013,"text":"advancement"} ---------------------------------------- The other commonly used technique is array_agg and array_to_json. array_agg is a aggregate function like sum or count. It aggregates its argument into a PostgreSQL array. array_to_json takes a PostgreSQL array and flattens it into a single JSON value. select array_to_json(array_agg(row_to_json(t))) from ( select id, text from words ) t [{"id":6001,"text":"abaissed"},{"id":6002,"text":"abbatial"},{"id":6003,"text":"abelia"},...] ----------------------------------------

+ Errors (July 6, 2015, 11:01 a.m.)

psql: could not connect to server: Connection refused Is the server running on host "192.168.0.6" and accepting TCP/IP connections on port 5432? For solving this error, refere to "Remote Connection". ------------------------------------------------------------------------- psycopg2.ProgrammingError: permission denied for relation notes_application OR ERROR: role "mohsen_notes" does not exist (While importing a database) For solving this error you need to access the database shell with `postgres` user: su su postgres psql -d notesdb -U postgres And using this command, you will grant all the needed permissions: GRANT ALL PRIVILEGES ON TABLE notes_application TO notes; -------------------------------------------------------------------------

+ Remote Connection (Feb. 4, 2016, 10:27 a.m.)

1- Add this line to the end of the file pg_hba.conf: host all all 88.135.34.18/32 trust Another example: hostnossl community_db community 89.42.211.37/32 trust 2- Uncomment the following line and put star instead of 'localhost' in the file "postgresql.conf": listen_addresses = '*' 3- Restart postgresql service: /etc/init.d/postgresql restart

+ Log into a Postgresql database (June 27, 2015, 11:35 a.m.)

http://alvinalexander.com/blog/post/postgresql/log-in-postgresql-database --------------------------------------------------------------------------------------------- psql -d mydb -U myuser

+ Changing a Column's Default Value (May 17, 2015, 11:38 a.m.)

To set a new default for a column, use a command like this: ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; Note that this doesn't affect any existing rows in the table, it just changes the default for future INSERT commands. To remove any default value, use ALTER TABLE products ALTER COLUMN price DROP DEFAULT; This is effectively the same as setting the default to null. As a consequence, it is not an error to drop a default where one hadn't been defined, because the default is implicitly the null value.

+ Update Values (Jan. 23, 2015, 6:12 p.m.)

UPDATE table SET column1 = value1, = value2 ,... WHERE condition;

+ Counting the select (Jan. 23, 2015, 5:53 p.m.)

SELECT count(*) FROM sometable;

+ Select unique column (Jan. 23, 2015, 5:44 p.m.)

SELECT DISTINCT column_1 FROM table_name --------------------------------------------------------------------- If you specify multiple columns, the DISTINCT clause will evaluate the duplicate based on the combination of values of those columns. SELECT DISTINCT column_1, column_2 FROM tbl_name; --------------------------------------------------------------------- PostgreSQL also provides the DISTINCT ON (expression) to keep the “first” row of each group of duplicates where the expression is equal. See the following syntax: SELECT DISTINCT ON (column_1), column_2 FROM tbl_name ORDER BY column_1, column_2; --------------------------------------------------------------------- select DISTINCT ip_src FROM (SELECT ip_src from acct order by stamp_inserted) as mohsen2 ---------------------------------------------------------------------

+ Set password for postgres user (Jan. 22, 2015, 12:03 p.m.)

sudo -u postgres psql postgres \password postgres

+ Needed packages for Asterisk/Apache2 (Jan. 22, 2015, 11:40 a.m.)

apt-get install libapache2-mod-auth-pgsql

+ Extend/Increase the length of a varchar column (Oct. 25, 2014, 4:57 p.m.)

This is done using the way you change the type of a column: alter table issue_tracker_sentsms alter column status type varchar(3);

+ Display Tables and Columns (Oct. 25, 2014, 4:53 p.m.)

Using this command you will be connected to the database: \d issue_tracker_db; Using this command you will see the tables inside it: \d or \d+ Using this command you will see the columns: \d issue_tracker_sms;

+ Default current date time for a field, while altering (Sept. 8, 2014, 9 p.m.)

alter table m_tasks_attachment add column "date_time" timestamp with time zone NOT NULL default now();

+ Add new column (Sept. 6, 2014, 9:42 p.m.)

alter table m_tasks_message add column "is_new" boolean NOT NULL; If you have already some data in the table, it will raise an error: ERROR: column "is_new" contains null values Which means you have to first create the column without the NOT NULL constraint and then set it to NOT NULL. But you can easily set the desired default values with: alter table m_tasks_message add column "is_new" boolean NOT NULL DEFAULT False; This will set the already created records with the default value `False`.

+ Commands (Aug. 22, 2014, 7:58 a.m.)

Login as "postgres" (SuperUser) to start using database: # su - postgres -------------------------------------------------------------------------------- Create a new database: createdb mydb -------------------------------------------------------------------------------- Drop database: dropdb mydb -------------------------------------------------------------------------------- Access database: psql mydb -------------------------------------------------------------------------------- Dump all databases: pg_dumpall > /var/lib/pgsql/backups/dumpall.sql -------------------------------------------------------------------------------- Show databases: # psql -l To see the size of databases or list of tables: psql <a_database> mydb=# \l mydb=# \l+ mydb=# \dt # lists all tables in the current database mydb=# \dt+ -------------------------------------------------------------------------------- Show users: mydb=# SELECT * FROM "pg_user"; -------------------------------------------------------------------------------- Show tables: mydb=# SELECT * FROM "pg_tables"; -------------------------------------------------------------------------------- Set password: mydb=# UPDATE pg_shadow SET passwd = 'new_password' where usename = 'username'; -------------------------------------------------------------------------------- Clean all databases (Should be done via a daily cron): vacuumdb --quiet --all -------------------------------------------------------------------------------- How to edit PostgreSQL queries in your favorite editor? # \e \e will open the editor, where you can edit the queries and save it. By doing so the query will get executed. -------------------------------------------------------------------------------- To rename a column: ALTER TABLE products RENAME COLUMN product_no TO product_number; -------------------------------------------------------------------------------- To rename a table: ALTER TABLE products RENAME TO items; -------------------------------------------------------------------------------- Change type: ALTER TABLE table ALTER COLUMN anycol TYPE anytype; Renaming a Column: ALTER TABLE products RENAME COLUMN product_no TO product_number; -------------------------------------------------------------------------------- Update a field: update menus set description='Payments: Carriersss' where username='mohsen' and menu='accountingcarrier'; -------------------------------------------------------------------------------- Delete all records from a table: delete from table_name; -------------------------------------------------------------------------------- Count unique records: select count(distinct ip_src) from table_name; -------------------------------------------------------------------------------- List columns with indexes: SELECT * FROM pg_indexes WHERE tablename = 'mytable'; -------------------------------------------------------------------------------- Delete all data in a table and reset auto increment counter: truncate table my_table RESTART IDENTITY; -------------------------------------------------------------------------------- Reset auto increment counter: ALTER SEQUENCE my_table_id_seq RESTART WITH 1; --------------------------------------------------------------------------------

+ Import / Export (Backup / Restore) (Aug. 6, 2015, 8:38 a.m.)

Backup / Export 1- su 2- su postgres -l 3- pg_dump dbname > outfile (If you want to compress the outfile) use step `4` instead of `3`) 4- pg_dump dbname | gzip > filename.gz (If you think your database output file is going to be so big, you can split it, using `5` instead of `3` and `4`) 5- pg_dump dbname | split -b 1m - filename (instead of 1mb you can write any size) ********** If you got permission denied error, it's because of the folder/directory you are using for backup! Change the output path or use `cd` to move the path to postgres home (which is /var/lib/postgresql). OR Create a folder and give it the permission for postgres to write to it by setting it the ownership mkdir postgres_dumps chown postgres.postgres postgres_dumps cd/to/postgres_dumps -------------------------------------------------------------------------------- Restore / Import: 1- su 2- su postgres -l 3- psql dbname < infile (if you have a compressed file, use step `4` instead of `3`) 4- gunzip -c filename.gz | psql dbname (If your backup files are already splitted, use `5` instead of `3` and `4`) 5- cat filename* | psql dbname -------------------------------------------------------------------------------- For selective tables: Go to Postgre console using `psql -U db_user db_name` and then: pg_dump -t table_name -t table_name2 -t table_name3 -U db_owner db_name > outfile.sql -------------------------------------------------------------------------------- Export Database into CSV file: 1- sudo su 2- su postgres 3- COPY table_name TO '/tmp/file_name.csv' DELIMITER ',' CSV HEADER; COPY (SELECT foo,bar FROM table_name limit 100) TO '/tmp/file_name.csv' DELIMITER ',' CSV HEADER; COPY (SELECT foo,bar FROM table_name) TO '/tmp/file_name.csv' DELIMITER ',' CSV HEADER; -------------------------------------------------------------------------------- For importing dumped tables: copy cdr from '/home/mohsen/MyTemp/as3.dat'; -------------------------------------------------------------------------------- Error while importing: ERROR: role "mohsen_notes" does not exist For solving this error, refer to `Errors` section within this category. -------------------------------------------------------------------------------- Dump all database: pg_dumpall > /var/lib/pgsql/backups/dumpall.sql -------------------------------------------------------------------------------- Restore database: psql -f /var/lib/pgsql/backups/dumpall.sql mydb -------------------------------------------------------------------------------- Dump only parts of tables: copy (select * from acct order by stamp_inserted limit 8000) to '/home/mohsen/Temp/acct.tsv'; Restore: copy acct from '/home/mohsen/Temp/acct.tsv'; --------------------------------------------------------------------------------

+ Configuration (Feb. 4, 2016, 10:18 a.m.)

1- Edit the file pg_hba.conf which can be found in either of the following paths: /var/lib/pgsql/data/ /usr/share/postgresql/9.x/ /etc/postgresql/9.x/main/ 2- Change the settings to this: local all postgres trust local all all password host all all 127.0.0.1/32 md5 3- Restart postgresql service: service postgresql restart

+ Installation (Feb. 5, 2016, 1:07 a.m.)

apt install postgresql-server-dev-all postgresql libpq-dev python3-dev ----------------------------------------------------------- To check if postgresql is installed and run successfully on port 5432, use this command: nc localhost 5432 < /dev/null It should not return anything. It should only wait ... ----------------------------------------------------------- If you got error like the following when creating databases or users: Is the server running locally and accepting ..... postgresql/.s.PGSQL.5432" Check if postgresql service is enabled!? systemctl status postgresql If not, start it: systemctl enable postgresql