Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to check table UNLOGGED with postgresql?

Tags:

postgresql

CREATE UNLOGGED TABLE IF NOT EXISTS <tablename>

How can I first check if the desired table is created UNLOGGED, and if not alter the table accordingly?

postgres 9.4

like image 575
membersound Avatar asked Apr 27 '15 15:04

membersound


People also ask

What is unlogged table in PostgreSQL?

Unlogged tables is a PostgreSQL feature that can be used effectively to optimize bulk inserts. PostgreSQL uses Write-Ahead Logging (WAL). It provides atomicity and durability, by default. Atomicity, consistency, isolation, and durability make up the ACID properties.

How do I view tables in PostgreSQL?

Another way to show tables in PostgreSQL is to use the SELECT statement to query data from the PostgreSQL catalog as follows: Syntax: SELECT * FROM pg_catalog. pg_tables WHERE schemaname != 'pg_catalog' AND schemaname !=

How do I get a list of columns in PostgreSQL?

To list down all tables columns on a specific table in the a PostgreSQL database using psql command-line, you can use \dS your_table_name.


1 Answers

You can check column relpersistence of table pg_class;

postgres=# select relpersistence, relname from pg_class where relname like 'foo%';
┌────────────────┬─────────┐
│ relpersistence │ relname │
╞════════════════╪═════════╡
│ p              │ foo     │
│ p              │ foo1    │
│ u              │ foo2    │
└────────────────┴─────────┘
(3 rows)

foo2 is unlogged table.

like image 153
Pavel Stehule Avatar answered Oct 04 '22 20:10

Pavel Stehule