3zze84mpr

commands.sql

3zze84mpr

commands.sql

CREATE TABLE site_resources (
  id      INTEGER 	PRIMARY KEY,
  title   TEXT 		  NOT NULL,
  parent  INTEGER 	NOT NULL
);

INSERT INTO site_resources VALUES (1,  'главная', 	0);     --главная
INSERT INTO site_resources VALUES (2,  'товары', 	  1);     --главная/товары
INSERT INTO site_resources VALUES (3,  'овощи', 	  2);     --главная/товары/овощи
INSERT INTO site_resources VALUES (4,  'картофель', 3);  	  --главная/товары/овощи/картофель
INSERT INTO site_resources VALUES (5,  'капуста', 	3);    	--главная/товары/овощи/капуста
									   
INSERT INTO site_resources VALUES (6,  'фрукты', 	  2);     --главная/товары/фрукты
INSERT INTO site_resources VALUES (7,  'бананы', 	  6);     --главная/товары/фрукты/бананы
INSERT INTO site_resources VALUES (8,  'яблоки', 	  6);     --главная/товары/фрукты/яблоки
INSERT INTO site_resources VALUES (9,  'груши', 	  6);     --главная/товары/фрукты/груши
									   
									   
INSERT INTO site_resources VALUES (10, 'контакты', 	0);   	--контакты
INSERT INTO site_resources VALUES (11, 'карта', 	  10);    --контакты/карта
INSERT INTO site_resources VALUES (12, 'реквизиты', 10);    --контакты/реквизиты

INSERT INTO site_resources VALUES (13, 'о нас', 	  0);     --о нас

with RECURSIVE cte(id, title, hierarchy, full_hierarchy, level, sort) as (

SELECT 
		id,
		title,
		null::text as hierarchy,
		title as full_hierarchy,
		0 as level,
		LPAD((row_number() OVER (ORDER BY title))::text, 5, '0') as sort
	from 
		site_resources
	where 
		parent = 0
	
	union all
	
	SELECT 
		r.id,
		r.title,
		coalesce(cte.hierarchy || ', ', '') || r.title,
		full_hierarchy || '/' || r.title,
		cte.level + 1,
		cte.sort || '.' || LPAD((row_number() OVER (ORDER BY r.title))::text, 5, '0') as sort
	from 
		cte
	inner join
		site_resources as r on
			cte.id = r.parent

)
SELECT 
  sr.*
FROM 
  cte as sr
order by
  sr.sort;

STDIN

Output:

CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
 id |   title   |        hierarchy         |         full_hierarchy         | level |          sort           
----+-----------+--------------------------+--------------------------------+-------+-------------------------
  1 | главная   |                          | главная                        |     0 | 00001
  2 | товары    | товары                   | главная/товары                 |     1 | 00001.00003
  3 | овощи     | товары, овощи            | главная/товары/овощи           |     2 | 00001.00003.00001
  5 | капуста   | товары, овощи, капуста   | главная/товары/овощи/капуста   |     3 | 00001.00003.00001.00003
  4 | картофель | товары, овощи, картофель | главная/товары/овощи/картофель |     3 | 00001.00003.00001.00004
  6 | фрукты    | товары, фрукты           | главная/товары/фрукты          |     2 | 00001.00003.00002
  7 | бананы    | товары, фрукты, бананы   | главная/товары/фрукты/бананы   |     3 | 00001.00003.00002.00001
  9 | груши     | товары, фрукты, груши    | главная/товары/фрукты/груши    |     3 | 00001.00003.00002.00002
  8 | яблоки    | товары, фрукты, яблоки   | главная/товары/фрукты/яблоки   |     3 | 00001.00003.00002.00005
 10 | контакты  |                          | контакты                       |     0 | 00002
 11 | карта     | карта                    | контакты/карта                 |     1 | 00002.00001
 12 | реквизиты | реквизиты                | контакты/реквизиты             |     1 | 00002.00002
 13 | о нас     |                          | о нас                          |     0 | 00003
(13 rows)

created 3 months ago

PostgreSQL online editor

Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.

About PostgreSQL

PostgreSQL is a open source relational database system and is also knows as Postgres.

Key Features:

Postgres is not only free and open-source but also it is highly extensible.
Custom Data types and funtions from various programming languaues can be introduced and the good part is compiling entire database is not required.
ACID(Atomicity, Consistency, Isolation, Durability) compliant.
First DBMS which implemented Multi-version concurrency control (MVCC) feature.
It's the default database server for MacOS.
It supports all major operating systems like Linux, Windows, OpenBSD,FreeBSD etc.

Syntax help

1. CREATE

CREATE command is used to create a table, schema or an index.

Syntax:

         CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

2. ALTER

ALTER command is used to add, modify or delete columns or constraints from the database table.

Syntax

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE:

TRUNCATE command is used to delete the data present in the table but this will not delete the table.

Syntax

TRUNCATE table table_name;

4. DROP

DROP command is used to delete the table along with its data.

Syntax

DROP TABLE table_name;

5. RENAME

RENAME command is used to rename the table name.

Syntax

ALTER TABLE table_name1 RENAME to new_table_name1;

6. INSERT

INSERT Statement is used to insert new records into the database table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

7. SELECT

Select statement is used to select data from database tables.

Syntax:

SELECT column1, column2, ...
FROM table_name;

8. UPDATE

UPDATE statement is used to modify the existing values of records present in the database table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

9. DELETE

DELETE statement is used to delete the existing records present in the database table.

Syntax

DELETE FROM table_name where condition;