Create database:
1 |
create database mydb; |
Delete database:
1 |
drop database mydb; |
Create table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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:
1 |
drop table my_table; |
Insert
1 2 3 4 5 6 7 8 9 10 11 12 |
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
1 2 |
pg_dump -o mydb -U postgres > mydb.sql pg_dump -h localhost -U postgres mydb > mydb.sql |
Restore
1 2 |
psql -U postgres -h localhost postgres < schema.sql psql -U postgres -h localhost mydb < mydb.sql |
SELECT COUNT(*)
1 |
select count(*) from mydb; |
Create user
1 |
create user john with password 'secret'; |
Grant privileges
1 2 3 |
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
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
1 |
alter user john with encrypted password "secret"; |
Add auto-increment pk to existing table, where ID is pk
1 2 3 |
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
1 |
alter sequence error_entity_seq restart with 29; |
Delete sequence
1 |
drop sequence sm_state_seq; |
Change column constraint to not null
1 |
alter table test_entity alter column parent_fk set not null; |
Change column type from integer to smallint
1 2 |
alter table error_entity alter column error_type_fk type smallint; |
Change column type from varchar (20) to varchar (40)
1 |
alter table type_entity alter column name_tx type varchar (40); |
Change column type from date to timestamp without time zone
1 |
alter table entity alter column expire_date type timestamp without time zone; |
Delete column
1 2 |
alter table error_entity drop column ack_flag; ALTER TABLE account_login DROP COLUMN key_tx; |
Add column
1 2 3 4 5 |
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
1 |
ALTER TABLE public.account_contact RENAME COLUMN "mobile_number_tx" TO "phone1_tx"; |
Add foreign key
1 2 3 4 |
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
1 |
ALTER TABLE your_table DROP CONSTRAINT constraint_name; |
Delete data
1 2 3 |
DELETE FROM model; DELETE FROM account_login WHERE id >= 3; |
1 |
delete from moto_model where id in (7337, 7336, 7333, 7344); |
Select A where B has 0 entries
1 2 3 4 |
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; |