Tag Info

Hot answers tagged

15

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


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

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


9

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

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


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

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


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

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

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. Another error There is another error: update netcen.test set test=myobj.test, ...


6

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


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

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


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

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


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

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

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


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


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


4

You will gain a lot more with regards to performance if you move the table to a different harddisk. As long as the "busy" table and the rest are located on the same disk, moving that table into a different "file" (by moving it into a different database) won't change anything with regards to (I/O) performance. Distributing the I/O load to a different ...


4

What about: with tweets(type, id) as ( select 'add' as type, tweets.id from tweets ) select row_to_json(row(array_agg(tweets))) from tweets; This is actually one of the really cool things about PostgreSQL, arrays, and complex types. One can aggregate them and then convert. You should get a structure which is effectively: tweets[] (i.e. an array ...


4

I can see how one might think listen_addresses = 'localhost' is somehow related to the authentication method local. But it is not. I quote the manual on listen_addresses: listen_addresses (string) Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. And the chapter The pg_hba.conf File in the ...


4

If you ever wonder about the correct syntax for a row type, ask Postgres. It should know: SELECT b FROM book b LIMIT 1; -- or: WHERE id = 179; Which will return a text representation of your row in valid format: (179,"the art of war",fiction,"{190,220}") Values of columns are represented as an unquoted, comma-separated list, enclosed in paretheses. ...


4

Assuming the locale of the database is en_GB.UTF-8 both in Ubuntu (master) and FreeBSD (slave), I believe the differences in sort semantics alone may account for the fact that the index is unusable on the slave. Here's an example of how they sort differently: On Ubuntu 12.04: $ export LANG=en_GB.UTF-8 $ cat >file "0102" 0102 $ sort file 0102 "0102" ...


4

First don't make plpython3u into a trusted language. This is bad. You are giving access to all kinds of things on the database, the filesystem, and the like in ways that could be horribly abused. Please reconsider. Now from your comments you want to keep the admin permission separate from the normal user permission. The way to do this is to make sure ...


4

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



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