Tag Info

Hot answers tagged

44

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


44

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


29

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


25

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


17

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


14

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


14

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


14

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


13

Generally, col IS NULL is a possible candidate for a (default) b-tree index search. I quote the manual here: 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) ...


11

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


10

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


10

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

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


8

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


8

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


7

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


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

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


7

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


7

There are a couple of misunderstandings here: The null bitmap is not part of the heap tuple header. Per documentation: There is a fixed-size header (occupying 23 bytes on most machines), followed by an optional null bitmap ... Your 32 nullable columns are unsuspicious for two reasons: The null bitmap is added per row, and only if there is at least ...


6

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.


6

Assuming your table is named readings: Find cities where at least one temperature is bigger than 80: select * from readings r where exists (select 1 from unnest(r.temps) i where i > 80) Find cities where all temperatures are bigger than 68: select * from readings r where 68 < all (select i from ...


6

Deferred indexing would be nice, but isn't currently supported. Adding indexes has a cost - write performance. They're a trade-off. COPY won't help much if index maintenance is the main issue. The simplest solution is to drop the indexes, and re-create them when you're done importing. Since you can live with losing all your data if the DB crashes, you ...


6

Answer The error occurs here: CASE tmp_code WHEN COALESCE(tmp_code,0)=0 THEN Would have to be CASE WHEN COALESCE(tmp_code,0)=0 THEN You are mixing the two different syntax variants of PL/pgSQL CASE ("simple case" vs. "searched case") in an incompatible way. There is another error: update netcen.test set test=myobj.test, testname=myobj.testname ...


6

This sort of thing gets complicated. I am working on some related projects right now. The basic tweak is that PostgreSQL uses a format which uses double quotes internally in tuple representation to represent literal values, so: SELECT save_book('(179,the art of war,fiction,"{190,220}")'::book); should work. In essence a neat trick is creating a csv and ...


6

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


6

I am afraid your chances are somewhere between infinitesimally small and zero. There are a lot of files in that directory and it's sub-directories. Any which were actually open by one of the postgresql backend processes at the time you deleted the directory, and have not been closed since, are still present in the filesystem, but any that were not are long ...


5

Primary and unique keys in all(?) RDBMSes use indexes in order to quickly be able to determine whether a newly inserted value is indeed unique. The side effect of this is that queries via primary and unique keys are usually "fast". Now if you haven't defined primary or unique keys on your tables, You don't have a relational table but you have junk (OK, ...


5

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


5

While klin is technically right in his answer about how to fix your current function, let me question your whole approach. What you try to achieve is called 'UPSERT' and with PostgreSQL 9.1 (which offers writable CTEs) you have a very simple way to achieve this. I omitted the function definition for the sake of clarity, but you can easily wrap it in a ...



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