Hot answers tagged

90

To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA select schema_name from information_schema.schemata More details in the manual: http://www.postgresql.org/docs/current/static/information-schema.html alternatively: select nspname from pg_catalog.pg_namespace; More details about pg_catalog in the manual: http://www.postgresql.org/docs/...


89

When using the psql command line, you may list all schema with command \dn.


64

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 ...


48

Solution for Postgres 9.1 CREATE INDEX idx_time_limits_inversed 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. I wrote more in this closely related answer on SO....


31

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: ...


22

Data alignment and storage size Actually, the overhead per tuple is 24 byte for the tuple header plus 4 byte for the item pointer. More details in the calculation in this related answer: Use GIN to index bit strings Basics of data alignment and padding in this related answer on SO: Calculating and saving space in PostgreSQL We have three columns for ...


21

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 ...


19

Plain INSERT INSERT INTO bar (description, foo_id) SELECT val.description, f.id FROM ( VALUES ('testing', 'blue') ,('another row', 'red' ) ,('new row1', 'purple') -- purple does not exist in foo, yet ,('new row2', 'purple') ) val (description, type) LEFT JOIN foo f USING (type); The use of a LEFT [OUTER] JOIN ...


18

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 ...


17

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))


15

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 ...


15

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, ...


14

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.


14

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 ...


13

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 ...


12

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 probably want to join that to pg_namespace to limit this to a specific schema).


12

(Note: Not much of this is relevant to readers using PostgreSQL 9.2 or above, which has a greatly simplified install). I have used net user postgres postgres to reset the password for my database but instead of a success message I am getting "System error 5 has occurred. Access is denied." You've reset (or tried to reset) the service account password....


11

In addition to Craig's advice I would like to advise you to examine the storage parameters of the affected tables. I am currently in a similar situation to yours. The largest table in my system contains ~200 million records and the performance was really bad. Tune the storage parameters of your tables and indexes Besides adding several indexes to the ...


9

The problem Here is a very similar case discussed on pgsql.general. It's about the limitation in a b-tree index, but it's all the same because a GIN index uses a b-tree index for keys internally and therefore runs into the same limitation for key size (instead of item size in a plain b-tree index). I quote the manual about GIN index implementation: ...


9

\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 ...


9

Will/can Solr/Lucene searches be faster than PostgreSQL even if no full-text search is involved? Yes. As per your quoted example, it can be many times faster than a relational database for certain use cases. Not surprising really. Solr is a search engine. PostgreSQL is a relational database engine. Solr is built from the ground up to do one thing well, ...


8

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.


8

The IPv6 addresses starting with fe80: are link-local addresses. They cannot be routed across different subnets or the internet. They are for communication between machines connected to the same LAN only. The link-local range is defined as fe80::/10. If you trust everybody on your local LAN then you could do host all all fe80::/10 trust If you don't ...


8

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 ...


8

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 ...


8

PostgreSQL certainly can use an index for IS NOT NULL. I don't see any query planner assumptions about that condition, either. If the null fraction for the column (pg_statistic.stanullfrac) is low enough to suggest that the index is usefully selective for the query, PostgreSQL will use an index. I can't figure out what you're trying to say with: If ...


7

Like willglynn said, it's probably your pg_hba.conf file. If you have the following line: local all all peer then change it to: local all all md5 That should then let you login with your new password (assuming that you correctly supply it) :)


7

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', '...


7

Yes you will have to give the slave a new base backup (for streaming replication only steps 1 to 4) of the master. Your problem has probably occured because the value of wal_keep_segments is to low. The value needs to be high enough that when the slave is down for some time the master won't start recycling segments the slave hasn't processed yet.


7

My suggestion about archiving: Create archive_tablespace (if you want you can separate hardware on archive) Create tables. For example we want to archive table posts. create table posts_all ( LIKE public.posts) ; create table posts_archive () inherits ( public.posts_all) ; alter table public.posts inherits ( public.posts_all ) ; After that ...



Only top voted, non community-wiki answers of a minimum length are eligible