CONTENT

CHANGES

Szukaj

#top Zapytania SQL¶

[<<]|[<]|[^]|[v]|[>]|[>>]|[Zwiń]|[#top]|[X]

#top users¶

#top USER CREATE¶

Zobacz także USER CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja PostgreSQL:
http://postgresql.org/docs/8.4/interactive/sql-createuser.html
http://postgresql.org/docs/8.4/interactive/sql-createrole.html

CREATE USER is now an alias for CREATE ROLE.
The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE.

Składnia:

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:
    
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE rolename [, ...]
    | IN GROUP rolename [, ...]
    | ROLE rolename [, ...]
    | ADMIN rolename [, ...]
    | USER rolename [, ...]
    | SYSID uid

Example: utworzenie użytkownika wraz hasłem:

CREATE ROLE "temp" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD 'temp';

Example: utworzenie użytkownika wraz z hasłem poprzez nadanie uprawnień:
W odróżnieniu od MySQL w PostgreSQL nie można utworzyć konta użytkownika poprzez nadanie uprawnień do bazy danych. Każda baza danych przed utworzeniem wymaga utworzenia użytkownika, który będzie jej właścicielem (WARNING: STANOWCZO ODRADZANE, NIE NALEŻY UŻYWAĆ ROLI postgres JAKO WŁAŚCICIELA TWORZONYCH BAZ DANYCH (WZGLĘDY BEZPIECZEŃSTWA oraz spójności konwencji nazewniczej)).
Wskazane dla każdej nowo tworzonej bazy danych (dla spójności konwencji nazewniczej) tworzenie użytkownika z suffixem root jako właściciela bazy danych bez możliwości logowania NOLOGIN, a następnie tworzenie użytkowników/rol i nadawanie określonych niezbędnych uprawnień do tworzonej bazy danych, analogicznie jak to zostało przedstawione poniżej.

Example: Utworzenie roli (bez możliwości logowania NOLOGIN) jako właściciela bazy danych oraz utworzenie roli wraz z hasłem i nadanie tylko niezbędnych uprawnień użytkownikowi do połączenia z bazą danych (na przykładzie projektu statadm: baza danych statadm, właściciel bazy danych statadmroot, użytkownik bazy danychstatadm): OK: STANOWCZO WSKAZANE, NIE NALEŻY NADAWAĆ UPRAWNIEŃ WIĘKSZYCH NIŻ SĄ WYMAGANE DO POPRAWNEGO DZIAŁANIA APLIKACJI (WZGLĘDY BEZPIECZEŃSTWA)
psql -U postgres template1

CREATE ROLE "statadmroot" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN ENCRYPTED PASSWORD 'statadmroot';
CREATE ROLE "statadm" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD 'statadm';
CREATE DATABASE "statadm" WITH OWNER="statadmroot";
REVOKE ALL ON DATABASE "statadm" FROM public;
GRANT CONNECT ON DATABASE "statadm" TO "statadm";

Nadanie stosownych uprawnień do schematu w tym przypadku public, aby użytkownik mógł wyświetlić tabele, sekwencje oraz wykonywać na nich operacje.
psql -U postgres statadm

GRANT ALL ON SCHEMA public TO "statadmroot" WITH GRANT OPTION;
GRANT USAGE ON SCHEMA public TO "statadm";

#top USER ALTER¶

ALTER USER name [ [ WITH ] option [ ... ] ]

where option can be:
    
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'

ALTER USER name RENAME TO newname

ALTER USER name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER USER name SET configuration_parameter FROM CURRENT
ALTER USER name RESET configuration_parameter
ALTER USER name RESET ALL

Example:

-- comment

#top USER SET PASSWORD¶

ALTER USER name [ [ WITH ] option [ ... ] ]
where option can be:
      [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'

Example: Change a role's password:

ALTER ROLE davide WITH PASSWORD 'hu8jmn3';

Example: Remove a role's password:

ALTER ROLE davide WITH PASSWORD NULL;

#top USER DROP¶

DROP ROLE [ IF EXISTS ] name [, ...]

Example: odebranie nadanych / wszystkich uprawnień użytkownikowi, a następnie skasowanie użytkownika

-- comment

#top permissions¶

#top GRANT¶

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
    [,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
    ON [ TABLE ] tablename [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [,...] | ALL [ PRIVILEGES ] }
    ON SEQUENCE sequencename [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE dbname [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdwname [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER servername [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE langname [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schemaname [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespacename [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT role [, ...] TO rolename [, ...] [ WITH ADMIN OPTION ]

The possible privileges are:
Global Privileges

ALL PRIVILEGES - Grant all of the available privileges at once. The PRIVILEGES key word is optional in PostgreSQL, though it is required by strict SQL.
USAGE - For servers, this privilege enables the grantee to create, alter, and drop his own user's user mappings associated with that server. Also, it enables the grantee to query the options of the server and associated user mappings.

Tablespace Privileges

CREATE - For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace. (Note that revoking this privilege will not alter the placement of existing objects.)

Database Privileges

CONNECT - Allows the user to connect to the specified database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf).
CREATE - For databases, allows new schemas to be created within the database.

Schema Privileges

CREATE - For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.
USAGE - For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables. Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access.

Table Privileges

SELECT - Allows SELECT from any column, or the specific columns listed, of the specified table, view, or sequence. Also allows the use of COPY TO. This privilege is also needed to reference existing column values in UPDATE or DELETE.
INSERT - Allows INSERT of a new row into the specified table. If specific columns are listed, only those columns may be assigned to in the INSERT command (other columns will therefore receive default values). Also allows COPY FROM.
UPDATE - Allows UPDATE of any column, or the specific columns listed, of the specified table. (In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and:or to compute new values for columns.) SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege on at least one column, in addition to the SELECT privilege.
DELETE - Allows DELETE of a row from the specified table. (In practice, any nontrivial DELETE command will require SELECT privilege as well, since it must reference table columns to determine which rows to delete.)
TRUNCATE - Allows TRUNCATE on the specified table.
REFERENCES - To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced columns. The privilege may be granted for all columns of a table, or just specific columns.
TEMPORARY, TEMP - Allows temporary tables to be created while using the specified database.

Column Privileges

SELECT - Allows SELECT from any column, or the specific columns listed, of the specified table, view, or sequence. Also allows the use of COPY TO. This privilege is also needed to reference existing column values in UPDATE or DELETE.
INSERT - Allows INSERT of a new row into the specified table. If specific columns are listed, only those columns may be assigned to in the INSERT command (other columns will therefore receive default values). Also allows COPY FROM.
UPDATE - Allows UPDATE of any column, or the specific columns listed, of the specified table. (In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and:or to compute new values for columns.) SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege on at least one column, in addition to the SELECT privilege.
REFERENCES - To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced columns. The privilege may be granted for all columns of a table, or just specific columns.

Sequences

USAGE - For sequences, this privilege allows the use of the currval and nextval functions.
SELECT - For sequences, this privilege also allows the use of the currval function.
UPDATE - For sequences, this privilege allows the use of the nextval and setval functions.

Stored Routine Privileges

TRIGGER - Allows the creation of a trigger on the specified table. (See the CREATE TRIGGER statement.)
EXECUTE - Allows the use of the specified function and the use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions. (This syntax works for aggregate functions, as well.)

Example: nadanie wszytkich uprawnień: WARNING: STANOWCZO ODRADZANE, NIE NALEŻY NADAWAĆ UPRAWNIEŃ WIĘKSZYCH NIŻ SĄ WYMAGANE DO POPRAWNEGO DZIAŁANIA APLIKACJI (WZGLĘDY BEZPIECZEŃSTWA)

GRANT ALL ON DATABASE "temp" TO "temproot" WITH GRANT OPTION;

Example: uprawnienia związane bazami danych (wymagany wpis w pliku pg_hba.conf)

-- (pgsql >= 8.4.4)
GRANT CONNECT ON DATABASE "temp" TO "temp";

Example: uprawnienia związane z procedurami / funkcjami

GRANT EXECUTE ON LANGUAGE langname TO "temp";

Example: uprawnienia związane ze schematami

GRANT USAGE ON SCHEMA public TO "temp";

Example: uprawnienia związane z tabelami

GRANT SELECT,INSERT,UPDATE,DELETE ON "public"."temp_table" TO "statadm";
-- (pgsql >= 8.4.4)
GRANT TRUNCATE ON "public"."temp_table" TO "temp";

#top REVOKE¶

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    FROM { [ GROUP ] rolename | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
    [,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
    ON [ TABLE ] tablename [, ...]
    FROM { [ GROUP ] rolename | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { USAGE | SELECT | UPDATE }
    [,...] | ALL [ PRIVILEGES ] }
    ON SEQUENCE sequencename [, ...]
    FROM { [ GROUP ] rolename | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE dbname [, ...]
    FROM { [ GROUP ] rolename | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdwname [, ...]
    FROM { [ GROUP ] rolename | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER servername [, ...]
    FROM { [ GROUP ] rolename | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
    FROM { [ GROUP ] rolename | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE langname [, ...]
    FROM { [ GROUP ] rolename | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schemaname [, ...]
    FROM { [ GROUP ] rolename | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespacename [, ...]
    FROM { [ GROUP ] rolename | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ ADMIN OPTION FOR ]
    role [, ...] FROM rolename [, ...]
    [ CASCADE | RESTRICT ]

Example: Odebranie wszytkich uprawnień: OK: STANOWCZO WSKAZANE, NIE NALEŻY NADAWAĆ UPRAWNIEŃ WIĘKSZYCH NIŻ SĄ WYMAGANE DO POPRAWNEGO DZIAŁANIA APLIKACJI (WZGLĘDY BEZPIECZEŃSTWA)

REVOKE ALL ON DATABASE "statadm" FROM "statadm";
REVOKE USAGE ON SCHEMA public FROM "statadm";

#top SHOW GRANTS¶

comment

Example:

-- comment

#top databases¶

#top DATABASE CREATE¶

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] dbowner ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace ]
           [ CONNECTION LIMIT [=] connlimit ] ]

Example: utworzenie bazy danych (jeśli nie istnieje) z określonym użytkownikiem jako właścicielem

CREATE DATABASE IF NOT EXISTS "temp" WITH OWNER="temproot";

Example: (pgsql <= 8.1.11): utworzenie bazy danych z odpowiednim kodowaniem

CREATE DATABASE "temp" WITH ENCODING 'EUC_KR' TEMPLATE=template0;

Example: (pgsql >= 8.4.4): utworzenie bazy danych z odpowiednim kodowaniem oraz kodowaniem znaków stosowanych podczas porównywania (klauzule WHERE)

CREATE DATABASE "temp" WITH ENCODING 'UTF8' LC_COLLATE='UTF8.euc_kr' LC_CTYPE='UTF8.euc_kr' TEMPLATE=template0;
ERROR:  invalid locale name UTF8.euc_kr

CREATE DATABASE "temp" WITH ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr' LC_CTYPE='ko_KR.euckr' TEMPLATE=template0;

#top DATABASE ALTER¶

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    CONNECTION LIMIT connlimit

ALTER DATABASE name RENAME TO newname

ALTER DATABASE name OWNER TO new_owner

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

Example:

-- comment

#top DATABASE DROP¶

DROP DATABASE [ IF EXISTS ] name

Example:

DROP DATABASE "temp";

#top schemas¶

#top SCHEMA CREATE¶

CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]

Example:

-- comment

#top SCHEMA ALTER¶

ALTER SCHEMA name RENAME TO newname
ALTER SCHEMA name OWNER TO newowner

Example:

-- comment

#top SCHEMA DROP¶

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Example:

-- comment

#top tablespaces¶

#top TABLESPACE CREATE¶

CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory'

Najpierw należy utworzyć stosowne katalogi z odpowiednimi uprawnieniami:

mkdir -p /srv/pgdata/edata
mkdir -p /srv/pgdata/eindex
chown -R postgres:postgres /srv/pgdata

w przeciwnym przypadku przy próbie utworzenia tablespace bez istniejących podkatalogów wyświetlony zostanie komunikat:
ERROR: could not set permissions on directory "/srv/pgdata/edata": No such file or directory
ERROR: could not set permissions on directory "/srv/pgdata/eindex": No such file or directory

Example:

CREATE TABLESPACE dbtablespace LOCATION '/srv/pgdata/edata';
CREATE TABLESPACE dbindexspace LOCATION '/srv/pgdata/eindex';

#top TABLESPACE ALTER¶

ALTER TABLESPACE name RENAME TO newname
ALTER TABLESPACE name OWNER TO newowner

Example:

ALTER TABLESPACE dbtablespace RENAME TO dbtable2space;
ALTER TABLESPACE dbindexspace RENAME TO dbindex2space;
ALTER TABLESPACE dbtable2space RENAME TO dbtablespace;
ALTER TABLESPACE dbindex2space RENAME TO dbindexspace;

Example:

ALTER TABLESPACE dbtablespace OWNER TO "temp";
ALTER TABLESPACE dbindexspace OWNER TO "temp";

#top TABLESPACE DROP¶

DROP TABLESPACE [ IF EXISTS ] tablespacename

Example:

DROP TABLESPACE dbtablespace;
DROP TABLESPACE dbindexspace;

#top tables¶

#top TABLE CREATE¶

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
  { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES } ] ... }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  CHECK ( expression ) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  CHECK ( expression ) |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

index_parameters in UNIQUE and PRIMARY KEY constraints are:

[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace ]

#top TABLE CREATE LIKE¶

CREATE TABLE temp_table_like (LIKE temp_table);
CREATE TABLE temp_table_like (LIKE temp_table INCLUDING DEFAULTS);
CREATE TABLE temp_table_like (LIKE temp_table INCLUDING CONSTRAINTS);
CREATE TABLE temp_table_like (LIKE temp_table INCLUDING INDEXES);

#top TABLE CREATE AS¶

CREATE TABLE temp_table_as AS SELECT * FROM temp_table LIMIT 0;

#top TABLE CREATE with PRIMARY KEY¶

#top TABLE CREATE with auto_increment¶

CREATE SEQUENCE temp_autoinc_idtemp_seq;
CREATE TABLE temp_prikey (
idtemp integer NOT NULL DEFAULT nextval('temp_autoinc_idtemp_seq'),
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
);

#top TABLE CREATE with UNIQUE KEY (singlecolumn)¶

CREATE TABLE temp_unikey (
idtemp integer NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
UNIQUE (name)
);

#top TABLE CREATE with UNIQUE KEY (multicolumn)¶

CREATE TABLE temp_unikey (
idtemp integer NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
UNIQUE (name,value)
);

#top TABLE CREATE with FULLTEXT KEY¶

CREATE SEQUENCE word_fulltext_idword_seq;
CREATE TABLE words_fulltext (
idword integer DEFAULT nextval('word_fulltext_idword_seq') NOT NULL,
title varchar(255) NOT NULL,
body text NOT NULL,
PRIMARY KEY (idword)
);

INSERT INTO words_fulltext (title,body) VALUES ('friend metting','my friend come to me');
INSERT INTO words_fulltext (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...');
INSERT INTO words_fulltext (title,body) VALUES ('How To Use MySQL Well','After you went through a ...');
INSERT INTO words_fulltext (title,body) VALUES ('Optimizing MySQL','In this tutorial we will show ...');
INSERT INTO words_fulltext (title,body) VALUES ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...');
INSERT INTO words_fulltext (title,body) VALUES ('MySQL vs. YourSQL','In the following database comparison ...');
INSERT INTO words_fulltext (title,body) VALUES ('MySQL Security','When configd properly, MySQL ...');

SELECT * FROM words_fulltext WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
 idword |     title      |         body
--------+----------------+----------------------
      1 | friend metting | my friend come to me

SELECT * FROM words_fulltext WHERE to_tsvector(body) @@ to_tsquery('friend');
 idword |     title      |         body
--------+----------------+----------------------
      1 | friend metting | my friend come to me

SELECT * FROM words_fulltext WHERE to_tsvector(body) @@ to_tsquery('database');
 idword |       title       |                   body
--------+-------------------+------------------------------------------
      2 | MySQL Tutorial    | DBMS stands for DataBase ...
      6 | MySQL vs. YourSQL | In the following database comparison ...

SELECT * FROM words_fulltext WHERE to_tsvector(body) @@ to_tsquery('come');
 idword |     title      |         body
--------+----------------+----------------------
      1 | friend metting | my friend come to me

SELECT * FROM words_fulltext WHERE to_tsvector(body) @@ to_tsquery('to me');
ERROR:  syntax error in tsquery: "to me"

#top TABLE CREATE with FOREIGN KEY¶

CREATE TABLE users (
id int NOT NULL,
login varchar(30) NOT NULL DEFAULT '',
hashed_password varchar(40) NOT NULL DEFAULT '',
firstname varchar(30) NOT NULL DEFAULT '',
lastname varchar(30) NOT NULL DEFAULT '',
mail varchar(60) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);

CREATE TABLE user_preferences (
id int NOT NULL,
user_id int NOT NULL DEFAULT '0',
others text,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users (id)
);

#top TABLE CREATE with INDEX¶

-- comment

#top TABLE CREATE with partitioned¶

mkdir -p /srv/pgdata/edata
mkdir -p /srv/pgdata/eindex
chown -R postgres:postgres /srv/pgdata

create tablespaces
ERROR: permission denied to create tablespace "dbtablespace"
HINT: Must be superuser to create a tablespace.psql -U postgres template1

CREATE TABLESPACE dbtablespace LOCATION '/srv/pgdata/edata';
CREATE TABLESPACE dbindexspace LOCATION '/srv/pgdata/eindex';
ALTER TABLESPACE dbtablespace OWNER TO "temp";
ALTER TABLESPACE dbindexspace OWNER TO "temp";

now create table with DATA DIRECTORY + INDEX DIRECTORY
psql -U temp partitioneddb

CREATE TABLE usersloghistory (
iduser integer NOT NULL,
startdate date NOT NULL,
enddate   date NOT NULL
) TABLESPACE dbtablespace;
CREATE INDEX iduser_2 ON usersloghistory USING HASH (iduser) TABLESPACE dbindexspace;
CREATE INDEX startdate_2 ON usersloghistory USING BTREE (startdate) TABLESPACE dbindexspace;
CREATE INDEX enddate_2 ON usersloghistory USING BTREE (enddate) TABLESPACE dbindexspace;

check results

ll /srv/pgdata/edata /srv/pgdata/eindex

/srv/pgdata/edata:
total 16
drwx------ 2 postgres postgres 4096 2013-02-14 23:06 1/
-rw------- 1 postgres postgres    4 2013-02-14 23:04 PG_VERSION
stat_myphp
/srv/pgdata/eindex:
total 16
drwx------ 2 postgres postgres 4096 2013-02-14 23:08 1/
-rw------- 1 postgres postgres    4 2013-02-14 23:04 PG_VERSION

#top TABLE CREATE with COLLATE without UNIQUE¶

SET NAMES 'utf8';
SET CLIENT_ENCODING TO 'UTF-8';

CREATE SEQUENCE words_cs_idword_seq;
CREATE TABLE words_cs (
idword integer DEFAULT nextval('words_cs_idword_seq') NOT NULL,
wordname varchar(255) NOT NULL,
PRIMARY KEY  (idword)
);

CREATE SEQUENCE words_ci_idword_seq;
CREATE TABLE words_ci (
idword integer DEFAULT nextval('words_ci_idword_seq') NOT NULL,
wordname varchar(255) NOT NULL,
PRIMARY KEY  (idword)
);
CREATE UNIQUE INDEX words_ci_wordname_idx ON words_ci (lower(wordname));

INSERT INTO words_cs (wordname) VALUES ('ZAJĄC');
INSERT INTO words_cs (wordname) VALUES ('zając');
INSERT INTO words_cs (wordname) VALUES ('ZaJąC');
INSERT INTO words_cs (wordname) VALUES ('zAjĄc');

INSERT INTO words_ci (wordname) VALUES ('ZAJĄC');
INSERT INTO words_ci (wordname) VALUES ('zając');
INSERT INTO words_ci (wordname) VALUES ('ZaJąC');
INSERT INTO words_ci (wordname) VALUES ('zAjĄc');
INSERT 0 1
ERROR:  duplicate key violates unique constraint "words_ci_wordname_idx"
ERROR:  duplicate key violates unique constraint "words_ci_wordname_idx"
ERROR:  duplicate key violates unique constraint "words_ci_wordname_idx"

SELECT * FROM words_cs;
 idword | wordname
--------+----------
      1 | ZAJĄC
      2 | zając
      3 | ZaJąC
      4 | zAjĄc

SELECT * FROM words_ci;
 idword | wordname
--------+----------
      1 | ZAJĄC

SELECT * FROM words_cs WHERE wordname ILIKE 'ZAJĄC';
 idword | wordname
--------+----------
      1 | ZAJĄC
      4 | zAjĄc

SELECT * FROM words_ci WHERE wordname ILIKE 'ZAJĄC';
 idword | wordname
--------+----------
      1 | ZAJĄC

SELECT * FROM words_cs WHERE LOWER(wordname)=LOWER('ZAJĄC');
 idword | wordname
--------+----------
      1 | ZAJĄC
      2 | zając
      3 | ZaJąC
      4 | zAjĄc

SELECT * FROM words_ci WHERE LOWER(wordname)=LOWER('ZAJĄC');
 idword | wordname
--------+----------
      1 | ZAJĄC

#top TABLE CREATE with COLLATE with UNIQUE¶

SET NAMES 'utf8';
SET CLIENT_ENCODING TO 'UTF-8';

CREATE SEQUENCE words_cs_idword_seq;
CREATE TABLE words_cs (
idword integer DEFAULT nextval('words_cs_idword_seq') NOT NULL,
wordname varchar(255) NOT NULL,
PRIMARY KEY  (idword),
UNIQUE (wordname)
);

CREATE SEQUENCE words_ci_idword_seq;
CREATE TABLE words_ci (
idword integer DEFAULT nextval('words_ci_idword_seq') NOT NULL,
wordname varchar(255) NOT NULL,
PRIMARY KEY  (idword),
UNIQUE (wordname)
);
CREATE UNIQUE INDEX words_ci_wordname_idx ON words_ci (lower(wordname));

INSERT INTO words_cs (wordname) VALUES ('ZAJĄC');
INSERT INTO words_cs (wordname) VALUES ('zając');
INSERT INTO words_cs (wordname) VALUES ('ZaJąC');
INSERT INTO words_cs (wordname) VALUES ('zAjĄc');

INSERT INTO words_ci (wordname) VALUES ('ZAJĄC');
INSERT INTO words_ci (wordname) VALUES ('zając');
INSERT INTO words_ci (wordname) VALUES ('ZaJąC');
INSERT INTO words_ci (wordname) VALUES ('zAjĄc');
INSERT 0 1
ERROR:  duplicate key violates unique constraint "words_ci_wordname_idx"
ERROR:  duplicate key violates unique constraint "words_ci_wordname_idx"
ERROR:  duplicate key violates unique constraint "words_ci_wordname_idx"

SELECT * FROM words_cs;
 idword | wordname
--------+----------
      1 | ZAJĄC
      2 | zając
      3 | ZaJąC
      4 | zAjĄc

SELECT * FROM words_ci;
 idword | wordname
--------+----------
      1 | ZAJĄC

SELECT * FROM words_cs WHERE wordname ILIKE 'ZAJĄC';
 idword | wordname
--------+----------
      1 | ZAJĄC
      4 | zAjĄc

SELECT * FROM words_ci WHERE wordname ILIKE 'ZAJĄC';
 idword | wordname
--------+----------
      1 | ZAJĄC

SELECT * FROM words_cs WHERE LOWER(wordname)=LOWER('ZAJĄC');
 idword | wordname
--------+----------
      1 | ZAJĄC
      2 | zając
      3 | ZaJąC
      4 | zAjĄc

SELECT * FROM words_ci WHERE LOWER(wordname)=LOWER('ZAJĄC');
 idword | wordname
--------+----------
      1 | ZAJĄC

#top TABLE ALTER¶

ALTER TABLE [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
    RENAME TO new_name
ALTER TABLE name
    SET SCHEMA new_schema

where action is one of:

    ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
    DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ]
    ALTER [ COLUMN ] column SET DEFAULT expression
    ALTER [ COLUMN ] column DROP DEFAULT
    ALTER [ COLUMN ] column { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column SET STATISTICS integer
    ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD table_constraint
    DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    DISABLE RULE rewrite_rule_name
    ENABLE RULE rewrite_rule_name
    ENABLE REPLICA RULE rewrite_rule_name
    ENABLE ALWAYS RULE rewrite_rule_name
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITH OIDS
    SET WITHOUT OIDS
    SET ( storage_parameter = value [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OWNER TO new_owner
    SET TABLESPACE new_tablespace

#top TABLE ALTER with PRIMARY KEY¶

CREATE TABLE temp_prikey (
idtemp integer NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
);

ALTER TABLE temp_prikey ADD PRIMARY KEY (idtemp);

#top TABLE ALTER with auto increment¶

CREATE TABLE temp_autoinc (
idtemp integer NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
);

CREATE SEQUENCE temp_autoinc_idtemp_seq;
ALTER TABLE temp_autoinc ALTER COLUMN idtemp SET DEFAULT nextval('temp_autoinc_idtemp_seq');

#top TABLE ALTER with UNIQUE KEY (singlecolumn)¶

CREATE TABLE temp_unikey (
idtemp integer NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
);

ALTER TABLE temp_unikey ADD UNIQUE (name);

#top TABLE ALTER with UNIQUE KEY (multicolumn)¶

CREATE TABLE temp_unikey (
idtemp integer NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
);

ALTER TABLE temp_unikey ADD UNIQUE (name,value);

#top TABLE ALTER with FULLTEXT KEY¶

#top TABLE ALTER with FOREIGN KEY¶

CREATE TABLE users (
id int NOT NULL,
login varchar(30) NOT NULL DEFAULT '',
hashed_password varchar(40) NOT NULL DEFAULT '',
firstname varchar(30) NOT NULL DEFAULT '',
lastname varchar(30) NOT NULL DEFAULT '',
mail varchar(60) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);

CREATE TABLE user_preferences (
id int NOT NULL,
user_id int NOT NULL DEFAULT '0',
others text,
PRIMARY KEY (id)
);

ALTER TABLE user_preferences ADD FOREIGN KEY (user_id) REFERENCES users (id);

#top TABLE ALTER with INDEX¶

CREATE SEQUENCE temp_index_idtemp_seq;
CREATE TABLE temp_index (
idtemp integer NOT NULL DEFAULT nextval('temp_index_idtemp_seq'),
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
);

CREATE INDEX temp_index_nameidx ON temp_index USING HASH (name);

#top TABLE ALTER with partitioned¶

#top TABLE ALTER with COLLATE without UNIQUE¶

#top TABLE ALTER with COLLATE with UNIQUE¶

#top TABLE RENAME¶

# To rename an existing table:
ALTER TABLE distributors RENAME TO suppliers;

Example: rename table

-- comment

#top TABLE TRUNCATE¶

Zobacz także TABLE TRUNCATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja PostgreSQL:
http://postgresql.org/docs/8.4/interactive/sql-truncate.html

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

Składnia:

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

Example:

TRUNCATE TABLE temp_table;

#top TABLE DROP¶

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Example:

DROP TABLE temp_prikey;

DROP TABLE temp_unikey;

DROP TABLE temp_autoinc;
DROP SEQUENCE temp_autoinc;

ALTER TABLE user_preferences DROP CONSTRAINT user_preferences_user_id_fkey;
DROP TABLE user_preferences;
DROP TABLE users;

DROP TABLE words_cs;
DROP SEQUENCE words_cs_idword_seq;
DROP TABLE words_ci;
DROP SEQUENCE words_ci_idword_seq;

DROP TABLE temp_table;

#top sequences¶

#top SEQUENCE CREATE¶

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table.column | NONE } ]

Example:

CREATE SEQUENCE settings_id_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

#top SEQUENCE ALTER¶

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ]
    [ RESTART [ [ WITH ] restart ] ]
    [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table.column | NONE } ]
ALTER SEQUENCE name OWNER TO new_owner
ALTER SEQUENCE name RENAME TO new_name
ALTER SEQUENCE name SET SCHEMA new_schema

Example: update owner/owned sequence

ALTER SEQUENCE public.settings_id_seq OWNED BY settings.id;
ALTER TABLE public.settings_id_seq OWNER TO chiliproject;

Example: update start value sequence

ALTER SEQUENCE settings_id_seq   START WITH  5;

Example: update increment value sequence

ALTER SEQUENCE settings_id_seq INCREMENT BY 1;

Example: update last value sequence

ALTER SEQUENCE settings_id_seq RESTART WITH 28;
-- the same as above:
SELECT pg_catalog.setval('settings_id_seq', 28, true);

#top SEQUENCE DROP¶

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Example:

DROP SEQUENCE temp_autoinc;
DROP SEQUENCE words_cs_idword_seq;
DROP SEQUENCE words_ci_idword_seq;

#top indexes¶

#top INDEX CREATE¶

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]

Example: alter table add INDEX

CREATE INDEX temp_index_nameidx ON temp_index USING HASH (name);

#top INDEX ALTER¶

ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name
ALTER INDEX name SET ( storage_parameter = value [, ... ] )
ALTER INDEX name RESET ( storage_parameter [, ... ] )

Example: alter table add INDEX

-- comment

#top REINDEX¶

Dokumentacja PostgreSQL:
http://postgresql.org/docs/8.4/interactive/sql-reindex.html

Składnia:

REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]

Example: alter table drop INDEX

-- comment

#top INDEX DROP¶

DROP INDEX [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Example: alter table drop INDEX

DROP INDEX temp_index_nameidx;

#top functions¶

#top Functions and Operators¶

-- comment

#top DATA Manipulations¶

#top SELECT with LIMIT and OFFSET¶

SELECT ...
LIMIT { count | ALL }
OFFSET start

Example:

CREATE TABLE temp_limit (
idtemp integer NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
);

INSERT INTO temp_limit(idtemp,name,value) VALUES(1,'name-01','val-01');
INSERT INTO temp_limit(idtemp,name,value) VALUES(2,'name-02','val-02');
INSERT INTO temp_limit(idtemp,name,value) VALUES(3,'name-03','val-03');
INSERT INTO temp_limit(idtemp,name,value) VALUES(4,'name-04','val-04');
INSERT INTO temp_limit(idtemp,name,value) VALUES(5,'name-05','val-05');
INSERT INTO temp_limit(idtemp,name,value) VALUES(6,'name-06','val-06');
INSERT INTO temp_limit(idtemp,name,value) VALUES(7,'name-07','val-07');
INSERT INTO temp_limit(idtemp,name,value) VALUES(8,'name-08','val-08');
INSERT INTO temp_limit(idtemp,name,value) VALUES(9,'name-09','val-09');

Po wykonaniu powyższych zapytań mających na celu utworzenie tabeli oraz dodaniu przykładowych danych można wykonać poniższe zapytania w celu sprawdzenia działania klauzul LIMIT przy wyświetlaniu danych z tabeli:

SELECT * FROM temp_limit LIMIT 3;
SELECT * FROM temp_limit LIMIT 5;
SELECT * FROM temp_limit LIMIT 7;

Uzyskany rezultat powinien być analogiczny do poniższego:

# SELECT * FROM temp_limit LIMIT 3;
 idtemp |  name   | value
--------+---------+--------
      1 | name-01 | val-01
      2 | name-02 | val-02
      3 | name-03 | val-03
(3 rows)

# SELECT * FROM temp_limit LIMIT 5;
 idtemp |  name   | value
--------+---------+--------
      1 | name-01 | val-01
      2 | name-02 | val-02
      3 | name-03 | val-03
      4 | name-04 | val-04
      5 | name-05 | val-05
(5 rows)

# SELECT * FROM temp_limit LIMIT 7;
 idtemp |  name   | value
--------+---------+--------
      1 | name-01 | val-01
      2 | name-02 | val-02
      3 | name-03 | val-03
      4 | name-04 | val-04
      5 | name-05 | val-05
      6 | name-06 | val-06
      7 | name-07 | val-07
(7 rows)

SELECT * FROM temp_limit LIMIT 5 OFFSET 0;
SELECT * FROM temp_limit LIMIT 5 OFFSET 3;
SELECT * FROM temp_limit LIMIT 5 OFFSET 6;

Uzyskany rezultat powinien być analogiczny do poniższego:

# SELECT * FROM temp_limit LIMIT 5 OFFSET 0;
 idtemp |  name   | value
--------+---------+--------
      1 | name-01 | val-01
      2 | name-02 | val-02
      3 | name-03 | val-03
      4 | name-04 | val-04
      5 | name-05 | val-05
(5 rows)

# SELECT * FROM temp_limit LIMIT 5 OFFSET 3;
 idtemp |  name   | value
--------+---------+--------
      4 | name-04 | val-04
      5 | name-05 | val-05
      6 | name-06 | val-06
      7 | name-07 | val-07
      8 | name-08 | val-08
(5 rows)

# SELECT * FROM temp_limit LIMIT 5 OFFSET 6;
 idtemp |  name   | value
--------+---------+--------
      7 | name-07 | val-07
      8 | name-08 | val-08
      9 | name-09 | val-09
(3 rows)

#top WHERE IN subselect with multiple columns¶

#top Deleting duplicates rows¶

Zobacz także Deleting duplicates rows dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja WIKI PostgreSQL: Deleting duplicates

A frequent question in IRC is how to delete rows that are duplicates over a set of columns, keeping only the one with the lowest ID.

This query does that for all rows of tablename having the same column1, column2, and column3.

DELETE FROM tablename
WHERE id IN (SELECT id
              FROM (SELECT id,
                             ROW_NUMBER() OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum
                     FROM tablename) t
              WHERE t.rnum > 1);

Sometimes a timestamp field is used instead of an ID field

#top REPLACE INTO¶

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2) SELECT 3, 'C', 'Z' WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

Dokumentacja PostgreSQL:
https://www.postgresql.org/docs/8.4/static/queries-with.html

Składnia:

[ WITH insert_values with_query [, ...] ]

With PostgreSQL 8.4 this can be achieved using a writeable CTE:

WITH new_values (id, field1, field2) as (
  values
     (1, 'A', 'X'),
     (2, 'B', 'Y'),
     (3, 'C', 'Z')

),
upsert as
(
    update mytable m
    set field1 = nv.field1, field2 = nv.field2
    FROM new_values nv WHERE m.id = nv.id RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2 FROM new_values
WHERE NOT EXISTS (SELECT 1 FROM upsert up WHERE up.id = new_values.id)

#top GROUP with CONCAT¶

Zobacz także REPLACE INTO dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja PostgreSQL 9.0: the aggregate function string_agg()
https://www.postgresql.org/docs/9.0/static/functions-aggregate.html

Dokumentacja PostgreSQL 8.4: the aggregate function array_agg()
https://www.postgresql.org/docs/8.4/static/functions-aggregate.html

Składnia: PostgreSQL 9.0:

Function	Argument Type(s)	Return Type	Description
array_agg(expression)	any	array of the argument type	input values, including nulls, concatenated into an array
string_agg(expression, delimiter)	text, text	text	input values concatenated into a string, separated by delimiter

Składnia: PostgreSQL 8.4:

Function	Argument Type	Return Type	Description
array_agg(expression)	any	array of the argument type	input values concatenated into an array

Zmodyfikowany ostatnio: 2017/11/02 19:44:22 (6 lat temu), textsize: 79,1 kB, htmlsize: 141 kB

Zapraszam do komentowania, zgłaszania sugestii, propozycji, własnych przykładów, ...

Dodaj komentarzKomentarze użytkowników