In this article, we will see how to grant select to all tables in PostgreSQL. We can grant select privilege in PostgreSQL by using grant command.
Syntax to grant select to all tables in PostgreSQL:
Continue reading How to grant select to all tables in PostgreSQL
In this article, we will see how to get current date in PostgreSQL. We can get current date in PostgreSQL by running the command current_date. Which gives output as YYYY-MM-DD(Year-month-day of month).
If we want to get date with time, we have to use now() function and if we want to see timezone, we use the command show timezone
In this article, we will see how to insert values into a table from a select query in PostgreSQL.
Syntax to Copy one table data to another in PostgreSQL:
insert into table_name select * from another_table where condition;
Continue reading How to insert values into a table from a select query in PostgreSQL
In this article, we will see how to get the current Unix timestamp from PostgreSQL?
The unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970 at UTC.
Getting the Unix timestamp from a postgresql timestamptz like now() is simple by using either of below queries.
Continue reading How to get the current Unix timestamp from PostgreSQL?
In this article, we will see PostgreSQL describe table. Which means we will get the table structure in of PostgreSQL table.
We can get the table structure by using meta command select query.
PostgreSQL describe table using meta command:
Continue reading PostgreSQL describe table
If we only grant CONNECT to a database, the user can connect but has no other privileges. We have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so:
1. Create User.
create user r2admin with password 'admin@123';
Continue reading How to create a read-only user in PostgreSQL
In this article, we will see how to save query result to a file in PostgreSQL. We can save query result to txt, csv,.. files.
We have three methods to save/export query results to a file in PostgreSQL:
1) copy command
2) from Linux shell using psql tool.
3) \o method.
Continue reading How to save query result to a file in PostgreSQL
In this article, we will see how to find the table size in PostgreSQL. We can find the table size by using pg_relation_size. pg_relation_size returns the size of a table in bytes. If we want more readable format, use pg_size_pretty() function.
We can also find the size of PostgreSQL table, using meta command \dt+ table_name.
1. Find the PostgreSQL table size using pg_relation_size.
Continue reading How to find the table size in PostgreSQL
In this article, we will see how to find database size in PostgreSQL server. We can find database size in PostgreSQL by using SQL Statement and size of the files that make up the PostgreSQL database server.
1. Find the size of current database in PostgreSQL server.
select pg_size_pretty(pg_database_size(current_database()));
