Yes, SERIAL is the equivalent function. CREATE TABLE foo ( id SERIAL, bar varchar ); INSERT INTO foo (bar) VALUES (‘blah’); INSERT INTO foo (bar) VALUES (‘blah’); SELECT * FROM foo; +———-+ | 1 | blah | +———-+ | 2 | blah | +———-+ SERIAL is just a create table time macro around sequences. You … Read more
Use the operating system user postgres to create your database – as long as you haven’t set up a database role with the necessary privileges that corresponds to your operating system user of the same name (h9uest in your case): sudo -u postgres -i As recommended here or here. Then try again. Type exit when … Read more
( tl;dr : goto option 3: INSERT with RETURNING ) Recall that in postgresql there is no “id” concept for tables, just sequences (which are typically but not necessarily used as default values for surrogate primary keys, with the SERIAL pseudo-type). If you are interested in getting the id of a newly inserted row, there … Read more
If I remember correctly the user postgres has no DB password set on Ubuntu by default. That means, that you can login to that account only by using the postgres OS user account. Assuming, that you have root access on the box you can do: sudo -u postgres psql If that fails with a database … Read more
There’s three ways to get this sort of count, each with their own tradeoffs. If you want a true count, you have to execute the SELECT statement like the one you used against each table. This is because PostgreSQL keeps row visibility information in the row itself, not anywhere else, so any accurate count can … Read more
Can be done without conversion to/from int (but to/from timestamp instead) SELECT date_trunc(‘day’, dd):: date FROM generate_series ( ‘2007-02-01’::timestamp , ‘2008-04-01’::timestamp , ‘1 day’::interval) dd ;
“Column Tetris” Actually, you can do something, but this needs deeper understanding. The keyword is alignment padding. Every data type has specific alignment requirements. You can minimize space lost to padding between columns by ordering them favorably. The following (extreme) example would waste a lot of physical disk space: CREATE TABLE t ( e int2 … Read more
Statements end with semicolons. In psql, pressing enter without a semicolon continues the statement onto the next line, adding what you wrote to the query buffer rather than executing it. You will notice that the prompt changes from dbname=> to dbname-> to indicate that you’re on a continuation line. regress=> DROP TABLE sometable regress-> \r … Read more
Take a look at this short article. The solution is paraphrased here: Create your table: CREATE TABLE zip_codes (ZIP char(5), LATITUDE double precision, LONGITUDE double precision, CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar); Copy data from your CSV file to the table: COPY zip_codes FROM ‘/path/to/csv/ZIP_CODES.txt’ WITH (FORMAT csv);
FTS does not support LIKE The previously accepted answer was incorrect. Full Text Search with its full text indexes is not for the LIKE operator at all, it has its own operators and doesn’t work for arbitrary strings. It operates on words based on dictionaries and stemming. It does support prefix matching for words, but … Read more