Databases

Post Example

Operations related to database configuration, connecting, data management, and data querying.

Examples in SQL
Return one null row if no matches
by yhuang SQL
Operations related to database configuration, connecting, data management, and data querying.
select id from (values(0)) zero left join app.tag on name in ('foo', 'bar');
Set Role for User in Postgres
by yhuang SQL 
# super user role
alter role  SUPERUSER;
Delete Postgres Database
by yhuang SQL 
drop database dbname;
Update Table Column in Postgres
by yhuang SQL 
# Change column type.
ALTER TABLE some_table ALTER COLUMN some_column TYPE varchar(500);

# Make column not accept nulls.
ALTER TABLE some_table ALTER COLUMN some_column SET NOT NULL;

# Make column accept nulls.
ALTER TABLE some_table ALTER COLUMN some_column DROP NOT NULL;

# Drop default.
ALTER TABLE some_table ALTER column some_column DROP default;
Show Table Schema in Postgres
by yhuang SQL 
\d+ dbschema.dbtable;
Drop Table in Postgres
by yhuang SQL 
drop table table_name;
Drop Index in Postgres
by yhuang SQL 
DROP INDEX index_name;

DROP INDEX IF EXISTS index_name;
Full Text Search in Postgres
by yhuang SQL 
# Create text search configuration.
create text search configuration devbits_config (copy=simple); # Uses simple as a template.

# Alter mapping in text search configuration.
alter text search configuration my_config alter mapping for asciiword with my_dictionary;
alter text search configuration my_config alter mapping for asciiword with my_dictionary, simple; # falls back to simple

# Remove mapping in text search configuration.
alter text search configuration my_config drop mapping if exists for url;

# Delete text search configurtion.
drop text search configuration my_config;

# Create text search dictionary.
create text search dictionary my_dictionary (
	template = thesaurus,
	dictfile = my_thesaurus_file,  # Must be located in postgres shared dir.
	dictionary = simple            # Dictionary for thesaurus
);

# Drop text search dictionary.
drop text search dictionary my_dictionary;
Query Postgres Table for Tree Node Path
by yhuang SQL 
create table objects (
    id int primary key,
    parent_id int,
    label text,
    content text);

insert into objects values
(1, 4, 'f', 'data for f'),
(2, 5, 'e', 'data for e'),
(3, 5, 'd', 'data for d'),
(4, 6, 'c', ''),
(5, 6, 'b', ''),
(6, 0, 'a', '');

with recursive nodes(id, path, content) as (
    select id, label, content
    from objects
    where parent_id = 0
union all
    select o.id, concat(path, '->', label), o.content
    from objects o
    join nodes n on n.id = o.parent_id
)
select *
from nodes
order by id desc;

 id |  path   |  content   
----+---------+------------
  6 | a       | 
  5 | a->b    | 
  4 | a->c    | 
  3 | a->b->d | data for d
  2 | a->b->e | data for e
  1 | a->c->f | data for f
(6 rows)
Rename Table in Postgres
by yhuang SQL 
ALTER TABLE table_name RENAME TO new_table_name;
Rename Table Column in Postgres
by yhuang SQL 
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;