Tag Info

Hot answers tagged

26

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


18

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


12

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

Solution for PostgreSQL 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 ...


11

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


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


10

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


9

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


9

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


8

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


8

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


7

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


7

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


7

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 Also read about the basics of data alignment in this related answer on SO. We have three columns for the primary key: PRIMARY KEY ...


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

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


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


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


5

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


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

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.


5

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


5

I think yes. Haven't tried with actual INSERTs or UPDATEs yet, but this function works: CREATE TABLE a (id integer); CREATE OR REPLACE FUNCTION arraytest2(rec a[]) RETURNS SETOF integer AS $body$ SELECT b.* FROM unnest($1) b; $body$ LANGUAGE sql; This way you can write your INSERT statement as INSERT INTO tbl_weightment SELECT (r).* FROM unnest(rec) ...


5

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


5

Probably this will help: The Linux operating system normally terminates, or kills, a task by sending it a SIGHUP command. This stands for "Signal Hangup" and terminates a process on the system. If a programmer has not created a program restart command, or if you are having difficulties with an unresponsive program and wish to restart it, sending ...


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


5

You are looking for the crosstab() function provided by the additional module tablefunc. Then your function could look like this: SELECT * FROM crosstab( 'SELECT class, payment_method, sum(debit - credit) AS saldo FROM tbl GROUP BY 1,2 ORDER BY 1,2' ,$$VALUES ('payment_method1'::text), ('sumofpayment_method1') , ...


4

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


4

As an alternative to a_horse_with_no_name's solution, the simplest option is to use < and the any row-or-array comparision with the array: SELECT city FROM cities WHERE 80 < any (temps); See SQLFiddle. It is not necessary to unnest the array, as any and all work on arrays as well as on rowsets. Unlike using the array operators, this operation does ...



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