FAQ - Databases and SQL

Everything related to database and SQL principles

Recommendations

1) Use lower letters for creating objects in a PostgreSQL database

• Use lower case letters for objects in PostgreSQL like tables, columns, triggers, keys, actually any object. Enter lower case in GUI environment, too. Otherwise objects like tables are created with partial uppercase letters! In this case double quotes are 'automatically' used while creating the objects and you have to use double quotes for querying, too! See Reuven Lerners blog. ------------------------------------------------------------------------------- -- Recommendation: Always use lower case letters in PostgreSQL ------------------------------------------------------------------------------- CREATE TABLE myschema.tbl( tbid INTEGER NOT NULL , tbltext VARCHAR NOT NULL, CONSTRAINT tbl_pk PRIMARY KEY(tbid)); -- Queries and all other DB-handling is much easier without double quotes SELECT t.tbid, t.tbltext FROM myschema.tbl t; ------------------------------------------------------------------------------- -- Try to avoid: Table or other objects created with mixed case letters ------------------------------------------------------------------------------- -- The following two - not recommended - SQL statements are equivalent CREATE TABLE myschema.Tbl ........; CREATE TABLE myschema."Tbl" ......; -- In both cases unwanted query syntax is needed SELECT t."TblId", t."TblText" FROM myschema."Tbl" t;

2) No special characters - No reserved keywords for object names in a database

• Special characters such as ä, ü, -/+ etc. should not be used in table names, column names, view names and key names (generally object names). In contrast, special characters are allowed to be used in comments on tables or columns. Obviously the contents of text columns (varchar) in the database may contain special characters of any kind.
• Databases have keywords for internal usage and also for querying purposes. Examples are VARCHAR, NUMBER, LIMIT, SELECT, FROM, .... Do not use these names as table names, column names, view names and key names (generally object names). Keywords are normally marked with special colour while typing its name in a SQL-window. PostgreSQL documentation.

3) Put comments to all your tables and all your columns

• Minimal documentation (=metadata) of your tables and their columns is a de-facto standard in each database. For columns we use square brackets to indicate a unit, a code or in some cases a datatype if applicable. For sequences or identification numbers empty brackets are common. ------------------------------------------------------------------------------- -- Commenting of a canton table with different attributes/columns ------------------------------------------------------------------------------- COMMENT ON TABLE myschema.canton IS 'Cantons of Switzerland'; COMMENT ON COLUMN myschema.canton.canid IS '[] Canton ID'; COMMENT ON COLUMN myschema.canton.canname IS '[] Canton Name'; COMMENT ON COLUMN myschema.canton.canpopulation IS '[] Most recent population number'; COMMENT ON COLUMN myschema.canton.wealthcategory IS '[Code] Categorized wealth level of the canton'; COMMENT ON COLUMN myschema.canton.avgtemperatur IS '[C°] Average yearly temperature over the last 100 years'; COMMENT ON COLUMN myschema.canton.geom IS '[Multipolygon] Geometry for Canton';

4) Name your primary keys, foreign keys or spatial indices with a constant speaking name pattern

• Minimal documentation (=metadata) of your tables and their columns is a de-facto standard in each database. For columns we use square brackets to indicate a unit, a code or in some cases a datatype if applicable. For sequences or identification numbers empty brackets are common. ------------------------------------------------------------------------------- -- Naming primary (_pk) and foreign keys (_othertable_fk) ------------------------------------------------------------------------------- ALTER TABLE myschema.canton ADD CONSTRAINT canton_pk PRIMARY KEY (canid); ALTER TABLE myschema.canton ADD CONSTRAINT canton_mywealthcattable_fk FOREIGN KEY (wealthcategory) REFERENCES myschema.mywealthcattable(wealthcatcolumn); -- Unique key index (_uk) on some column CREATE UNIQUE INDEX canton_canname_uk ON user50.canton (canname); -- Spatial index (_sidx) on postgis geometry column CREATE INDEX canton_geom_sidx ON myschema.canton USING gist(geom);

Modelling

In our tree table we have a categorical attribute which is a color component (red, green blue). Shall we write the text values in a text-column or encode the text values with numbers?

• It is recommended to encode categorical text values with numbers. You then actually treat the color as a separate entity type. This entity type will en up in the DB as a separate table (lookuptable) named 'color' with a primary key field e.g. COLNR (number) and a text column e.g. COLTXT. The tree table has a COLNR field as well. This field refers to the color table over a foreign key making sure that you can only fill in valid color numbers.

SQL Spatial Questions

Which SRID/geometry type have geometries stored in a spatial table?

• ------------------------------------------------------------------------------- -- What SRID, geometry types or subtypes do geometries in a table consist of? -- Replace user and tablename as needed ------------------------------------------------------------------------------- SELECT ST_SRID(t.geom) AS srid, ST_GeometryType(t.geom) AS geom_type, COUNT(*) AS number_of_geoms FROM userxx.tablenamexx t GROUP BY ST_SRID(t.geom), ST_GeometryType(t.geom);

What is the unit for a specific projection/SRID?

• ------------------------------------------------------------------------------- -- Finding units about a specific coordinate system in the fields srtext or proj4text (here meters) ------------------------------------------------------------------------------- SELECT sr.srid, sr.proj4text, sr.srtext, sr.auth_name, sr.auth_srid FROM spatial_ref_sys sr WHERE sr.srid = 26918;

How can I change the table columns' geometry type as well as reproject the geometries stored in the table?

• Example: Reproject a spatial table (mytable) with a subtyped column named geom of type geometry(MultiLineString,21781). This is projection CH1903 LV03.
• The column wille get a new subtype - a new SRID, respectively
• All records (geometries) inside the table get reprojected
• This converts all to CH1903+ LV95 ------------------------------------------------------------------------------- -- Changing SRID subtype and reprojecting ------------------------------------------------------------------------------- ALTER TABLE userxx.mytable ALTER COLUMN geom TYPE geometry(MultiLineString,2056) USING ST_Transform(geom,2056);
• Check the geometry column datatype of your table now as described on this page 'Which column datatypes or subtypes exist in a table?'

SQL General Questions

Which datatypes should I use for tables in PostgreSQL with PostGIS?

• We recommend the follwing ones that cover all datatypes we usually need ------------------------------------------------------------------------------- -- Recommended PostgreSQL Types ------------------------------------------------------------------------------- varchar(n) --> string with predefined length numeric(t,p) --> precise calculations, measurements, money; t=precision/total length; p=precision/positions after decimal point; smallint (=int2) --> enumerations, key attributes, ID attibutes, integer numbers from -32768 to +32767 integer (=int4) --> enumerations, key attributes, ID attibutes, integer numbers from -2147483648 to +2147483647 bigint(=int8) --> enumerations, key attributes, ID attibutes, integer numbers from -9223372036854775808 to 9223372036854775807 timestamp --> date with time, often used date --> date only geometry --> for vector geometries, general type (postgis), please use a subtype geometry(POLYGON, 2056) --> subtyped, line geometries ch-lv95 (postgis) geometry(LINESTRING, 3857) --> subtyped, line geometries wgs84 | pseudo web mercator (postgis) geometry(POINT, 4326) --> subtyped, point geometries wgs84 (postgis) geometry(MULTIPOLYGON, 2056) --> subtyped, multipolygon geometries ch-lv95 (postgis) geometry(MULTILINESTRING, 2056) --> subtyped, multiline geometries ch-lv95 (postgis) geometry(MULTIPOINT, 2056) --> subtyped, multipoint geometries ch-lv95 (postgis)

Which column datatypes or subtypes exist in a table?

• Listing existing columns of a table ------------------------------------------------------------------------------- -- Getting types/subtypes used to define columns of a table -- Replace user and tablename as needed ------------------------------------------------------------------------------- SELECT a.attname as column_name, format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_attribute a INNER JOIN pg_class b ON (a.attrelid = b.oid) LEFT JOIN pg_namespace n ON n.oid = b.relnamespace WHERE n.nspname= 'userxx' AND b.relname = 'tablenamexx' and a.attstattarget = -1;

Can we automatically count up an identification field in a table?

• Yes. While creating a table choose for your id field the serial field type (~data type). (rm) -------------------------------------------------------------------------------- -- Create a table with a self increasing field and a primary key on that field -------------------------------------------------------------------------------- CREATE TABLE myschema.mytable ( idfield INT2 not null GENERATED ALWAYS AS IDENTITY, textfield VARCHAR(128) not null, CONSTRAINT mytable_pk PRIMARY KEY(idfield) ); -- INT2 covers an integer data range 1 to 32'767 (2 bytes) -- consider INT4, INT8 for larger ranges What happens behind the scenes? The field idfield actually is generated to contain the integer data type. Additionally a sequence 'mytable_idfield_seq' is automatically generated for counting up the values. As soon as we insert a record into table mytable the idfield gets filled with the next value of the sequence.

How can I retrieve a limited number of rows in a SELECT Query?

• Yes. Use the LIMIT clause.
  Some GUIs like SQL Manager for PostreSQL limit returning records automatically in the background. They add additional SQL-Code to your SQL sentence. You can change such restricting values in the preferences of those programs. -------------------------------------------------------------------------------- -- Retrieve only two records of a table -------------------------------------------------------------------------------- SELECT t.idfield, t.textfield FROM myschema.mytable t LIMIT 2;

How can I grant or revoke a table to a different user?

• Yes. Use the LIMIT clause.
  Some GUIs like SQL Manager for PostreSQL limit returning records automatically in the background. They add additional SQL-Code to your SQL sentence. You can change such restricting values in the preferences of those programs. -------------------------------------------------------------------------------- -- 1) Grant the usage of your schema (that's where your objects reside) -- In our database the schema name is the same as the user's name. -------------------------------------------------------------------------------- GRANT USAGE ON SCHEMA myschema TO adifferentusername; -------------------------------------------------------------------------------- -- 2) Grant e.g. SELECT right on table to the required user -------------------------------------------------------------------------------- GRANT SELECT ON myschema.mytable TO adifferentusername; -------------------------------------------------------------------------------- -- 3) If needed: Revoke e.g. SELECT right from the required user -------------------------------------------------------------------------------- REVOKE SELECT ON myschema.mytable FROM adifferentusername;

How difficult is it to store files in the DB (e.g. .pdf, .docx, .wav or others)?

• Somewhat complicated in PostreSQL. Have a look at this documentation.

QGIS connection to PostgreSQL/PostGIS

How can I connect from QGIS to a PostGIS database?

• In the Browser Panel of QGIS -> Rightclick PostGIS -> New Connection
• Configure your connection accordingly
  
• To check your credentials: Test Connection
• You can use this connection either for browsing your spatial database objects or for loading spatial datasets (layers) to PostgreSQL/PostGIS

DBeaver

Configure new background to visualize your geometries on top

• If you run a SQL-Statement on one of your spatial tables like this: SELECT b.id, b.geom FROM userxx.imp_biogeo b; you can click on the resulting geom-column to visualize your geometry.
• You can use OpenStreetMap as a background map or define your own services
• Swiss Maps are available by entering the services in the DBeaver's Value-window -> Map-dropown -> Manage -> Add

  Label: CH-25
  Layer definition: 'https://wmts20.geo.admin.ch/1.0.0/ch.swisstopo.pixelkarte-farbe-pk25.noscale/default/current/3857/{z}/{x}/{y}.jpeg', {attribution:'CH-1:25', id:'ch.swisstopo.pixelkarte-farbe-pk25.noscale'}

  or this generalization:

  Label: CH-1000
  Layer definition: 'https://wmts20.geo.admin.ch/1.0.0/ch.swisstopo.pixelkarte-farbe/default/current/3857/{z}/{x}/{y}.jpeg', {attribution:'CH-1:1000', id:'ch.swisstopo.pixelkarte-farbe-pk1000.noscale'}

Other Material

Can a PostreSQL database be accessed via PHP or other programming languages?

• Yes. Use the corresponding connection classes, mostly jdbc [java database connector]. (rm)

Errors

• Solution: