PostgreSQL

Create database:

create database mydb;

1	create database mydb;

Delete database:

drop database mydb;

1	drop database mydb;

Create table:

CREATE TABLE location_state (
    id int primary key,
    name_tx varchar(3) NOT NULL DEFAULT '',
    fullname_tx varchar(30) NOT NULL DEFAULT '');

CREATE TABLE mydb (
 id INT PRIMARY KEY,
 birth_timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
 parent_fk INTEGER,
 brand_tx VARCHAR(50) NOT NULL DEFAULT '',
 model_tx VARCHAR(50) NOT NULL DEFAULT '',
 ack_flag BOOLEAN DEFAULT FALSE);

CREATE TABLE IF NOT EXISTS db_version (
    id int PRIMARY KEY,
    birth_timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
    ver int
);

CREATE TABLE recipe_ingredient (
  recipe_fk CHAR(32) not null ,
  ingredient_fk int not null ,
  quantity_nr int not null,
  substitute_fk int,
  primary key (recipe_fk, ingredient_fk)
);

CREATE TABLE location_state (

id int primary key,

name_tx varchar(3) NOT NULL DEFAULT '',

fullname_tx varchar(30) NOT NULL DEFAULT '');

CREATE TABLE mydb (

id INT PRIMARY KEY,

birth_timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,

parent_fk INTEGER,

brand_tx VARCHAR(50) NOT NULL DEFAULT '',

model_tx VARCHAR(50) NOT NULL DEFAULT '',

ack_flag BOOLEAN DEFAULT FALSE);

CREATE TABLE IF NOT EXISTS db_version (

id int PRIMARY KEY,

birth_timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,

ver int

);

CREATE TABLE recipe_ingredient (

recipe_fk CHAR(32) not null ,

ingredient_fk int not null ,

quantity_nr int not null,

substitute_fk int,

primary key (recipe_fk, ingredient_fk)

);

Delete table:

drop table my_table;

1	drop table my_table;

Insert

INSERT INTO location_state (id, name_tx, fullname_tx)
VALUES
(1,'ACT','Australian Capital Territory'),
(2,'NSW','New South Wales'),
(3,'NT','Nothern Territory'),
(4,'QLD','Queensland'),
(5,'SA','South Australia'),
(6,'TAS','Tasmania'),
(7,'VIC','Victoria'),
(8,'WA','Western Australia');

INSERT INTO sm_state (name_tx, fullname_tx) VALUES ('TIMEOUT', 'Time Out');

INSERT INTO location_state (id, name_tx, fullname_tx)

VALUES

(1,'ACT','Australian Capital Territory'),

(2,'NSW','New South Wales'),

(3,'NT','Nothern Territory'),

(4,'QLD','Queensland'),

(5,'SA','South Australia'),

(6,'TAS','Tasmania'),

(7,'VIC','Victoria'),

(8,'WA','Western Australia');

INSERT INTO sm_state (name_tx, fullname_tx) VALUES ('TIMEOUT', 'Time Out');

Dump

pg_dump -o mydb -U postgres > mydb.sql
pg_dump -h localhost -U postgres mydb > mydb.sql

1 2	pg_dump -o mydb -U postgres > mydb.sql pg_dump -h localhost -U postgres mydb > mydb.sql

Restore

psql -U postgres -h localhost postgres < schema.sql
psql -U postgres -h localhost mydb < mydb.sql

1 2	psql -U postgres -h localhost postgres < schema.sql psql -U postgres -h localhost mydb < mydb.sql

**SELECT COUNT(*)**

select count(*) from mydb;

1	select count(*) from mydb;

Create user

create user john with password 'secret';

1	create user john with password 'secret';

Grant privileges

grant all privileges on database mydb to john;
grant all privileges on all tables in schema public to john;
grant all privileges on table1 to john;

grant all privileges on database mydb to john;

grant all privileges on all tables in schema public to john;

grant all privileges on table1 to john;

Rename column

alter table account_entity rename column "DTYPE" to "dtype";
ALTER TABLE public.account_contact RENAME COLUMN "mobile_number_tx" TO "phone1_tx";

1 2	alter table account_entity rename column "DTYPE" to "dtype"; ALTER TABLE public.account_contact RENAME COLUMN "mobile_number_tx" TO "phone1_tx";

Change password

alter user john with encrypted password "secret";

1	alter user john with encrypted password "secret";

Add auto-increment pk to existing table, where ID is pk

create sequence error_entity_seq;
alter table error_entity alter id set default nextval('error_entity_seq');
grant all privileges on sequence error_entity_seq to MYUSER;

create sequence error_entity_seq;

alter table error_entity alter id set default nextval('error_entity_seq');

grant all privileges on sequence error_entity_seq to MYUSER;

Modify sequence number

alter sequence error_entity_seq restart with 29;

1	alter sequence error_entity_seq restart with 29;

Delete sequence

drop sequence sm_state_seq;

1	drop sequence sm_state_seq;

Change column constraint to not null

alter table test_entity alter column parent_fk set not null;

1	alter table test_entity alter column parent_fk set not null;

Change column type from integer to smallint

alter table error_entity
alter column error_type_fk type smallint;

1 2	alter table error_entity alter column error_type_fk type smallint;

Change column type from varchar (20) to varchar (40)

alter table type_entity alter column name_tx type varchar (40);

1	alter table type_entity alter column name_tx type varchar (40);

Change column type from date to timestamp without time zone

alter table entity alter column expire_date type timestamp without time zone;

1	alter table entity alter column expire_date type timestamp without time zone;

Delete column

alter table error_entity drop column ack_flag;
ALTER TABLE account_login DROP COLUMN key_tx;

1 2	alter table error_entity drop column ack_flag; ALTER TABLE account_login DROP COLUMN key_tx;

Add column

alter table error_entity add column ack_flag boolean default false;
ALTER TABLE account_login ADD COLUMN validate_tx VARCHAR(64) DEFAULT '';
ALTER TABLE account_login ADD COLUMN code_exp_timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL;
ALTER TABLE brand ADD COLUMN priority INT DEFAULT 0;
ALTER TABLE moto_brand ADD COLUMN url_tx VARCHAR(128) DEFAULT '';

alter table error_entity add column ack_flag boolean default false;

ALTER TABLE account_login ADD COLUMN validate_tx VARCHAR(64) DEFAULT '';

ALTER TABLE account_login ADD COLUMN code_exp_timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL;

ALTER TABLE brand ADD COLUMN priority INT DEFAULT 0;

ALTER TABLE moto_brand ADD COLUMN url_tx VARCHAR(128) DEFAULT '';

Rename column

ALTER TABLE public.account_contact RENAME COLUMN "mobile_number_tx" TO "phone1_tx";

1	ALTER TABLE public.account_contact RENAME COLUMN "mobile_number_tx" TO "phone1_tx";

Add foreign key

alter table error_entity add column error_type_fk smallint;
alter table error_entity add constraint error_type_fkey 
    foreign key(error_type_fk) 
    references error_type_entity(id) match simple;

alter table error_entity add column error_type_fk smallint;

alter table error_entity add constraint error_type_fkey

foreign key(error_type_fk)

references error_type_entity(id) match simple;

Delete constraint

ALTER TABLE your_table DROP CONSTRAINT constraint_name;

1	ALTER TABLE your_table DROP CONSTRAINT constraint_name;

Delete data

DELETE FROM model;
DELETE FROM account_login
    WHERE id >= 3;

DELETE FROM model;

DELETE FROM account_login

WHERE id >= 3;

delete from moto_model where id in (7337, 7336, 7333, 7344);

1	delete from moto_model where id in (7337, 7336, 7333, 7344);

Select A where B has 0 entries

SELECT brand.id FROM brand brand
LEFT JOIN model model ON model.brand_fk = brand.id
GROUP BY brand.id
HAVING count(model.id) = 0;

SELECT brand.id FROM brand brand

LEFT JOIN model model ON model.brand_fk = brand.id

GROUP BY brand.id

HAVING count(model.id) = 0;

My IT thoughts collection

Tag Archives: PostgreSQL

Create database:

Delete database:

Create table:

Delete table:

Insert

Dump

Restore

**SELECT COUNT(*)**

Create user

Grant privileges

Rename column

Change password

Add auto-increment pk to existing table, where ID is pk

Modify sequence number

Delete sequence

Change column constraint to not null

Change column type from integer to smallint

Change column type from varchar (20) to varchar (40)

Change column type from date to timestamp without time zone

Delete column

Add column

Rename column

Add foreign key

Delete constraint

Delete data

Select A where B has 0 entries

by JadeBaboon Team