New answers tagged

1

Yes, as (like you described) rotation replaces the file with the same name, you can find whichever name you like, without any daily changing bits.


1

If you migrate from RDBMS to NoSQL then you will also have problems. They are not interchangeable for all types of usage. When you start a project then just before you start the creation of your data objects you must choose the one that suits best to your needs. Changing later will nearly always cause problems as soon as you use more then the 'standard' ...


1

This might be too broad of a question but as with any system, the less steps and dependencies you have, the easier it is to support it as long as it meets current and future (or future enough) business goals. In your use case scenario it doesn't look like you have any use for HDFS and SQOOP. In a lot of environments they might. For example they might ...


0

In the FROM clause below, the subquery acts as a table: SELECT * FROM (SELECT id, name FROM users) That subquery is called an inline view. So in this case you are not directly selecting rows from a table, but from an "inline view". From your first link it seems that the user created an actual view, which you can do from a query. More information over ...


0

In this context, I usually create a specific table to see the last replication status. On your PostgreSql master server, you create a simple table, example: CREATE TABLE check_replication ( now_date timestamp ); Then you create a batch or job, that performs every minute, or more often: INSERT INTO check_replication ( now_date ) VALUES (now()); Then, ...


2

A method that I find useful is to implement the cleanup code in my main programming framework (so Django, I guess, in your case) and expose that function via a URL. Then in my crontab I use wget or curl to invoke the cleanup URL. That way the cron file is only responsible for the scheduling part, while the code that does the cleanup is kept together with ...


0

Read the article on http://help.bluemangolearning.com/m/screensteps-workgroup/l/18259-Configuring-PostgreSQL-to-Accept-Connections-From-Computers-on-Your-Network where it is explained with screen shots.


4

In the ANSI SQL Standard, there is no TEXT type. There are various string types defined, like CHAR and VARCHAR and many more but no mention of a TEXT type. There is a CHARACTER LARGE OBJECT and a BINARY LARGE OBJECT. The various TEXT types found in various DBMS (Postgres, SQL Server, MySQL) are additions and have small differences between them. Their ...


2

You can generate the SQL to add the primary key with: select concat('ALTER TABLE ',table_schema,'.',table_name,' ADD CONSTRAINT ', table_schema,'.pk_',table_name,' PRIMARY KEY (id)') from information_schema.columns where column_name ='id' and table_schema='xxx'; Replace the xxx with the schema for which you want to create the primary ...


0

The ANSI SQL standard (which I assume you refer to) will nothing to say on how Microsoft (or Oracle, etc) implement and remove data types to suit their particular flavour of DBMS, and which ones take precedence. The Microsoft standard is to use varchar in place of text, nvarchar in place of ntext, and varbinary in place of image, and this is because of the ...


0

Assumptions I'm assuming you have two instances each one with a different data directory but with the same schema for the same version of postgres installed. I'm also assuming when the server starts is using your newly created, possibly empty, instance What to do You should try to start a new instance of the server pointing to your backup data directory......


1

If You are querying just one table (like SELECT * FROM table) then right click on it in Object browser, choose View Data from context menu, You will have options to View Top 100 Rows or View Last 100 Rows. Other than that, use LIMIT and OFFSET clauses in Your queries: https://www.postgresql.org/docs/9.3/static/queries-limit.html


1

Using pgAdmin III, I ran the following command to generate 100,000 random records. create table t_random as select s, md5(random()::text) from generate_Series(1, 100000) s; I then did as MarcinS suggested in his answer: "right click on it in Object browser, choose View Data from context menu, You will have options to View Top 100 Rows or View Last 100 ...


0

If you are using Postgresql 9.3 or superior I strongly suggest you to use extension postgres_fdw. With that you can create a new empty database as you suggested in your post (#1) and create a different schema per "organisation" in which you'll have foreign tables corresponding to that "organisation" database. It'll be like having all your databases in one. ...


2

The psql command \? shows the option to turn off expanded formatting: Formatting: ... \x [on|off|auto] toggle expanded output (currently off) Typically it will show table data in one record per line if its "off" or else each field in its own line when switched on, e.g.: postgres=# \x off Expanded display is off. postgres=# \l ...


1

Back in 2013 a similar question was asked (How to run recurring tasks on a Postgresql database without a cron-like tool?). The crontab or pgAgent were the only way to go. For version 9.3 there is already the Background Worker Processes. But even in the version 9.5.2 documentation there is a warning about using it. I think that the cron solution is a good ...


1

First off, your question as well as your column name "key" are misleading. The column key does not contain any JSON keys, only values. Else we could use the function jsonb_object_keys(jsonb) to extract keys, but that's not so. Assuming all your JSON arrays are either empty or hold integer numbers as demonstrated. And the scalar values (non-arrays) are also ...


3

No, those are not "duplicated", those are additional relations/objects created for the tables. reversion_revision_pkey is most probably the unique index supporting the primary key for the table reversion_revision. And the others are most probably indexes as well. You can add this expression to your query to see the actual type of the relation: case ...


2

Your subquery is uncorrelated and returns all rows from the join between native_name_aliases and dpoint, where only a single row would be allowed. I guess this is what you are after: UPDATE dpoint d SET native_name = nna.native_name FROM native_name_aliases nna WHERE d.building_id = 42 AND d.dpoint_id = nna.dpoint_id AND d.native_name IS ...


0

While using EXCEPT like @Martin provided, remember to make it EXCEPTALL, unless you want to pay a little extra for trying to fold duplicates. BTW, a VALUES expression can stand on its own: VALUES (4),(5),(6) EXCEPT ALL SELECT id FROM images; But you get default column names this way. For a long list of values it may be more convenient to provide it as ...


3

You could merge the subqueries using this model: SELECT bool_or(B.tags&1<>0) as "has_children_tag_1", bool_or(B.tags&2<>0) as "has_children_tag_2", bool_or(B.tags&4<>0) as "has_children_tag_3", bool_or(B.tags&8<>0) as "has_children_tag_4" FROM A LEFT JOIN B ON A.id = B.parent_id WHERE [conditions] ...


2

For Postgres 9.4+: UPDATE trajs t SET traj_id = upd.traj_id FROM ( SELECT id, 1 + count(*) FILTER (WHERE t >= t0 + interval '6 min') OVER (PARTITION BY obj_id ORDER BY t) AS traj_id FROM ( SELECT id, obj_id, t, lag(t) OVER (PARTITION BY obj_id ORDER BY t) AS t0 FROM trajs ) sub ) upd WHERE t.id ...


0

The documentation you cited says it all, but I wouldn't blame you if you want to try to verify the claims of the vendor regarding snapshots taken at the same time. Perhaps a way of uncovering something could be to stress test the WAL system more specifically. e.g. In addition to your pgbench-based tests, try adding random calls to pg_switch_xlog() to ...


4

Two major improvements: SELECT * FROM certificates c WHERE c.expires_on <= current_date + 30 -- sargable! AND NOT EXISTS ( SELECT 1 FROM certificates WHERE common_name = c.common_name AND expires_on > c.expires_on AND state = 'issued' ); Make the first predicate sargable, so that an index can be used. You second ...


0

I just remembered you can quote table names, and I guess this works to escape the question marks. ALTER MATERIALIZED VIEW temp_name_relationships RENAME COLUMN "?column?" TO score;


1

Sounds like you're trying to insert rows to a table that already exists, in which case you'll need something like... INSERT INTO new_records SELECT * FROM new_table t JOIN new_record_ids r ON(r.id = t.id) --this assumes the result of the select has the same column layout as new_records... --which seems unlikely. replace (SELECT *...) with (SELECT column1, ...


2

Your parameter _source in the added MWE is not referenced anywhere. The identifier source in the function body has no leading underscore and is interpreted as constant table name independently. More importantly, it would not work like this anyway. SQL only allows to parameterize values in DML statements. Details in this related answer: Error when setting ...


3

I'd like to know what I'm not understanding correctly about excluding constraints. What is happening here is that primary key (col1) creates a primary key constraint but doesn't create a separate not null constraint. It adds a not null "modifier" to the column. This works as a constraint, ie. no nulls are allowed in col1 but it is not a named constraint. ...


0

The planner can't get a good estimate of how selective your join is going to be, and so can't make good decisions about it. Can you force it to do the join you want with a CTE, and then join that to the rest of the tables? WITH t as (SELECT * from protein_seq p, tmp_psm_seqs t WHERE p.sequence LIKE '%'|| t.sequence || '%' ) select ... from t join .... ...


2

Use t1."MaxID" literally the same as in with or drop quotes in with.


-4

You should store your value for FIELD1 in a TABLE, instead of in a bunch of variables. Make a three level index, a COVERED index specifically. your IN clause is preposterous. I mean.. Learn how to Normalize. THIS: WHERE Field1 IN ('aaaaaaa','bbbbbbb','ccccccc',...,'nnnnnnn' ) is actually SLOWER than this: SELECT Field1,Date FROM schema.table1 ...


3

The OR REPLACE clause in CREATE FUNCTION is not meant for seamless parallel execution, it's meant to avoid dropping the function when we just want to update the body. From the doc: If you drop and then recreate a function, the new function is not the same entity as the old; you will have to drop existing rules, views, triggers, etc. that refer to the ...


0

You could perform the pg_dump with the parameters --no-owner and --no-acl, so no permissions or links to any users are presents in the dump.


3

Explanation I'm going to step through the multiple layers of misunderstandings one by one - arriving at a simple, secure solution. 0. The reason why overlay is escaped is not because it's a function name, but because it's a reserved word. Also, overlay() is not a "PL/pgSQL function" (nor PL/pgSQL keyword), it's a standard SQL function built into the ...


2

The first query is logical nonsense for your case: SELECT count(DISTINCT a.id) from the_view a; It makes no sense to use DISTINCT with the id defined unique. The second query is typically faster with a sequential scan: SELECT count(*) from the_view a; Indexes don't contain visibility information. Due to the MVCC model of Postgres it needs to check ...


1

OK simple solution. Use: conn = psycopg2.connect("db=dbname etc etc") lo = conn.lobject(12345) lo.export('/tmp/test.jpg')


0

If possible, rather than creating a new Linode(thanks for enlightening me), if you are able to create pgbouncer on the source (webserver or whatever) of the short lived connections, then you'll also save on the network time to make the connection.


0

I had the same problem recently and I couldn't find any sufficiently good solution. So I created a small tool to dump a sample of data from the PostgreSQL database: https://github.com/dankeder/pg_dump_sample.


1

The only way I could think was to wrap the call to the format function in a translate as follows: RETURN translate(format('%I_new',old_name),'"',''); This post processes the output of the format and strips the " characters out. I don't know if anybody has a more elegant solution. I thought of btrim, rtrim etc...but it would need a call to each.


0

I suggest that you use a tool such as Liquibase or Flyway which is designed to manage such schema changes in a database and keep the configurations consistent and known. http://www.liquibase.org/ https://flywaydb.org/ There are other tools available and they usually have plugins to maven, Puppet & Chef etc...it'll come down to a google search and ...


0

The top command gives the CPU usage per core. Since you have 12 I would not create another Linode for that. Check the line with Cpu(s). There you will find the %id which is a better indication weather or not your server is using it's full CPU capacity.


1

I would recommend using the same syntax for all of your WHERE clauses (your index would build for negative values of bar, but you never SELECT those) CREATE INDEX has an implicit order of ASC, so it unlikely that not specifying the indexing order alone is the source of your problem (as you stated the DESC sort is faster). Naively, I'd also recommend that ...


3

I highly recommend re-writing your query using proper join syntax: select pep.sequence, string_agg(DISTINCT g.symbol, ',') FROM tmp_psm_seqs2 pep JOIN protein_seq p ON p.sequence LIKE '%' || pep.sequence || '%' JOIN transcript_translation tt ON tt.protein_seq_id = p.protein_seq_id JOIN transcript t ON tt.transcript_id = t.transcript_id JOIN gene g ON t....


2

I'm not sure why you need to join all three tables every time. For your specific example, what about the following query: WITH rel AS ( SELECT prm.power_relation_id FROM power_relation_members prm JOIN power_lines pl ON prm.member_id = pl.id WHERE pl.geom = :BIND_VAR_HERE -- in this case, 'abc' GROUP BY prm.power_relation_id ) SELECT pl.id, pl....


2

The plan I see when I reconstruct your example is that it does a nested loop around a sequential scan on tmp_psm_seqs and a bitmap scan, using the index you created, on protein_seq. That seems like a pretty good plan to me.


2

There are lots of ways to run a scheduled job. One of them is using pgAgent. For windows you can store your password in %APPDATA%\postgresql\pgpass.conf file. Here you can find details. You can also add trust to specific ip and user from server side by changing pg_hba.conf file


4

You can do that with a column check: ALTER TABLE table ADD CHECK ((column->'id') is not null and (column->'name') is not null);


1

Another option is something that I've just recently come across (granted, I'm yet to use it, but plan to soon): rdbms-subsetter. It is a little bit simpler and lighter than Jailer, with a few nice features/perks: CLI so easy to wire up to existing tooling Open source Will follow foreign keys to fetch a coherent subset of data If you don't have well-...


0

It's not the client application that makes the results differ, but the database user you're connected with. In 9.3, schemata returns only the schemas that the session's user owns, either directly or indirectly through a granted role. The doc says: The view schemata contains all schemas in the current database that are owned by a currently enabled ...



Top 50 recent answers are included