278
To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA
select schema_name
from information_schema.schemata;
More details in the manual
alternatively:
select nspname
from pg_catalog.pg_namespace;
More details about pg_catalog in the manual
answered Apr 15 '13 at 19:48
a_horse_with_no_name
51.5k10 gold badges100 silver badges133 bronze badges
267
When using the psql command line, you may list all schema with command \dn.
180
First, you have to be able to connect to the database in order to run queries. This can be achieved by
REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;
GRANT CONNECT
ON DATABASE database_name
TO user_name;
The REVOKE is necessary because
The key word PUBLIC indicates that the privileges are to be granted to
all roles, including those that ...
177
For Postgres 9.1 or later:
CREATE INDEX idx_time_limits_ts_inverse
ON time_limits (id_phi, start_date_time, end_date_time DESC);
In most cases the sort order of an index is hardly relevant. Postgres can scan backwards practically as fast. But for range queries on multiple columns it can make a huge difference. Closely related:
PostgreSQL index not used ...
70
Your syntax is almost good, needs some parenthesis around the subqueries and it will work:
INSERT INTO bar (description, foo_id) VALUES
( 'testing', (SELECT id from foo WHERE type='blue') ),
( 'another row', (SELECT id from foo WHERE type='red' ) );
Tested at SQL-Fiddle
Another way, with shorter syntax if you have a lot of values to insert:
...
60
Whether a password is required or not has nothing to do with pg_shadow and whether a password is actually defined for the user. Yes, I know, that's weird.
pg_hba.conf controls the authentication method. If you want to request a password, use md5 authentication. If you want to allow login with no password to anyone, use trust. If you want to require the same ...
55
For the general settings use:
select *
from pg_settings
where name like '%autovacuum%'
for table specific settings, check out the column reloptions in pg_class:
select relname, reloptions
from pg_class
You will probably want to join that to pg_namespace to limit this to a specific schema. Where joining needs to happen on the hidden col pg_namespace.oid (...
answered Mar 2 '13 at 20:48
a_horse_with_no_name
51.5k10 gold badges100 silver badges133 bronze badges
54
assuming you want to give them all privileges - do this:
grant all privileges on database dbname to dbuser;
where dbname is the name of your database and dbuser is the name of the user.
54
Data alignment and storage size
Actually, the overhead per index tuple is 8 byte for the tuple header plus 4 byte for the item pointer.
Related:
Use GIN to index bit strings
Calculating and saving space in PostgreSQL
We have three columns for the primary key:
PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag")
"Timestamp" timestamp (8 bytes)
...
answered May 27 '13 at 16:46
Erwin Brandstetter
112k13 gold badges245 silver badges375 bronze badges
49
From the documentation:
select table_name from INFORMATION_SCHEMA.views;
If you don't want the system views is your result, try this:
select table_name from INFORMATION_SCHEMA.views WHERE table_schema = ANY (current_schemas(false))
44
Plain INSERT
INSERT INTO bar (description, foo_id)
SELECT val.description, f.id
FROM (
VALUES
(text 'testing', text 'blue') -- explicit type declaration; see below
, ('another row', 'red' )
, ('new row1' , 'purple') -- purple does not exist in foo, yet
, ('new row2' , 'purple')
) val (description, type)
LEFT JOIN foo f ...
answered Jul 16 '13 at 20:40
Erwin Brandstetter
112k13 gold badges245 silver badges375 bronze badges
39
Connect to the psql command --> psql --u {userName} {DBName} then you can type the below command to check how many schemas are present in the DB
DBName=# \dn
Else you can check the syntax by the below steps easily-
After connecting the the DB, press
DBName=# help
You will get the below options:
You are using psql, the command-line interface to ...
37
(Note: Not much of this is relevant to readers using PostgreSQL 9.2 or above from the EDB installers, which now have a greatly simplified default install using the NETWORK SERVICE, though you can still configure other accounts).
I have used net user postgres postgres
to reset the password for my database but instead of a success message I am getting "...
34
In PostgreSQL 9.6 there will be a new version of pg_trgm, 1.2, which will be much better about this. With a little effort, you can also get this new version to work under PostgreSQL 9.4 (you have to apply the patch, and compile the extension module yourself and install it).
What the oldest version does is search for each trigram in the query and take the ...
33
This will list all tables the current user has access to, not only those that are owned by the current user:
select *
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema')
and table_schema not like 'pg_toast%'
(I'm not entirely sure the not like 'pg_toast%' is actually needed though.)
I you really need the owner ...
answered Dec 9 '12 at 18:02
a_horse_with_no_name
51.5k10 gold badges100 silver badges133 bronze badges
28
There are basically three ways of upgrading PostgreSQL from different major versions (e.g. 9.1 to 9.3).
Upgrading with pg_dump
The first one, and recommended if possible, is to do a dump of the old (9.1) version using the binary of the newer (9.3) version and restore it on a new cluster created of the newer version.
This approach is, generally, the slower ...
25
Try this: Re: could not change directory to "/root":
Apparently you did "su postgres" from the root account, so you're
still in root's home directory. It'd be better to do "su - postgres"
to ensure you've acquired all of the postgres account's environment.
Reading "man su" might help you out here.
23
You can query pg_catalog.pg_views for your desired information:
select viewname from pg_catalog.pg_views;
Refined query to get schema name also - just in case you have multiple views with the same name in different schemas - and left out those system views:
select schemaname, viewname from pg_catalog.pg_views
where schemaname NOT IN ('pg_catalog', '...
21
Granting all privileges to all tables within the database is achieved with
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema_name> TO <username>;
21
I am assuming data type text for the relevant columns.
CREATE TABLE prefix (code text, name text, price int);
CREATE TABLE num (number text, time int);
"Simple" Solution
SELECT DISTINCT ON (1)
n.number, p.code
FROM num n
JOIN prefix p ON right(n.number, -1) LIKE (p.code || '%')
ORDER BY n.number, p.code DESC;
Key elements:
DISTINCT ON is a ...
answered May 30 '13 at 14:24
Erwin Brandstetter
112k13 gold badges245 silver badges375 bronze badges
21
You were close. Your last idea is actually the way to go:
log_statement = none
log_min_duration_statement = 10000
Then no statement will be logged, except those running longer than 10 seconds - including the query string itself. Logging may have seemed to stop because 10 seconds is a high threshold. I am using 2 seconds normally, but YMMV.
This related ...
20
The short answer to the question would be:
SELECT *
FROM pg_tables t
WHERE t.tableowner = current_user;
19
Generally, col IS NULL is a possible candidate for a (default) b-tree index search. The manual:
Also, an IS NULL or IS NOT NULL condition on an index column can be
used with a B-tree index.
To get proof, disable sequential scans (in a test session only!):
SET enable_seqscan = OFF;
I quote the manual here:
enable_seqscan (boolean)
Enables or ...
answered Oct 26 '12 at 17:57
Erwin Brandstetter
112k13 gold badges245 silver badges375 bronze badges
19
The following should do it:
Shut down PostgreSQL
Make sure PostgreSQL does not run any longer
Check that PostgreSQL is really stopped
Copy the old data directory to the new drive This is usually defined through a commandline parameter (-D) for your service or through the PGDATA environment variable.
Update your PostgreSQL configuration (service, ...
answered Nov 19 '12 at 15:39
a_horse_with_no_name
51.5k10 gold badges100 silver badges133 bronze badges
19
So you look up other tables in a CHECK constraint.
CHECK constraints are supposed to run IMMUTABLE checks. What passes OK for a row at one time should pass OK at any time. That's how CHECK constraints are defined in the SQL standard. That's also the reason for this restriction (per documentation):
Currently, CHECK expressions cannot contain subqueries ...
19
If you created an SQL-format dump, all you can use is psql.
If you created a custom-format (pg_dump -Fc) or directory-format (pg_dump -Fd) dump, you can and must use pg_restore.
Custom and directory format dumps offer a lot of advantages over plain SQL script dumps, and I use them exclusively. You can selectively restore only some tables/schema, can choose ...
19
You're restoring with pg_restore --format=c ... but the pg_dump was not done with --format=c, it was done with the default, plain format.
From pg_dump manpage:
-F format, --format=format
Selects the format of the output. format can be one of the
following:
p, plain
Output a plain-text SQL script file (the default).
A ...
18
It may be that I was doing something wrong here since I am very new to PostgreSQL. But this only solved the first part of the problem for me - setting the privileges on all existing tables.
In order for permissions to be correctly set for my user on new tables, that are created I has to set default permissions for the user:
ALTER DEFAULT PRIVILEGES IN ...
18
You have corrupted your database by manually deleting files from within the data directory. Never delete files from within the data directory manually.
Safely removing WAL
If you want to remove WAL, either let the server do it at CHECKPOINT time, or use pg_archivecleanup. Note that the server will remove WAL is no longer needs automatically, unless:
...
16
\df *crypt in psql reveals the argument types of the pgcrypto encrypt and decrypt functions (as do the PgCrypto docs):
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-----------------+------------------+--------------------------+--------
...
public | decrypt ...
Only top voted, non community-wiki answers of a minimum length are eligible
