New answers tagged postgresql
0
You may want to check out ETL tools like Talend Studio and Pentaho Kettle.
Also, my sympathies. Horrible messy undocumented data models that change all the time and come in the form of dumps in random irregular forms are no fun at all.
4
Answer is yes. :)
CREATE OR REPLACE FUNCTION create_table_type1(t_name varchar(30))
RETURNS VOID AS
$func$
BEGIN
EXECUTE format('
CREATE TABLE IF NOT EXISTS %I (
id serial PRIMARY KEY,
customerid int,
daterecorded date,
value double precision
)', 't_' || t_name);
END
$func$ LANGUAGE plpgsql;
I am using format() with %I to ...
1
If you return * from the CTE then you are effectively reading the row that you are currently trying to read from t1 -- that's how you mange to read the changes you're making to t1.
In your current code the join to t1 is redundant by the way, but I get the impression that you're using an example more simple than you really want.
1
UNION ALL
I would go with a simple UNION ALL query here:
(
SELECT *
FROM tbl
WHERE timein >= '2013-04-26 0:0'
AND timein < '2013-04-27 0:0'
ORDER BY timein DESC
LIMIT 1
)
UNION ALL
(
SELECT *
FROM tbl
WHERE timein >= '2013-04-27 0:0'
AND timein < '2013-04-30 0:0'
ORDER BY timein
);
This is a single query to Postgres.
...
1
No, there's no detailed optimizer audit/reporting structure or function.
Your best tool is 'gdb'. Unlike MS SQL Server, PostgreSQL is open source; you can just build with debug symbols enabled, attach a debugger, and trace exactly what it's doing.
That doesn't mean it's easy, of course. A canned set of breakpoints and gdb macros to produce a trace of what ...
2
You seem to be misinterpreting a part of my advice to your previous question:
so I can not Truncate and rename the temp_tables since the system
should be on all the time.
There was no renaming involved. After TRUNCATE you run an INSERT. The only blocking operation is the TRUNCATE. I quote the manual:
TRUNCATE acquires an ACCESS EXCLUSIVE lock on ...
0
You might use oracle datamodeler (it is free and multiplatform). You can run it againt the source database and design the current model. Then you can automatically extract DDL and run them against Oracle or SQLServer. It should works but due to differences across database you may have to manually review the DDLs.
3
time with time zone stores microseconds (8 bytes) and the time zone (4 bytes). timestamp with time zone stores just the microseconds and converts the time zone at display time. Because of the conceptual weirdness of the time with time zone type, the time zone needs to be stored explicitly. You don't actually need 8 bytes to store the number of ...
2
I can suggest you another approach/solution. You can partition the table and delete partition older than 3 months just dropping them. This is fast and does not impact other inserts.
However seems that you need to keep at least one value; can you calculate values to be kept and insert them in another table?
0
I know the one-click installer breaks (or used to break) with passwords that have an ampersand in them.
It's part of some shell that doesn't go through. It gives errors like the ones you saw.
0
You can use pg_basebackup to take a copy of the PostgreSQL database over the replication protocol. It copies the whole DB though, it isn't incremental. You'll want to use the --xlog-method=stream argument unless you have a shared WAL archive set up.
Streaming replication isn't really well suited to the purpose you describe. You can set it to have ...
0
you should be able to use "pg_standby". http://www.postgresql.org/docs/current/static/pgstandby.html
cannot gurantee it as i havent tried it.
1
This appears to work - for public. I'm assuming (but have not yet tested) that I can do something similar with another group role. As me (the local admin), I run the following.
create schema foo;
grant usage on schema foo to public;
alter default privileges in schema foo grant select on all tables to public;
select 1 into foo.test;
Once this has finished, ...
2
I have not done it but, if not mistaken, it can be easily accomplished with tablespaces.
Let us know how it went.
0
I made a connection using pgadmin, and then dropped the trigger. The problem was I can only use ssh to connect to the server, and I didn't know how to make that connection at first. I've just found a way to tunnel the connection via ssh.
ssh -fNg -L 5555:localhost:5432 {username}@{domain.com}
0
First make sure the file exists really in that location. Run ls $PGDATA if there is a mistake in the environment variable you will either see the wrong files or get an error because the path does not exist.
Then make sure the owner of the parent directory /home/destination_data_directory and all directories and files under match the user you start pg_ctl ...
1
There are no permissions on LISTEN and NOTIFY. It would not have made sense to have any until support for notify payloads was introduced in more recent versions.
If you want access control, create a table with the information that you want and then send a NOTIFY that's empty or has nothing but a primary key for that table in it. SELECT the row(s) of ...
4
This won't work. Don't even try feeding mysqldump output directly into psql. You'll need to dump schema and data separately, convert the schema either by hand or with a tool, load the converted schema into PostgreSQL then load the dumped data.
mysqldump's compatibility flags are moderately useful for dumping data but pretty useless for dumping schema ...
1
I found this answer:
http://stackoverflow.com/questions/10763143/in-rails-couldnt-create-database-for-adapter-postgresql
And, as simple as it was, it worked for me... ran a $bundle update and it started working again.
4
If you don't have concurrent transactions that would prohibit you from getting an exclusive lock on the table, I would:
Select the (relatively few) surviving rows into a temporary table.
Make sure you have enough RAM available for the temporary tables (for this session only). Read about temp_buffers in this related answer:
Optimizing bulk update ...
2
I don't think there is a global parameter to tweak in pgAdmin 1.16.*
As an alternative you can open tables with:
Tools -> View Data -> View Top 100 rows
This is also available from the context menu of a table in the object browser.
Once the data grid is open, a "Limit Bar" is available, where you can set a maximum for returned rows.
I would ...
2
It sounds like what you probably want is to:
Create a role to own all the common tables and schema, or just use your own if you really will always be the only one with full control of the main tables.
Create another role you intend to give only read-only access to the shared tables and schemas. GRANT that role rights using GRANT SELECT ON ALL TABLES IN ...
2
I am not entirely sure how much this information helps, but the system table pg_stats contains a correlation column.
From the manual
Statistical correlation between physical row ordering and logical ordering of the column values. This ranges from -1 to +1. When the value is near -1 or +1, an index scan on the column will be estimated to be cheaper ...
2
I have experience this exact same thing in the past. It usually happens because you have linked psql against an inferior readline-like library. If you are using libedit, use libreadline instead. If you are using libreadline, use a different one. Details depend on your operating system and method of installation.
4
You can get some of that information by turning on the configuration parameter log_planner_stats. Most of that information, however, doesn't really exist, because the planner does not fully compute all alternative plans and their costs. It only explores an alternative plan until it can determine that it is slower than the current best plan. So alternative ...
0
Have you tried something like:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA individual_schema1 TO PUBLIC;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA individual_schema2 TO PUBLIC;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA individual_schema3 TO PUBLIC;
...
GRANT SELECT ON ALL TABLES IN SCHEMA core_schema TO PUBLIC;
This will give R/O access to the core ...
1
Depending on how you would want to deal with possible NULL values, concat_ws() is probably your safest and simplest way to go:
UPDATE tbl
SET filed1 = replace(field1, concat_ws(' ', field2, field3, field4), '')
WHERE filed1 IS DISTINCT FROM replace(field1, concat_ws(' ', field2, field3, field4), '')
concat_ws() ignores NULL values. With plain ...
2
IN queries with huge sets are notoriously slow. It's often faster to use a JOIN instead:
SELECT nodes
FROM planet_osm_ways
JOIN (
SELECT ltrim(member, 'w')::bigint AS id
FROM (
SELECT unnest(members) AS member
FROM planet_osm_rels
WHERE (tags_hstore @> '"type"=>"boundary", "admin_level"=>"2", ...')
) u
WHERE ...
2
You haven't disclosed what to do with whitespaces or what to do with overlapping substrings. Anyhow, something like this could help:
UPDATE tableA
SET field1 = replace(replace(field1, field2, ''), field3, '')
WHERE replace(replace(field1, field2, ''), field3, '') <> '';
There is no pattern to match, simply replace a string (in an other column) with ...
1
Postgres-XC does not run on Windows:
Postgres-XC currently runs on the Linux Operating Systems running on
64-bit Intel(R) processors.
The development team tested this with Cent-OS 5.3 and 5.4.
You might be able to build the data nodes and possibly the coordinators on Windows, but the GTM will take major work to port to Windows.
0
It depends a lot on what the data is, what you're doing with the data, what the bandwidth between client and server is, what stored procedure language you're using, etc.
Test it and see. You can:
Use the psql \timing command to get the final execution time including results transfer from the client perspective;
Use log_min_duration_statement = 0 in ...
0
VACUUM FULL takes an ACCESS EXCLUSIVE lock on the table, then rewrites the whole table. This will take a long time for a large table, especially since it must also rebuild all indexes. You haven't said anything about the table size or the server's I/O subsystem so it's hard to say if 5 hours is unexpected or not.
You shouldn't generally need to use VACUUM ...
7
Usually, the Postgres server process is owned by the accompanying system user (not by root or the installing user), so it only has a limited set of rights, making possible attacks somewhat less dangerous.
Additionally, the Postgres system user is normally used to carry the default privileges to initialize db clusters and access newly created databases ...
3
The widely used tool is the SQL command EXPLAIN ANALYZE, possibly with more options for more details in the answer. That outputs the query plan with the planner estimates plus actual execution times.
Why would you want to clear the cache? The generally more likely use case is that the cache is populated. If you still want to got that route, here is a ...
3
Not in PostgreSQL at this time. PostgreSQL doesn't actually build a single giant SQL statement; instead, it uses the plan tree of the view(s) and inserts that into the top level query's plan tree. To turn that back into SQL would require a deparser, something PostgreSQL doesn't have a the moment.
You can kind-of do-it-yourself by replacing references to ...
3
I very strongly recommend that you do not put a tablespace on an external removable drive. If the tablespaces disappears (ie: drive unplugged) you risk severe database corruption that will be difficult to recover from. You'll likely find permissions management a challenge, too.
You should generally initdb a new database cluster on the drive, then start it ...
4
Create the new database, then create a tablespace on your external drive:
CREATE TABLESPACE mytablespace LOCATION '/Volumes/externaldisk/pg';
Then alter the database so that the new tablespace is the default location for it:
ALTER DATABASE MYDB TABLESPACE mytablespace;
The other way to do this is to just move your entire Postgres data directory to the ...
2
You seem to have omitted the new version of the upgraded server, but I strongly suspect it's still on 8.1 since the ALTER SEQUENCE ... OWNED BY clause was added in 8.2 (Compare ALTER SEQUENCE docs on 8.2 to ALTER SEQUENCE docs on 8.1).
You really need to understand that 8.4 and 8.1 aren't slightly different versions, they're massively different. It's like ...
1
Very likely your problem is that you have an index on the source table which is making the first hits easy. However at some point you are hitting rows that are not in the cache and so this is requiring a sudden increase in disk I/O accounting for the performance drop. In essence suddenly you start missing the cache and so you end up having to read a bunch ...
3
No, there is no reliable way to prevent the local administrator (or a domain admin with the ability to grant themselves local admin on a machine) from accessing the raw PostgreSQL data files, starting/stopping the server, changing the service account password, changing pg_hba.conf so they can log in to the server, etc.
As stated in the comments, if you ...
2
With the comment from FrustratedWithFormsDesigner, I came to the following solution:
SELECT subq2.*, sum(new_group) OVER (ORDER BY t ASC) AS group_id
FROM (
SELECT subq.*, CASE WHEN delta > 1500 THEN 1 ELSE 0 END AS new_group
FROM (
SELECT t, lag(t) over (ORDER BY t ASC),
t - lag(t) over (ORDER BY t ASC) AS delta
FROM time_points
) AS ...
1
If it is sufficient that the group boundaries be arbitrary time periods (like 1000000-10001499, 10001500-10002999, ...) then GROUP BY int_timestamp/1500 should do, and you can take MIN(int_timestamp) as the ID for the group.
Or are you actually trying to output the whole rows grouped by 1500ms instead of just aggregates of the groupings? It would be helpful ...
3
Only ORACLE and IBM-DB2 are officially supported as subscribers. Refer here and here for more details.
Also, from sql server 2012, this is being depreciated as well.
Heterogeneous replication to non-SQL Server subscribers is deprecated. Oracle Publishing is deprecated. To move data, create solutions using change data capture and SSIS.
Caution :
This ...
1
I faced the same problem, it was resolved by turning off my Win Firewall and Antivirus
They were blocking the DB server connection
4
Answer to question
SELECT DISTINCT ON (name, zonedistrict_id)
ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname
FROM zones
GROUP BY gid, name, zonedistrict_id, zonestyle_id, longname
ORDER BY name, zonedistrict_id, zonestyle_id;
It depends on what you are actually trying to achieve and what version of Postgres you are ...
5
Yes, it is safe, at least on 9.2, and I'm not aware of any major changes in that regard since 8.4. If absolute certainty is required you should initdb a test instance to experiment with before changing the production system.
In the following tests with Pg 9.3-pre, if PostgreSQL cannot re-read the config file due to a syntax error or other serious problem ...
3
I have not watched the RailsCast, but here are the steps I took to set up PostgreSQL on OS X:
Mac OS X comes with PostgreSQL 9.0.4 installed automatically, and we need to change the symbolic links for the postgres, pg_ctl, and psql commands. After installing the latest PostgreSQL:
$ cd /usr/local/bin
$ rm postgres
$ ln -s ...
2
go to ..\PostgreSQL\9.0\data and open the file postgresql.conf in text editor/notepad
search for port parameter .eg: port = 5433
edit this to your port number.
go to run type services.msc and restart postgresql service.
you can check wether the parameter is set or not via query tool. just execute show port query. it will display your current port ...
3
Since you're on Windows and are probably starting PostgreSQL as a Windows service, you'll need to edit postgresql.conf (inside your data directory) to set the new port there, then restart the postgresql service using the Services control panel or (as an Administrator) the net service command.
You can use a programmatic text editing tool to change ...
Top 50 recent answers are included
