Postgresql – rename database in psql

postgresql

Could anybody help me to rename database in postgresql from the Linux shell

ALTER DATABASE name RENAME TO newname

The above statement doesn't execute

This may be a stupidly obvious question. Are you running psql as the postgres user?

e.g.

$ sudo -u postgres psql
# alter database FOO rename to BAR;
# \q

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

I figured it out from the PostgreSQL docs, the exact syntax is:

ALTER TABLE the_table ADD CONSTRAINT constraint_name UNIQUE (thecolumn);

Thanks Fred.