Hot answers tagged

8

The ages are discrete values OK. a Person can have any number of the 18 (assuming 0-17 years old) assigned to their account. So it's a many-to-many relationship? If so, you just decompose your data into third normal form as usual, expressing the cardinality by means of one extra relation. Example follows. The SQL dialect is not necessarily ...


8

1. Assuming that sku and item_number will always hold unique values I consider that you have come up to the answer by yourself, since an item_number is an optional attribute (or column at the implementation phase), i.e., it does not apply to all the product occurrences, therefore, from a logical point of view, it decidedly cannot (should not) be declared as ...


6

I will assume your table is named baz and is in schema public create table baz ( foo text primary key ); insert into baz (foo) values ('hello'); select foo from baz; Now you want to rename that column: alter table baz rename column foo to bar; But now your scripts are broken: select foo from baz; -- nope! So create a new schema, put a view in ...


5

This has been asked many times. The long and the short of it: Don't try it. You'd run into all kinds of problems with concurrency, gaps introduced by deleting rows, etc. Make id a plain serial column and create a VIEW to display a running number per user_id. CREATE VIEW v_user_article AS SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY id) AS ...


5

Assuming 2016-04-05 0:27:15 instead of 2016-04-05 1:27:15 in the underlying table, the question would make more sense to me: CREATE TABLE tbl (created_at timestamp, status text); INSERT INTO tbl VALUES ('2016-04-05 00:27:15', 'info') , ('2016-04-05 03:27:15', 'info') , ('2016-04-05 05:27:15', 'warn') , ('2016-04-05 10:27:15', 'info') , ('2016-04-05 ...


5

If the account id is always at the third position use split_part() select * from gorfs.seg WHERE split_part(full_path, '/', 4) = '4865' Note that due to the leading / the actual index is 4, not 3. The following query: with seg (id, full_path) as ( values (1, '/userfiles/account/4556/attachment/1234'), (2, ...


4

I don't think that - a default index generation for foreign key columns - would lead to serious problems. It was just a decision taken from the PostgreSQL developers, to leave this choice to each database designer / administrator. We have the choice to either add an index when creating a foreign key or not. If they had taken the opposite decision, then ...


4

This turned out to be a tricky puzzle which took up far more time than I thought it would - I guess for you too? :-). I (finally) managed to solve it thus: First I created (using DDL and DML for those who wish to try this at home) a table called stringtest: CREATE TABLE stringtest (my_string VARCHAR(255)); and populated it as below INSERT INTO ...


4

The statement looked perfectly fine in the first place. After excluding other options (like tables referenced without schema), it seems like the problem is caused by the 4 UPDATE triggers on the table (or one of them). You should inspect the trigger code and understand what they are doing and which one of them is causing this behaviour. After that, it's ...


4

Your existing configuration of HAProxy -> PGBouncer -> PGServer approch is better. And that only works. Here is the reason: HAProxy redirects connection to different servers. this results in MAC address change in the database connection. So if PGBouncer is above HAProxy, each time the connections in the pool gets invalidated because of MAC address change.


4

To answer this, I did the following. (I found the following three threads helpful - 1, 2 and 3. I also found the documentation on generate_series and CROSSTAB here and here respectively to be useful). This should work on 9.1 - not tested, but the documentation indicates that no post-9.1 stuff is being used. Created a table: ntest=# create table ...


4

Depending on what you do exactly in your scripts, there are ways to work around it. I'll assume SELECT only for the purpose of this answer. But INSERT, UPDATE, DELETE are possible, too ... VIEW A VIEW would be an option, like @Andriy commented. If you need the current table to stay in place as is, things get more complicated, but still possible. You could ...


3

I would simply call it "aggregation". As for elegance, you can do this with a single SQL command without another table using a data-modifying CTE: For (only) hourly aggregation of rows that are older than a week: WITH del AS ( DELETE FROM data d WHERE stamp < now() - interval '7 days' -- ">" would be wrong AND (date_trunc('hour', ...


3

PostgreSQL database dumps are normally produced by the command line tool pg_dump, and expected to be used via either pg_restore or simply psql command line tools. However, you can both backup and restore from PgAdminIII. The backup option is available via right-clicking either the server or any database or schema. The Restore option is available via ...


3

As for my experience with PostgreSQL it is relatively safe operation unless you have a really big performance impact on that moment. If you have it then it's better to temporary pause writing from all of your clients. I had only one critical case while syncing my master to slave under load and it was caused by OOM killer (yes, you really should COMPLETELY ...


3

As @NeilMcGuigan suggested, an Exclusion Constraint is the key here. The part I was unfamiliar with was the ability to use the <> operator. As available post Postgres 9.0: CREATE TABLE accrual ( add_date date NOT NULL, user_id integer NOT NULL, rate integer NOT NULL, amount numeric(7,3), EXCLUDE USING gist (date WITH =, ...


3

as well as inserting into the login_coordinates table I don't see the benefit of redundant storage. Just write to the table login_coordinates. Easy enough to access with only 10 rows per user. Don't update the user row as well. Basically, what @Ziggy already suggested, with more flesh. Based on this table: CREATE TABLE login_coordinates ( ...


3

Try this: -- This will concatenate the values: \set myvariable '/var/lib/pgsql95/' :myvariable '/pg_tblspc' -- This will expand the variable single-quoted: CREATE TABLESPACE aspire_data OWNER aspireapp LOCATION :'myvariable'


2

Join to a subquery that computes numero with the window function row_number(): UPDATE movimientos m SET numero = sub.rn FROM (SELECT id, row_number() OVER (ORDER BY orden, id) AS rn FROM movimientos) sub WHERE m.id = sub.id; Details for UPDATE syntax in the manual. If you have concurrent write access you need to lock the table to avoid race ...


2

However, after searching for similar issues, all I can see from other people is this error for 'of relation base', not 'in file'. How is 'in file' different, and what does this mean? Is it more likely that I have a hardware issue? No, it's the same issue. The error message used to refer to the relation in older versions of PostgreSQL, but it was ...


2

If using psql via bash command line, you can simply do: psql -c "COPY (select query) To '/tmp/test_`date +"%Y-%m-%d_%H%M%S"`.csv' DELIMITER ',' CSV HEADER;"


2

You could implement an ON INSERT trigger and delete, for the same user, any rows with a timestamp older than the oldest (MIN) of a subselect ordered by timestamp DESC limit 10. Edit, using an array in users: An alternative is using an array straight in the users table: Add a type and a new array in users: CREATE TYPE past_logon AS (login_at timestamptz, ...


2

Stop postgresql service cp -a source_data_directory destination_data_directory chown -R postgres_user /destination_data_directory export PGDATA=destination_data_directory Changing data directory to destination_data_directory within postgresql.conf pg_ctl start


2

You could just alter the table and convert the column in one step: alter table ogrtable alter column arrcolumn type varchar using (array_to_string(arrcolumn, ', '));


2

If it's one time setup, I think dump would be easiest. But if You need your update be available on customer, then I think streaming replication would be easier, set up once and no need to update in the future as the replication will handle the data updates. http://www.postgresql.org/docs/current/static/high-availability.html


2

pg_indexes.tablename only contains the table name, not the schema name. The schema name is available in the column schemaname. So you need to use select * from pg_indexes where tablename ='asignacion' and schemaname = 'distribucion';


2

PostgreSQL can be built to either use the operating system's time zone database or its own. Check with the command pg_config --configure whether the option --with-system-tzdata was used. If not, it is using its own time zone data, and that hasn't been updated yet. If so, to fix, either wait for the next releases, or install the packages the way you have ...


2

Here's a shell script that can do what you want: SCHEMA="myschema" DB="mydb" psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\ while read TBL; do psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV" $DB > $TBL.csv done Make sure you set the DB and SCHEMA variables to your particular database and schema. The wrapping psql ...


2

PostgreSQL can only make use of a function index when the comparison is against the results of the function, e.g.: AND (s.full_path)::text ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+' Alternatively, create the index without typecasting: CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path); Note also that the character / ...


2

There are many ways for doing this. When measurements can be trusted to be sequential (time), then one can use a simple self left join, which should perform very fast: SELECT * FROM data d LEFT JOIN data prev ON (prev.time = d.time - 1) WHERE (d.foo, d.bar) IS DISTINCT FROM (prev.foo, prev.bar) Another one is using window functions (PG version >= 8.4): ...



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