Tag Info

New answers tagged

0

Try the following command disregarding from which folder you start psql. It will work fine on windows. The SQL file may be located any where on your system. Put the address of the SQL file in single quotes and use double backslashes to specify the path, like this: \i 'C:\\Users\\Work...'


0

RETURN isn't a valid command in a rule definition; you can only use SELECT, INSERT, UPDATE, DELETE, and NOTIFY. You can create an ALSO rule to return the value of the new row, something like this: > CREATE RULE return_data AS ON INSERT TO FOO DO ALSO SELECT NEW.*; CREATE RULE > INSERT INTO foo VALUES (1, 'test'); a | b ---+------ 1 | test (1 row) ...


2

It sounds like you might be coming from an Oracle background, where the Oracle database generally manages the storage system directly. That's not how PostgreSQL works. It uses the file systems, RAID support, etc provided by the operating system. To get redundant storage with PostgreSQL you need to set up redundant stoage - in the form of RAID, or whatever ...


0

COPY doesn't understand the shell-script shorthand ~ for "my home directory". You must specify the path explicitly, e.g. COPY nh FROM '/home/MYUSERNAME/Dropbox/0ptimus-Jaspin/VoterMapping--NH--03-17-2014-HEADERS.tab'; at which point you'll probably get a "permission denied" error, because COPY runs as the postgresql server user, which can't normally read ...


3

I'd use Londiste for this. Set up the data collector as a publisher and the analytics DB as a subscriber. The main issue is that Londiste imposes a significant load due to write-amplification - each write must be written once to the table and once to a trigger-maintained replication queue. So using Londiste for replication may add too much load to an ...


0

The information_schema is your friend for db stats. In addition, you can write a relatively simple query up against pg_stat_activity if your looking for detailed data on your queries. Cheers.


0

Thanks for the reply @Imarin. I figured it out. I was configuring the wrong pg_hba.conf file. I needed to configure the file in test-qa-db because that was where the database resided.


0

Have you tried the following line, host 10.1.1.91 all all 10.1.1.91/32 md5 in your pg_hba.conf and reload your configuration ? Perhaps you should see, https://wiki.postgresql.org/wiki/Client_Authentication Regards


1

In Postgres (and probably any RDBMS to a similar extent, MySQL to a lesser extent), fewer queries are almost always much faster. The overhead of parsing and planning multiple queries is already more than any possible gain in most cases. Not to speak of additional work to be done in the client, combining the results, which is typically much slower at that. ...


1

The best solution depends on where (and in what form) the values come from. unnest() Use the set returning function unnest() with an array in the FROM clause. That's shortest for long lists, but a Postgres specific solution. SELECT avg(n) FROM unnest('{2,3,4}'::int[]) n; Which is short syntax for: SELECT avg(n) FROM unnest('{2,3,4}'::int[]) AS n(n); ...


0

What would address your question is the subject JOIN DECOMPOSITION. According to Page 209 of the Book You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application. For example, instead of this single query: SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN ...


1

For your case, SELECT AVG(foo) FROM (SELECT 2 as foo UNION SELECT 3 UNION SELECT 4) aliasname like what Mihai said. Here is the generic syntax: SELECT AVG (* | [DISTINCT] ALL | column_name) FROM (table_name) I would suggest learning the function by using columns that have numeric or integer values to average before you go down your route. Also ...


1

No, deferred trigger execution will not help. It only defers triggers until the end of the transaction. PostgreSQL doesn't have a built-in task/job scheduler, either, so you'll need to do this in your app. In the trigger, insert a row into a table that keeps track of which entries should be deleted and when. Scan that table every few minutes and, in a ...


2

You must also set separate unix_socket_directories.


1

You can't use variables for columns. So you would need to do something like this: CREATE RULE update_main_table AS ON INSERT TO entity_history DO UPDATE entity SET eValue1 = case when new.type = 'Value1' then NEW.val else eValue1 end, eValue2 = case when new.type = ...


3

Ubuntu and Debian use pg_wrapper to manage PostgreSQL. It's in the postgresql-common package. (As a result, this answer doesn't apply to RHEL/CentOS/Fedora). If there's no data of value, you can just use pg_dropcluster (part of pg_wrapper) to drop (delete) your old datadir. This will destroy all data in it. Then use pg_createcluster to make a new one in ...


2

It's the same as any other string. Either use non-standard E'' strings to tell PostgreSQL you want escape processing: RAISE WARNING E'Line 1\nLine 2'; or use SQL-standard strings with literal newlines: RAISE WARNING 'Line 1 Line 2';


0

One good way to do this is to have your PGDATA (/var/lib/postgresql/9.3/main) symlink-ed to /data. Doing it this way no need to change postgresql.conf.


2

From pg_restore's documentation you can see: -t table --table=table Restore definition and/or data of named table only. Multiple tables may be specified with multiple -t switches. This can be combined with the -n option to specify a schema. Highlighting "Multiple tables may be specified with multiple -t switches", which means that you can use -t ...


0

This depends most likely on how your updates will be accomplished, because " A trigger is fired for any affected row once. A rule manipulates the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must ...


3

Syntax errors Immediate causes for the error: WHERE id = select currval('mytable_id_seq'); And a missing semicolon at the end of the DO statement: ... $do$; Better query At a closer look, this does not seem to be another case of UPSERT. It would seem one of the inserted columns needs to depend on multiple input values in combination with the freshly ...


1

You can use this method (assuming both tables have same columns in same order): with T as (delete from only company where id=? returning *) insert into school select * from T


1

Try: cat data.csv | psql -d database -c "copy v (a) from stdin with delimiter ',' escape '\' quote '''' CSV header" You don't need to escape the escape character when specifying it. QUOTE defaults to double-quote so you need to pass that.


0

Error on inserting data due to FK constraint violation will leave dead tuple on the target table. That's the nature of Postgres. This dead tuple of course will occupy disk space. Things to prevent this filling up disk space : A well tuned autovacuum can handle this wasted space. Do a manual FK constraint check (on application side perhaps) prior to ...


2

As it turns out, the key word loop needs to be double-quoted inside PL/pgSQL or the parser is confused: SELECT public.foo_fun(NEW."loop") INTO fRV; The situation in Postgres 9.3 is unchanged: SQL fiddle with quotes (works). SQL fiddle without quotes (doesn't work). It's generally a good idea to avoid typical key words like loop as identifiers. There are ...


2

The word LOOP is a reserved word in PL/pgSQL. If you want to use it as a column name, you need to quote it.


2

CREATE OR REPLACE FUNCTION trade_volume (_market_id int , _start_time timestamp , _end_time timestamp) RETURNS numeric(16,8) AS $func$ BEGIN RETURN QUERY SELECT sum -- dubious identifier FROM market_trades_sum_cache WHERE market_id = _market_id ...


1

Use the window function lead() in a subquery: SELECT id, device_id, uptime FROM ( SELECT *, lead(uptime) OVER (PARTITION BY device_id ORDER BY id) AS next_uptime FROM tbl ) sub WHERE next_uptime < uptime BTW, "where a field stops growing" would translate to <=, but "where the next row's timestamp is smaller" requires <.


2

Question: do I really have to copy the WAL to have complete backup from the time of starting pg_basebackup command? Yes, you do. The DB needs current WAL to start up from the base backup. You can use pg_basebackup -X stream on 9.3 and above to copy all the required WAL along with the initial basebackup.


1

The answer is same as the answer for this question: Inner join using an array column The main point is installing the btree_gin extension (this may require additional package, like postgresql-contrib) Then, the extension can be enabled easily with: (requires postgresql >= 9.1) CREATE EXTENSION btree_gin; And index can be created with: CREATE INDEX ...


1

pg_temp is an alias for the current session's temporary schema. If you do a SET search_path TO pg_temp before running your tests it should all just work (as long as nothing is referencing a schema explicitly). If you don't want to change your script at all, then set the search_path on the user that the tests log in as: > ALTER ROLE testuser SET ...


0

Answering my own question... It turns out this is harmless INFO-level logging: http://stackoverflow.com/a/3531274/14731


3

The expression regexp_replace(the_column, ' +.*$', '') will remove everything starting with the first space character up until the end of the string. So you just need to group by that expression: with test_data (sport) as ( values ('Baseball'), ('Football'), ('Baseball [a]'), ('Football [a] [b]'), ('Basketball') ) select ...


-1

USE tempdb IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp GO SELECT Sport INTO #temp FROM ( SELECT 'Baseball' Sport UNION ALL SELECT 'Football' UNION ALL SELECT 'Baseball [a]' UNION ALL SELECT 'Football [a] [b]' UNION ALL ...


0

To gain more info on database and table list, You can do : \l+ to list databases List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description ...


0

cluster command is a table rewrite process. So, yes it will cluster anything on the table, including extended storage.


1

Here's what You can do to move your data to the new tablespace : Create the new tablespace pointing to the new location. postgres=# create TABLESPACE movespace LOCATION '/home/postgres_tbsp'; CREATE TABLESPACE Alter table or database to move to the new tablespace. postgres=# ALTER DATABASE slonmaster SET tablespace movespace; ALTER DATABASE This ...


2

COALESCE in an outer SELECT does the job for a single value. Wouldn't work for selecting multiple columns or rows, though. This technique with UNION ALL works universally: ( SELECT numofitems -- or more columns FROM inventory ORDER BY inventorydate DESC NULLS LAST -- add NULLS LAST if you col isn't NOT NULL! LIMIT 1 -- or more rows ) UNION ...


1

You can get the name of the current temporary schema (after creating the first temp table) like laid out in the link you added: SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema(); But your current plan still wouldn't make a lot of sense. To create tables in the current temporary schema, just create temporary tables. That's all. By ...


0

Do your tests involve transactions? DDL is transactional in PostgreSQL, so if you create your schema and tables, then run your tests, all within a single transaction that is then rolled back, the schema is never actually committed and visible to other sessions. You'd still need to use a probably-unique name for your schema (maybe include hostname and PID), ...


2

Yes it does support different transaction isolation levels per-connection. You can set the transaction isolation level (as well as the read-only and deferrable status for transactions) for a connection with SET SESSION CHARACTERISTICS: localhost:5432 postgres postgres # SHOW transaction_isolation; transaction_isolation ----------------------- read ...


2

Transaction isolation level is set up per transaction basis. Even one connection can have different isolation level on each transaction. Of course different connection can have different isolation level. Here's some testing : postgres=# begin; BEGIN postgres=# show transaction_isolation ; transaction_isolation ----------------------- read committed (1 ...


0

I just got an idea. Postgresql guarantees that a session can't see another's temporary tables. I'm guessing this means that when you create a temporary table, it creates a temporary schema. So perhaps I could do the following: Create a (dummy) temporary table and look up its schema. Use this schema for the test (create the tables, run the test). When the ...


0

There is an extension that tracks queries: http://www.postgresql.org/docs/9.2/static/pgstatstatements.html You have to enable the extension and then change your postgresql.conf to load it in the server. But, after that there would be next to zero overhead to get how many rows were affected. where you could do something like: select rows from ...


1

The real answer is that you just can not practically. This has been a requested feature for decades and the developers refuse to implement it. The popular answer suggesting querying the schema tables will not be able to run efficiently because the Postgres optimizer considers dynamic functions a black box (see the test case below). That means that indexes ...


1

That depends on what else happens in the transaction. With the for share there, nobody can update the selected get_offer_data row until the transaction commits or rolls back. Without the context, it's really impossible to say what the original intended purpose of adding the lock there was.


1

Interesting question! Short answer: no. Long answer: there does not appear to be any existing way to get a list of savepoints defined. Even worse, it doesn't seem possible to create a PostgreSQL extension which would let you do this: looking at src/backend/access/transam/xact.c, you can see that functions like RollbackToSavepoint (which is where that "no ...


2

I think you want coalesce outside the subquery expression. SELECT 1000 < coalesce((SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1), 0);


3

David's answer is correct except for the code, which is nonsensical in PostgreSQL. You're looking for > 2 decimal places. The easiest way to find that is to cast to NUMERIC (arbitrary-precision decimal floating point) and subtract the rounded value from the original. SELECT * FROM mytable WHERE (CAST(floatvalue AS numeric) - round(CAST(floatvalue AS ...


2

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



Top 50 recent answers are included