「PostgreSQLでよくやること」の版間の差分

$ pg_dump <DATABASE_NAME> > <FILE_NAME>

$ pg_dump -Fc <DATABASE_NAME> > <FILE_NAME>

$ pg_dump -n <SCHEMA> <DATABASE_NAME> > <FILE_NAME>

$ psql <DATABASE_NAME> < <FILE_NAME>

$ pg_restore -d <DATABASE_NAME> <FILE_NAME>

# \du

# CREATE ROLE <ROLE_NAME> WITH LOGIN PASSWORD '<PASSWORD>';

# ALTER ROLE <OLD_ROLE_NAME> TO <NEW_ROLE_NAME>

# ALTER ROLE <ROLE_NAME> WITH PASSWORD '<PASSWORD>';

# ALTER ROLE <ROLE_NAME> WITH PASSWORD NULL;

# DROP ROLE <ROLE_NAME>;

# \l

# \c <DATABASE_NAME>

$ createdb -U <ROLE_NAME> <DATABASE_NAME>

# CREATE DATABASE <DATABASE_NAME>;

# CREATE DATABASE <DATABASE_NAME> OWNER <ROLE_NAME>;

# ALTER DATABASE <OLD_NAME> RENAME TO <NEW_NAME>;

$ dropdb -U <ROLE_NAME> <DATABASE_NAME>

# DROP DATABASE <DATABASE_NAME>;

# \d

# \dt

# CREATE TABLE IF NOT EXISTS <TABLE_NAME>(
    id BIGSERIAL,
    <COLUMN_NAME> <TYPE>,
    ...,
    PRIMARY KEY (id)
);
COMMENT ON COLUMN <TABLE_NAME>.id IS 'ID';
COMMENT ON COLUMN <TABLE_NAME>.<COLUMN_NAME>IS '<COMMENT>';
...

# DROP TABLE IF EXISTS <TABLE_NAME>;

# ALTER TABLE <TABLE_NAME> ADD COLUMN <COLUMN_NAME> <COLUMN_TYPE>;

# ALTER TABLE <TABLE_NAME> ADD COLUMN <COLUMN_NAME> <COLUMN_TYPE> AFTER <EXISTING_COLUMN_NAME>;

# ALTER TABLE <TABLE_NAME> ALTER COLUMN <COLUMN_NAME> TYPE <NEW_COLUMN_TYPE>;

# ALTER TABLE <TABLE_NAME> DROP COLUMN <COLUMN_NAME>;

# \dn

# SELECT current_schema;

# CREATE SCHEMA <SCHEMA_NAME>;

$ pg_dump　-n <SOURCE_SCHEMA_NAME> <DATABASE_NAME> > <FILE_NAME>

# ALTER SCHEMA <NEW_SCHEMA_NAME> RENAME TO <SOURCE_SCHEMA_NAME>;

$ psql <DATABASE_NAME> < <FILE_NAME>

# ALTER SCHEMA <OLD_NAME> RENAME TO <NEW_NAME>;

# SET search_path TO <SCHEMA_NAME>;

# ALTER USER <ROLE_NAME> SET search_path TO <SCHEMA_NAME>;

# DROP SCHEMA <SCHEMA_NAME>;

# DROP SCHEMA <SCHEMA_NAME> CASCADE;

SELECT
      * 
FROM
    <TABLE>
WHERE
    (<COLUMN_1>, <COLUMN_2>) IN ( 
        SELECT DISTINCT
              <COLUMN_1>
            , <COLUMN_2>
        FROM
            <TABLE>
        GROUP BY
            <COLUMN_1>
            , <COLUMN_2>
        HAVING
            count(<COLUMN_1>) > 1 
            AND count(<COLUMN_2>) > 1
    ) 
ORDER BY
    <COLUMN_1>
    , <COLUMN_2>
;