I had a look through the pg_* tables, and I couldn’t find any creation times in there. It’s possible to locate the table files, but then on Linux you can’t get file creation time. So I think the answer is that you can only find this information on Windows, using the following steps: get the … Read more
Postgresql > 8.4 SELECT row_number() OVER (ORDER BY col1) AS i, e.col1, e.col2, … FROM …
Column / Row … I don’t need the transactional integrity to be maintained across the entire operation, because I know that the column I’m changing is not going to be written to or read during the update. Any UPDATE in PostgreSQL’s MVCC model writes a new version of the whole row. If concurrent transactions change … Read more
I found this answer for SQL Server that also works in Postgres. Having never done it before, I thought the technique was quite clever. Basically, he creates a custom partition for the windowing function by using a case statement inside of a nested query that increments a sum when the value is not null and … Read more
Or ask your database: $ psql -U postgres -c ‘SHOW config_file’ or, if logged in as the ubuntu user: $ sudo -u postgres psql -c ‘SHOW config_file’
Of course, you will get a fatal error for authenticating, because you do not include a user name… Try this one, it is OK for me 🙂 psql -U username -d myDataBase -a -f myInsertFile If the database is remote, use the same command with host psql -h host -U username -d myDataBase -a -f … Read more
Right after I posted this, I ran another query to check on a suspicion: SELECT * FROM pg_timezone_abbrevs WHERE abbrev IN (‘CEST’, ‘CET’); abbrev | utc_offset | is_dst ——–+————+——– CEST | 02:00:00 | t CET | 01:00:00 | f As it turns out, there is also a time zone abbreviation named CET (which makes sense, … Read more
You can see what’s in the PostgreSQL buffer cache using the pg_buffercache module. I’ve done a presentation called “Inside the PostgreSQL Buffer Cache” that explains what you’re seeing, and I show some more complicated queries to help interpret that information that go along with that. It’s also possible to look at the operating system cache … Read more
Just increasing max_connections is bad idea. You need to increase shared_buffers and kernel.shmmax as well. Considerations max_connections determines the maximum number of concurrent connections to the database server. The default is typically 100 connections. Before increasing your connection count you might need to scale up your deployment. But before that, you should consider whether you … Read more
Use LOWER function to convert the strings to lower case before comparing. Try this: SELECT id FROM groups WHERE LOWER(name)=LOWER(‘Administrator’)